SQL经典面试题

根据如下订单表orders的字段和类型，按要求写出满足条件的SQL语句：

order_id	user_id	product_id	paid_time	is_refunded
1001	123	A	2023-10-24 11:14:07	0
1002	123	B	2023-10-25 18:03:24	0
1003	234	C	2023-11-11 00:03:32	1
1004	456	D	2023-11-11 01:10:01	0
1005	234	A	2023-12-20 16:09:50	1
1006	456	B	2023-12-21 17:24:12	0
1007	123	A	2023-12-31 15:20:21	0
1008	234	C	2023-12-31 19:13:30	0

字段说明：

order_id：订单ID，String类型
user_id：用户ID，String类型
product_id：商品ID，String类型
paid_time：付款时间，String类型
is_refunded：是否退款，1表示退款，0表示未退款，Bigint类型

1、数据准备

create table orders (
    order_id string,
    user_id string,
    product_id string,
    paid_time string,
    is_refunded bigint
);

insert into orders values
('1001', '123', 'A', '2023-10-24 11:14:07', 0),
('1002', '123', 'B', '2023-10-25 18:03:24', 0),
('1003', '234', 'C', '2023-11-11 00:03:32', 1),
('1004', '456', 'D', '2023-11-11 01:10:01', 0),
('1005', '234', 'A', '2023-12-20 16:09:50', 1),
('1006', '456', 'B', '2023-12-21 17:24:12', 0),
('1007', '123', 'A', '2023-12-31 15:20:21', 0),
('1008', '234', 'C', '2023-12-31 19:13:30', 0);

select * from orders order by order_id;

2、题目描述与题解

1）查询购买过每种商品的总人数（不限时间、退款与否）

select product_id,count(distinct user_id) cnt from orders group by product_id

结果如下：

product_id	cnt
A	2
D	1
B	2
C	1

2）查询2023-11-01及之后购买过商品C超过1次的用户（不限退款与否）

select user_id,count(1) cnt 
from orders where substr(paid_time, 0, 11) >= '2023-11-01' and product_id='C' 
group by user_id having cnt>1

结果如下：

user_id	cnt
234	2

3）查询2023-11-01及之后每天的总订单数和退款率

-- 方式1
select substr(paid_time, 0, 11) dt,count(order_id) nums,count(case when is_refunded=1 then is_refunded end)/count(is_refunded) refund_rate
from orders where substr(paid_time, 0, 11) >= '2023-11-01' 
group by substr(paid_time, 0, 11)

-- 方式2
select substr(paid_time, 0, 11) dt,count(order_id) nums,count(case when is_refunded=1 then 1 end)/count(is_refunded) refund_rate
from orders where substr(paid_time, 0, 11) >= '2023-11-01' 
group by substr(paid_time, 0, 11)

-- 方式3（注意：if(is_refunded='1',1)不能正确计算）
select substr(paid_time, 0, 11) dt,count(order_id) nums,count(if(is_refunded=1, is_refunded))/count(is_refunded) refund_rate
from orders where substr(paid_time, 0, 11) >= '2023-11-01' 
group by substr(paid_time, 0, 11)

结果如下：

dt	nums	refund_rate
2023-11-11	2	0.5
2023-12-31	2	0.0
2023-12-20	1	1.0
2023-12-21	1	0.0

4）查询日退款率前3的商品及对应退款率（不限时间）

select substr(paid_time, 0, 11) dt,count(if(is_refunded=1, is_refunded))/count(is_refunded) refund_rate
from orders group by substr(paid_time, 0, 11) order by refund_rate desc limit 3

结果如下：

dt	refund_rate
2023-12-20	1.0
2023-11-11	0.5
2023-10-24	0.0

5）查询每个用户购买每种商品的最后一次未退款的记录（结果仅包含表中字段）

-- 方式1
select order_id,user_id,product_id,paid_time,is_refunded from (
    select *,row_number() over(partition by user_id,product_id order by paid_time desc) rk 
    from orders where is_refunded=0
) t where t.rk=1

-- 方式2
select order_id,user_id,product_id,paid_time,is_refunded from (
    select *,first_value(paid_time) over(partition by user_id,product_id order by paid_time desc) last_paid_time 
    from orders where is_refunded=0
) t where t.paid_time=t.last_paid_time