sql面试题汇总
一、武汉-叽里呱啦-2020-09-25
点击查看
1、数据库data表 users_info
creat table user_info(
user_id varchar(50),--用户id，主键
user_name varchar(50),--用户姓名
sales_id varchar(50),--销售id
sales_name varchar(50),--销售姓名
sales_department varchar(50),--销售所属部门
xx_first_cts datetime --用户首购时间
)
2、数据库表data表 order_info
creat table order_info(
order_id varchar(50),--订单号，主键
goods_name varchar(50),--商品名
user_id varchar(50),--用户id
ispaid varchar(50),--是否支付（'是'，'否'）
pay_amount float,--实际支付金额
pay_time datetime --支付时间
)
问题1：求每个部门复购率前三的销售复购数据
用户对象：首购在2020年7月的用户
复购逻辑：商品名中存在'规划师'关键词且在首购30天之内的订单
字段如下：
| 部门 | 销售id | 销售姓名 | 首购人数 | 复购人数 | 复购率 | 复购金额 |
|---|
代码
点击查看
SELECT
*,
row_number() over(partition by sales_department order by repurchase_rate desc) as rn
FROM
(SELECT
sales_department,
sales_id,
sales_name,
count(distinct t1.user_id) as first_user_num,
count(distinct t2.user_id) as repurchase_user_num,
count(distinct t2.user_id)/count(distinct t1.user_id) AS repurchase_rate,
sum(pay_amount) as pay_amount
FROM
(SELECT
DISTINCT
sales_department,
sales_id,
sales_name,
user_id,
xx_first_cts
FROM
users_info
WHERE
xx_first_cts >='2020-07-01' and xx_first_cts <'2020-08-01'
)t1
left join
(
SELECT
user_id,
pay_time,
sum(pay_amount) as pay_amount
FROM
order_info
WHERE
goods_name like '%规划师%'
and ispaid='是'
and pay_time >='2020-07-01' and pay_time <'2020-09-01'
group by
user_id,
pay_time
)t2 on t1.user_id=t2.user_id
WHERE
DATEDIFF(day,t1.xx_first_cts,t2.pay_time)<='30'
group by
sales_department,
sales_id,
sales_name
) t
WHERE
rn<=3