SQL经典面试题

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

字段说明：

• 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

结果如下：

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

结果如下：

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)

结果如下：

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

结果如下：

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

结果如下：

以上SQL若存在错误或者大家有更好的方案，欢迎交流和指正