揽收表统计客户快递单量分布情况-SQL面试题

SQL 查询：请根据揽收表写出单量分布的SQL查询代码；

揽收表：

计算创建日期在2022年5月份的单量分布：

单量分布的表头示列：

 解答：

with count_SQL as(
select
client_id,
count(order_id) as num
from
揽收表
where to_char(create_time,'yyyymmdd')='202205'
group by client_id
)
select 
decode(qj,1,'1-5',2,'6-10',3,'11-20',4,'20以上') as '单量区间',
count(client_id) as '客户数量'
from (
select 
client_id,
num,
case 
when num<=5 then 1,
when num>5 and num <=10 then 2,
when num>10 and num<=20 then 3,
when num>20 then 4 end as qj
from count_SQL
)a
group by decode(qj,1,'1-5',2,'6-10',3,'11-20',4,'20以上')