Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 9.6.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table "order"(order_number varchar, order_created_date timestamp,  order_completed_date  timestamp,  order_delivered_date  timestamp,   customer_id  bigint, order_info varchar,   gmv numeric(10,2));

insert into "order" values 
('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)
5 rows affected

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 


salesyear
salesmonth
repeat_cust
new_cust
totalcust
repeat_sales
new_sales
totalsales


2020
2
1
1
2
74.26
126.62
200.88

2020
6
1
1
2
93.53
69.08
162.61

2020
7
1
0
1
114.53
null
114.53


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


SalesYear
SalesMonth
Repeat_cust (count)
Repeat_cust ($)
New_cust (count)
New_cust ($)
TotalCust
TotalSales


2020
2
1
74.26
1
126.62
2
200.88

2020
6
1
93.53
1
69.08
2
162.61

2020
7
1
114.53
0
null
1
114.53
salesyear	salesmonth	repeat_cust	new_cust	totalcust	repeat_sales	new_sales	totalsales
2020	2	1	1	2	74.26	126.62	200.88
2020	6	1	1	2	93.53	69.08	162.61
2020	7	1	0	1	114.53	null	114.53