By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 |