Counting the number of repeating customer and new customer for every month in sql

Question

I have table Order looks like this:

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

I would like to count the number of repeated customer and new customer with the sales amount for every month of the year. If possible, a table looks like this:

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

I have tried but I still unable to calculate the sales of each repeated customer and new customer. Plus with the code that I use below, I didn't get the correct count for each repeated customer and new customer for each month. Is there a sophiscated way to do so?

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 

which give me this output:

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

Try this below script-

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 

Answer 2

You can use filter clause like below:

SELECT 
extract (year from order_completed_date) as "SalesYear", 
extract (month from order_completed_date) as "SalesMonth", 
count(*) filter (where order_info = 'repeat') as "Repeat_cust (count)", 
sum(gmv) filter (where order_info = 'repeat') as "Repeat_cust ($)",
count(*) filter (where order_info = 'first') as "New_cust (count)", 
sum(gmv) filter (where order_info = 'first') as "New_cust ($)",
count(*)  as "TotalCust" ,
SUM(gmv) AS "TotalSales"
FROM "order"
where order_completed_date >= '2020-01-01 00:00:00'
GROUP BY 1,2
ORDER BY 1,2

DEMO