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?.
create table mytable(field int)
create table orders(id int,
shop_id int,
taxes int,
shipping int,
total_price int,
total_discount int,
created_at timestamp,
active boolean)
INSERT INTO orders values(1,43,100,100,100,10,'2022-07-23 10:05:20',true)
1 rows affected
INSERT INTO orders values(1,43,100,100,100,10,'2022-07-23 20:15:20',true)
1 rows affected
INSERT INTO orders values(1,43,100,100,100,10,'2022-07-21 05:25:20',true)
1 rows affected
INSERT INTO orders values(1,43,500,200,300,5,'2022-07-22 08:25:20',true)
1 rows affected
INSERT INTO orders values(1,43,250,150,200,15,'2022-07-25 19:25:20',true)
1 rows affected
SELECT date(created_at),
COALESCE(COUNT(id), 0) AS total_orders,
COALESCE(SUM(total_price), 0) AS total_price,
SUM(COALESCE(taxes, 0)) AS taxes,
SUM(COALESCE(shipping, 0)) AS shipping,
AVG(COALESCE(total_price, 0)) AS average_order_value,
SUM(COALESCE(total_discount, 0)) AS total_discount,
SUM(total_price - COALESCE(taxes, 0) - COALESCE(shipping, 0) - COALESCE(total_discount, 0)) as net_sales
FROM orders
WHERE shop_id = 43
AND orders.active = true
AND orders.created_at >= '2022-07-20'
AND orders.created_at <= '2022-07-26'
GROUP BY date (created_at)
| date | total_orders | total_price | taxes | shipping | average_order_value | total_discount | net_sales |
|---|---|---|---|---|---|---|---|
| 2022-07-21 | 1 | 100 | 100 | 100 | 100.0000000000000000 | 10 | -110 |
| 2022-07-22 | 1 | 300 | 500 | 200 | 300.0000000000000000 | 5 | -405 |
| 2022-07-23 | 2 | 200 | 200 | 200 | 100.0000000000000000 | 20 | -220 |
| 2022-07-25 | 1 | 200 | 250 | 150 | 200.0000000000000000 | 15 | -215 |
SELECT * FROM generate_series('2022-07-20', '2022-07-26', interval '1 day') AS dates
WHERE dates NOT IN (SELECT date(created_at) FROM orders);
| dates |
|---|
| 2022-07-20 00:00:00+01 |
| 2022-07-24 00:00:00+01 |
| 2022-07-26 00:00:00+01 |
SELECT *
FROM
(SELECT date(created_at) AS created_at,
COUNT(id) AS total_orders,
SUM(total_price) AS total_price,
SUM(taxes) AS taxes,
SUM(shipping) AS shipping,
AVG(total_price) AS average_order_value,
SUM(total_discount) AS total_discount,
SUM(total_price - taxes - shipping - total_discount) AS net_sales
FROM orders
WHERE shop_id = 43
AND orders.active = true
AND orders.created_at >= '2022-07-20'
AND orders.created_at <= '2022-07-26'
GROUP BY date (created_at)
UNION
SELECT dates AS created_at,
0 AS total_orders,
0 AS total_price,
0 AS taxes,
0 AS shipping,
0 AS average_order_value,
0 AS total_discount,
0 AS net_sales
FROM generate_series('2022-07-20', '2022-07-26', interval '1 day') AS dates
WHERE dates NOT IN
(SELECT created_at
FROM orders
WHERE shop_id = 43
AND orders.active = true
AND orders.created_at >= '2022-07-20'
AND orders.created_at <= '2022-07-26' ) ) a
ORDER BY created_at;
| created_at | total_orders | total_price | taxes | shipping | average_order_value | total_discount | net_sales |
|---|---|---|---|---|---|---|---|
| 2022-07-20 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-21 00:00:00+01 | 1 | 100 | 100 | 100 | 100.0000000000000000 | 10 | -110 |
| 2022-07-21 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-22 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-22 00:00:00+01 | 1 | 300 | 500 | 200 | 300.0000000000000000 | 5 | -405 |
| 2022-07-23 00:00:00+01 | 2 | 200 | 200 | 200 | 100.0000000000000000 | 20 | -220 |
| 2022-07-23 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-24 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-25 00:00:00+01 | 1 | 200 | 250 | 150 | 200.0000000000000000 | 15 | -215 |
| 2022-07-25 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-26 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
SELECT *
FROM
(SELECT date(created_at) AS created_at,
COUNT(id) AS total_orders,
SUM(total_price) AS total_price,
SUM(taxes) AS taxes,
SUM(shipping) AS shipping,
AVG(total_price) AS average_order_value,
SUM(total_discount) AS total_discount,
SUM(total_price - taxes - shipping - total_discount) AS net_sales
FROM orders
WHERE shop_id = 43
AND orders.active = true
AND orders.created_at >= '2022-07-20'
AND orders.created_at <= '2022-07-26'
GROUP BY date (created_at)
UNION
SELECT dates AS created_at,
0 AS total_orders,
0 AS total_price,
0 AS taxes,
0 AS shipping,
0 AS average_order_value,
0 AS total_discount,
0 AS net_sales
FROM generate_series('2022-07-20', '2022-07-26', interval '1 day') AS dates
WHERE dates NOT IN
(SELECT date (created_at)
FROM orders
WHERE shop_id = 43
AND orders.active = true
AND orders.created_at >= '2022-07-20'
AND orders.created_at <= '2022-07-26' ) ) a
ORDER BY created_at;
| created_at | total_orders | total_price | taxes | shipping | average_order_value | total_discount | net_sales |
|---|---|---|---|---|---|---|---|
| 2022-07-20 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-21 00:00:00+01 | 1 | 100 | 100 | 100 | 100.0000000000000000 | 10 | -110 |
| 2022-07-22 00:00:00+01 | 1 | 300 | 500 | 200 | 300.0000000000000000 | 5 | -405 |
| 2022-07-23 00:00:00+01 | 2 | 200 | 200 | 200 | 100.0000000000000000 | 20 | -220 |
| 2022-07-24 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022-07-25 00:00:00+01 | 1 | 200 | 250 | 150 | 200.0000000000000000 | 15 | -215 |
| 2022-07-26 00:00:00+01 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |