Postgres 13

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

CREATE TABLE orders_table(
  id        int PRIMARY KEY
, email     text
, orderdate timestamp
, paid      bool
);

INSERT INTO orders_table VALUES
  ( 0, 'foo', '2018-12-30 12:00', true)  -- last week of 2018
, ( 1, 'foo', '2018-12-31 12:00', true)  -- week 1 of of 2019
, ( 2, 'bar', '2019-02-02 12:00', true)
, ( 3, 'foo', '2019-02-08 12:00', true)
, ( 4, 'foo', '2019-02-15 12:00', true)
, ( 5, 'foo', '2019-02-21 12:00', true)  -- mutiple orders same week
, ( 6, 'foo', '2019-02-22 12:00', true)
, ( 7, 'foo', '2019-02-23 12:00', true)
, ( 8, 'baz', '2019-03-23 12:00', true)  -- mutliple orders same day
, ( 9, 'baz', '2019-03-23 13:00', true)
, (10, 'baz', '2019-03-23 14:00', true)
, (11, 'XXX', '2019-04-23 12:00', true)  -- 3 new customers same day
, (12, 'YYY', '2019-04-23 13:00', true)
, (13, 'ZZZ', '2019-04-23 14:00', true)
, (14, 'foo', '2020-05-01 12:00', true)  -- 1 year later ...
, (15, 'foo', '2020-05-01 12:00', true)
, (16, 'bar', '2020-05-08 12:00', true)
;
17 rows affected

CREATE OR REPLACE FUNCTION f_weeks_of_year(_year int)
  RETURNS TABLE(year int, week int, week_start timestamp)
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
  ROWS 52 COST 10 AS
$func$
SELECT _year, d.week::int, d.week_start
FROM   generate_series(date_trunc('week', make_date(_year, 01, 04)::timestamp)  -- first day of first week
                     , LEAST(date_trunc('week', localtimestamp), make_date(_year, 12, 28)::timestamp) -- latest possible start of week
                     , interval '1 week')  WITH ORDINALITY d(week_start, week)
$func$;
WITH weekly_customer AS (
   SELECT DISTINCT
          EXTRACT(YEAR FROM orderdate)::int AS year
        , EXTRACT(WEEK FROM orderdate)::int AS week
        , email
   FROM   orders_table
   WHERE  paid
   AND    orderdate >= date_trunc('week', timestamp '2019-01-04')  -- !
   ORDER  BY 1, 2, 3  -- optional, might improve performance
   )
SELECT d.year, d.week
     , (SELECT count(DISTINCT email)
        FROM   weekly_customer w
        WHERE  (w.year, w.week) >= (d.year - 1, d.week)
        AND    (w.year, w.week) <  (d.year    , d.week)  -- exclude current week
       ) AS active_customers
FROM   f_weeks_of_year(2020) d;  -- (year int, week int, week_start timestamp)


year
week
active_customers


2020
1
6

2020
2
6

2020
3
6

2020
4
6

2020
5
6

2020
6
5

2020
7
5

2020
8
5

2020
9
4

2020
10
4

2020
11
4

2020
12
4

2020
13
3

2020
14
3

2020
15
3

2020
16
3

2020
17
3

2020
18
0

2020
19
1

2020
20
2

2020
21
2

2020
22
2

2020
23
2

2020
24
2

2020
25
2

year	week	active_customers
2020	1	6
2020	2	6
2020	3	6
2020	4	6
2020	5	6
2020	6	5
2020	7	5
2020	8	5
2020	9	4
2020	10	4
2020	11	4
2020	12	4
2020	13	3
2020	14	3
2020	15	3
2020	16	3
2020	17	3
2020	18	0
2020	19	1
2020	20	2
2020	21	2
2020	22	2
2020	23	2
2020	24	2
2020	25	2