SQL面试题

3 年前 · 来自专栏数据分析

crazy

一.

表一：订单表orders，大概字段有(user_id '用户编号',order_pay '订单金额',order_time '下单时间')

表二：活动报名表act_apply，大概字段有(act_id '活动编号', user_id '报名用户',act_time '报名时间')

需求：

统计每个活动对应所有用户在报名后产生的总订单的金额，总订单数。（每个用户限报一个活动，题干默认用户报名后产生的订单均为参加活动的订单）
统计每个活动从开始后到当天（考试日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间（涉及到时间的数据类型均为datetime）

-- 第一问
select t2.act_id, sum(order_pay) as total_pay, count(t1.order_time) as total_orders
from (select user_id,order_pay,order_time from orders) t1
inner join 
(select act_id,user_id,act_time from act_apply) t2
on t1.user_id=t2.user_id
where t1.order_time>=t2.act_time
group by t2.act_id
-- 第二问
select t2.act_id, count(t1.order_time)/datedif(now(),begin_time) as avg_orders
from (select user_id, order_time from orders) t1
inner join
     (select act_id, user_id, act_time, 
      min(act_time) over(partition by act_id) as begin_time) from act_apply) t2
on t1.user_id=t2.user_id
where t1.order_time>=t2.act_time and ti.order_time between begin_time and now()
group by act_id

二.

表一：用户行为表tracking_log,大概字段有(user_id '用户编号',opr_id '操作编号',log_time '操作时间')

需求：

计算每天的访客数和他们的平均操作次数；
统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。

-- 第一问
select log_date, count(distinct user_id),avg(opr_num) as avg_num
from (select date(log_time) as log_date, user_id, count(opr_id) as opr_num
      from tracking_log)
group by date(log_date),user_id)
-- 第二问
select log_date, count(distinct user_id)
from (select date(log_time) as log_date, user_id, opr_id, 
      lead(opr_id) over(partition by user_id) as opr_id_2
      from tracking_log)
where opr_id='A' and opr_id_2='B'
group by log_date

三.

表1——用户登陆表user_log，大概字段有（user_id'用户编号'，log_time'登陆时间'）

要求：

每天新增用户数，以及他们第2天、30天的回访比例

如何定义新增用户：用户登陆表中最早的登陆时间所在的用户数为当天新增用户数；

第2天回访用户数：第一天登陆的用户中，第二天依旧登陆的用户；--次日留存率

第30天的回访用户数：第一天登陆用户中，第30天依旧登陆的用户；

select t1.begin_time
       count(distinct t1.user_id) as '每天新增用户数'，
       count(distinct t2.user_id) as '次日留存用户'，
       count(distinct t3.user_id) as '第30日留存用户数'
