Postgres 14

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

CREATE TABLE orders (
  id int
, shop_id int
, taxes int
, shipping int
, total_price int
, total_discount int
, created_at timestamp
, active bool
);

CREATE TABLE


INSERT INTO orders VALUES
  (1,43,100,100,100,10,'2022-07-23 10:05:20',true)
, (1,43,100,100,100,10,'2022-07-23 20:15:20',true)
, (1,43,100,100,100,10,'2022-07-21 05:25:20',true)
, (1,43,500,200,300, 5,'2022-07-22 08:25:20',true)
, (1,43,250,150,200,15,'2022-07-25 19:25:20',true)
, (1,43,250,150,200,15,'2022-07-26 17:00:00',true)  -- !!!
;

INSERT 0 6


SELECT *, total_price - taxes - shipping - total_discount AS net_sales
FROM  (
   SELECT created_at
        , COALESCE(total_orders        , 0) AS total_orders
        , COALESCE(total_price         , 0) AS total_price
        , COALESCE(taxes               , 0) AS taxes
        , COALESCE(shipping            , 0) AS shipping
        , COALESCE(average_order_value , 0) AS average_order_value
        , COALESCE(total_discount      , 0) AS total_discount
   FROM   generate_series(timestamp '2022-07-20'  -- !
                        , timestamp '2022-07-26'
                        , interval '1 day') AS g(created_at)
   LEFT  JOIN (  -- !
      SELECT created_at::date
           , count(*)            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
      FROM   orders
      WHERE  shop_id = 43
      AND    active  -- simpler
      AND    created_at >= '2022-07-20'
      AND    created_at <  '2022-07-27'  -- !!!
      GROUP  BY 1
      ) o USING (created_at)  -- !
   ) sub
ORDER  BY created_at DESC;


created_at
total_orders
total_price
taxes
shipping
average_order_value
total_discount
net_sales


2022-07-26 00:00:00
1
200
250
150
200.0000000000000000
15
-215

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

2022-07-24 00:00:00
0
0
0
0
0
0
0

2022-07-23 00:00:00
2
200
200
200
100.0000000000000000
20
-220

2022-07-22 00:00:00
1
300
500
200
300.0000000000000000
5
-405

2022-07-21 00:00:00
1
100
100
100
100.0000000000000000
10
-110

2022-07-20 00:00:00
0
0
0
0
0
0
0



SELECT 7


-- Xu's query (with fixed sort order)
-- Note the 0 result for '2022-07-26' - probably not what you want
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 desc;


created_at
total_orders
total_price
taxes
shipping
average_order_value
total_discount
net_sales


2022-07-26 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-24 00:00:00+01
0
0
0
0
0
0
0

2022-07-23 00:00:00+01
2
200
200
200
100.0000000000000000
20
-220

2022-07-22 00:00:00+01
1
300
500
200
300.0000000000000000
5
-405

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

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



SELECT 7

created_at	total_orders	total_price	taxes	shipping	average_order_value	total_discount	net_sales
2022-07-26 00:00:00	1	200	250	150	200.0000000000000000	15	-215
2022-07-25 00:00:00	1	200	250	150	200.0000000000000000	15	-215
2022-07-24 00:00:00	0	0	0	0	0	0	0
2022-07-23 00:00:00	2	200	200	200	100.0000000000000000	20	-220
2022-07-22 00:00:00	1	300	500	200	300.0000000000000000	5	-405
2022-07-21 00:00:00	1	100	100	100	100.0000000000000000	10	-110
2022-07-20 00:00:00	0	0	0	0	0	0	0

created_at	total_orders	total_price	taxes	shipping	average_order_value	total_discount	net_sales
2022-07-26 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-24 00:00:00+01	0	0	0	0	0	0	0
2022-07-23 00:00:00+01	2	200	200	200	100.0000000000000000	20	-220
2022-07-22 00:00:00+01	1	300	500	200	300.0000000000000000	5	-405
2022-07-21 00:00:00+01	1	100	100	100	100.0000000000000000	10	-110
2022-07-20 00:00:00+01	0	0	0	0	0	0	0