Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table mytable(field int)

CREATE TABLE


create table orders(id int,
shop_id int,
taxes int,
shipping int,
total_price int,
total_discount int, 
created_at date, 
active boolean)

CREATE TABLE


select coalesce((select field from mytable where 1=1), 0);


coalesce


0



SELECT 1


INSERT INTO orders values(1,43,100,100,100,10,'2022-07-23',true)

INSERT 0 1


INSERT INTO orders values(1,43,500,200,300,5,'2022-07-21',true)

INSERT 0 1


INSERT INTO orders values(1,43,250,150,200,15,'2022-07-25',true)

INSERT 0 1


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
300
500
200
300.0000000000000000
5
-405

2022-07-23
1
100
100
100
100.0000000000000000
10
-110

2022-07-25
1
200
250
150
200.0000000000000000
15
-215



SELECT 3


SELECT * FROM generate_series('2022-07-20', '2022-07-26', interval '1 day') AS dates
  WHERE dates NOT IN (SELECT created_at FROM orders);


dates


2022-07-20 00:00:00+01

2022-07-22 00:00:00+01

2022-07-24 00:00:00+01

2022-07-26 00:00:00+01



SELECT 4


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
300
500
200
300.0000000000000000
5
-405

2022-07-22 00:00:00+01
0
0
0
0
0
0
0

2022-07-23 00:00:00+01
1
100
100
100
100.0000000000000000
10
-110

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



SELECT 7

date	total_orders	total_price	taxes	shipping	average_order_value	total_discount	net_sales
2022-07-21	1	300	500	200	300.0000000000000000	5	-405
2022-07-23	1	100	100	100	100.0000000000000000	10	-110
2022-07-25	1	200	250	150	200.0000000000000000	15	-215

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	300	500	200	300.0000000000000000	5	-405
2022-07-22 00:00:00+01	0	0	0	0	0	0	0
2022-07-23 00:00:00+01	1	100	100	100	100.0000000000000000	10	-110
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