from (select user_id, min(date(log_time)）as begin_time
      from user_log group by user_id) t1
left join
(select user_id, date(log_time) as second_day
from user_log) t2
on t1.user_id=t2.user_id and t1.begin_time=t2.second_time-1
left join
(select user_id, date(log_time) as thirty_day
from user_log) t3
on t1.user_id=t3.user_id and t1.begin_time=t3.thirtieth_day-29
group by t1.begin_time

四.

将用户对商品的评分用稀疏向量表示，保存在数据库表t中，t的字段有：uid，goods_id，star；uid是用户id；good_id是商品id；star是用户对该商品的评分，值为1-5。现在我们想要计算向量两两之间的内积，内积在这里的定义为：对于不同的用户，如果他们对同样一批的商品打了分，那么将每个人对该商品的打分与所有对该商品的打分依次相乘后并求和。

create table t(uid varchar(12),good_id varchar(12),star int(10));
insert into t(uid,good_id,star) 
values('u0','g0',2);
insert into t(uid,good_id,star) 
values('u0','g1',3);
insert into t(uid,good_id,star) 
values('u1','g0',4);
insert into t(uid,good_id,star) 
values('u1','g1',3);
insert into t(uid,good_id,star) 
values('u2','g0',5);
insert into t(uid,good_id,star) 
values('u2','g1',1);

创建表t，结果如下：

-- 解答
select good_id,sum(inner_product)
(select t1.uid,t1.good_id,t1.star as star1,t2.star as star2,t1.star*t2.star as inner_product
from t as t1
left join t as t2
on t1.good_id=t2.good_id)t3
group by good_id;

五.

员工表，宿舍表，部门表，统计宿舍楼各部门人数表。

员工表为employee，字段为id，employee_name，belong_dormitory_id，belong_department_id；

宿舍表为dormitory，字段为id，dormitory_number;

部门表为department，字段为id，department_name;

-- 解答
select t2.dormitory_name,t3.department_name,count(distinct employee_name)
(select employee, id,employee_name,belong_dormitory_id,belong_department_id
from employee)t1
inner join
(select id, dormitory_number from dormitory)t2
on t1.belong_dormitory_id=t2.id
inner join
(select id, department_name from department)t3
on t1.belong_department_id=t3.id
group by t2.dormitory_name,t3.department_name;

六.

交易表order_record,结构为user_id，id，paid_time，amount。

查询过去一个月付款用户量(提示：用户量取去重)最高的三天分别是哪三天；
查询昨天每个用户最后付款的订单id和金额。

-- 第一问
select date(paid_time)
from order_record
where month(paid_time)='03'
group by date(paid_time)
order by count(distinct user_id) desc
limit 3;
-- 第二问
select user_id,id,amount
from (
select user_id,id,paid_time,amount,max(paid_time) over(partition by user_id) as
latest_paid
from order_record)t1
where datedif(now(),date(paid_time))=1 and latest_paid=paid_time;

七.

表一：orders，字段分别为：id，coupon_fee优惠卷金额，order_fee订单金额，city_code城市代码(包括在city-conf中存在城市与乱码/NA/不在city-conf中的代码)；

表二：city_conf，字段分别为：name城市名称，city_code城市代码，area地区；

统计每个城市cover的订单费用(注：若优惠卷金额大于订单金额则cover的是订单费，否则为优惠卷金额；所有归不到城市列表中的订单统一为others)。

select city_name, sum(cover_order_fee) as total_cover_fee
from(
select (case when t2.name is null then 'others' else t2.name end) as city_name,
       (case when t1.coupon_fee>order_fee then order_fee else t1.coupon_fee end) as cover_order_fee,
from (
       (select id, coupon_fee, order_fee, city_code from orders)t1
left join
       (select name, city_code, area from city_conf)t2
on orders.city_code=city_conf.city_code)t3
group by city_name;

八.

表一：metro，字段分别为：name地铁站站名，Lng经度，Lag纬度

表二：orders，字段分别为：Lng经度，Lag纬度，Time开锁时间

统计各地铁站周边1km范围内的订单，找出7月1日早上7:00-9:00期间出行量最大的10个地铁站。(注：经度和纬度的小数点后两位相等即表示在一公里范围内)

select m.name as metro_name, count(Time)
from (metro as m
inner join 
orders as o)
on m.Lng=o.Lng and m.Lag=o.Lag
where trunc(m.Lng,2)=trunc(o.Lng,2) and trunc(m.Lag,2)=trunc(o.Lag,2)
and time between '2019-07-01 7:00:00' and '2019-07-01 9:00:00'
group by m.name;

九.

‘订单信息表’里记录了巴西乘客使用打车软件的信息，包括订单呼叫，应答，取消，完单时间

注意：表中的时间是北京时间，巴西比中国慢11小时；应答时间列的数据值如果是1970年，表示该订单没有司机应答，属于无效订单。

问题：

订单的应答率，完单率分别是多少？
呼叫应答时间有多长？
从这一周的时间来看，呼叫量最多的是哪一个小时(当地时间)？呼叫量最少的是哪一个小时(当地时间)？
呼叫时间第二天继续呼叫的比例有多少？
如果要对乘客进行分类，你认为需要参考哪一些因素？

首先需要将表中的北京时间转换为巴西时间,第一步就是先将表中的时间转换为datetime格式。

update 订单信息表 set call_time=cast(call_time as datetime);
update 订单信息表 set grab_time=cast(grab_time as datetime);
update 订单信息表 set cancel_time=cast(cancel_time as datetime);
update 订单信息表 set finish_time=cast(finish_time as datetime);

接下来，将表中的时间均转换为巴西时间。

update 订单信息表 set call_time=date_add(call_time, interval -11 hour);
update 订单信息表 set grab_time=date_add(grab_time, interval -11 hour);
update 订单信息表 set cancel_time=date_add(cancel_time, interval -11 hour);
update 订单信息表 set cancel_time=date_add(cancel_time, interval -11 hour);

第一问：

应答率=订单应答数/订单呼叫数

由于grab_time列存在时间为1970年的值，视为未应答。

select sum(case when grab_time <> 1970 then 1 else 0 end)/count(call_time)
as response_rate
from 订单信息表；

完单率=完成订单数/呼叫订单数

select sum(case when finish_time <> 1970 then 1 else 0 end)/count(call_time)
as finishing_rate
from 订单信息表；

第二问：

呼叫应答时间=被应答订单从呼叫到被应答时长总和/被应答订单数量

select timestampdiff(minute,grab_time,call_time)/count(call_time)
from 订单信息表
where grab_time<>1970;

第三问：

这里需要将呼叫时间根据呼叫的小时时间分类。因此，可以先添加一列作为小时：

alter table 订单信息表 add column call_hour varchar(255);

然后将call_time中的值转换为小时后加入call_hour列：

update 订单信息表 call_hour=date_format(call_time,'%H');

接下来，就可以将不同小时内的呼叫数求和并使用窗口函数得到下单最多与下单最少的时间：

select call_hour, call_amount, 
first_value(call_hour) over(order by call_amount desc) as call_most,
last_value(call_hour) over(order by call_amount desc 
rows between unbounded preceding and unbounded following) as call_least
(select call_hour, count(call_hour) as call_amount
from 订单信息表
group by call_hour
order by count(call_hour) desc)

第四问：

可以先找出每个用户的第一次下单时间，再通过确定此用户第二天是否下单来找出连续两天下单的用户：

select 
count(distinct t2.passenger_id)/count(distinct t1.passenger_id) as retention_rate
((select passenger_id, min(date(call_time)) as begin_day
from 订单信息表
group by passenger_id)t1
left join
(select passenger_id, date(call_time) as second_day