电商订单量统计-SQL题(2)

题目来源：

每天一道大厂SQL题【Day03】订单量统计_Maynor996的博客-CSDN博客

该文章只为记录自己的学习历程，侵删！

需求:

已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。
数据样例:2017-01-01,10029028,1000003251,33.57。

请给出sql进行统计:

(1) 给出 2017年每个月的订单数、用户数、总成交金额。
(2)给出2017年11月的新客数(指在11月才有第一笔订单)
准备:

CREATE TABLE backup.test_230427_lyq (
dt string, order_id string, user_id string,
amount DECIMAL ( 10, 2 ) ) ROW format delimited FIELDS TERMINATED BY '\t';

INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-01-01','10029028','1000003251',33.57);
 INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-01-01','10029029','1000003251',33.57); 
 INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-01-01','100290288','1000003252',33.57); 
 INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-02-02','10029088','1000003251',33.57); 
 INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-02-02','100290281','1000003251',33.57); 
 INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-02-02','100290282','1000003253',33.57); 
 INSERT INTO TABLE backup.test_230427_lyq VALUES ('2017-11-02','10290282','100003253',234);
INSERT INTO TABLE backup.test_230427_lyq VALUES ('2018-11-02','10290284','100003243',234);

实现:

-- 给出 2017年每个月的订单数、用户数、总成交金额。
select 
  month
  ,sum(order_tol) as order_tol
  ,sum(amount_sum) as amount_sum
  ,count(1) as user_tol
from (
  select
    substr(dt,1,7) as month
    ,count(order_id) as order_tol  
    ,user_id-- 实际要简单的写法，这里直接count(disitnct order_id) 一步即可到位，但是不推荐使用distinct
    ,sum(amount) as amount_sum
  from backup.test_230427_lyq
  where substr(dt,1,4) = '2017'
  group by substr(dt,1,7),user_id
) t group by month
order by month

-- 给出2017年11月的新客数(指在用户只在11月才有第一笔订单)
select 
  dt,count(1) cnt
from (
  select 
    dt,user_id
  from (
  select
    substr(dt,1,7) as dt
    ,user_id
    ,min(substr(dt,1,7)) over(partition by user_id order by substr(dt,1,7)) aS min_dt
  from backup.test_230427_lyq
  where substr(dt,1,4) = '2017'
  ) t1 where min_dt >= '2017-11'
  group by dt,user_id
) t2
where dt = '2017-11'
group by dt