sql - 在sql中计算每个月的重复客户和新客户的数量

Question

我有 table Order看起来像这样:

order_number   order_created_date   order_completed_date   order_delivered_date    customer_id order_info   gmv
R074828364      8/2/2020 9:53        8/2/2020 10:09           8/2/2020 13:06         1467218    first    126.62
R195440232      20/6/2020 11:56      20/6/2020 12:11          20/6/2020 12:33        2105932    first    69.08
R204790956      20/6/2020 12:45      28/6/2020 9:34           28/6/2020 10:58        2105934    repeat   93.53
R097755601      2/2/2020 7:29        10/2/2020 19:12          11/2/2020 10:53        1183397    repeat   74.26
R862404004      20/6/2020 12:34      15/7/2020 7:42           15/7/2020 9:01          2105934   repeat   114.53

我想用一年中每个月的销售额来计算重复客户和新客户的数量。如果可能，表格如下所示:

MTD  Repeat_cust (count)    Repeat_cust ($) New_cust (count)    New_cust ($)    Total_cust
Jan                 
Feb                 
Mar                 
Apr                 
May 

我已经尝试过，但我仍然无法计算每个回头客和新客户的销售额。加上我在下面使用的代码，我没有得到每个月每个重复客户和新客户的正确计数。有没有一种复杂的方法来做到这一点？

SELECT extract (year from order_completed_date) as SalesYear, extract (month from order_completed_date) as SalesMonth, count(order_info = 'repeat') as Repeat_cust, count(order_info = 'first') as New_cust, count(customer_id) as TotalCust ,SUM(GMV) AS TotalSales
FROM order
where order_completed_date >= '2020-01-01 00:00:00'
GROUP BY salesyear, salesmonth 
ORDER BY salesyear, salesmonth 

这给了我这个输出:

SalesYear   SalesMonth  Repeat_cust New_cust    Total_cust  Total sales
  2020         1           25342     25342        25342      3867478.38
  2020         2           24544     24544        24544      3701199.176
  2020         3           22120     22120        22120      3266824.03
  2020         4           20589     20589        20590      3035031.011
  2020         5           24183     24183        24184      3662249.952
  2020         6           23176     23176        23177      3619393.899
  2020         7           20164     20164        20164      3467183.72
  2020         8           18024     18024        18024      3131129.843
  2020         9           18441     18441        18441      3354984.953


    

Answer 1

试试这个下面的脚本 -

SELECT 
extract (year from order_completed_date) as SalesYear, 
extract (month from order_completed_date) as SalesMonth, 
count(case when order_info = 'repeat' then 1 end) as Repeat_cust, 
count(case when order_info = 'first' then 1 end) as New_cust, 
count(customer_id) as TotalCust ,
SUM(case when order_info = 'repeat' then GMV end) as repeat_Sales, 
SUM(case when order_info = 'first' then GMV end) as new_Sales, 
SUM(GMV) AS TotalSales
FROM order
where order_completed_date >= '2020-01-01 00:00:00'
GROUP BY salesyear, salesmonth 
ORDER BY salesyear, salesmonth