Postgres 13

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

CREATE TABLE "Users" (
    id bigserial PRIMARY KEY,
    "onboardedAt" timestamp with time zone,
    "closedAt" timestamp with time zone
);

CREATE TABLE "Transactions" (
    id bigserial PRIMARY KEY,
    "userId" bigint,
    "createdAt" timestamp with time zone,
    amount numeric(20,8) NOT NULL DEFAULT 0
);

CREATE TABLE "UserActions" (
    id bigserial PRIMARY KEY,
    "userId" bigint,
    "createdAt" timestamp with time zone,
    action character varying(255) NOT NULL
);


INSERT INTO "Users" ("onboardedAt", "closedAt") VALUES 
    ( '2021-01-01', '2021-02-01' ), 
    ( '2021-01-01', '2021-02-01' ), 
    ( '2021-01-01', '2021-02-01' ), 
    ( '2021-02-01', '2021-03-01' ), 
    ( '2021-02-01', '2021-03-01' );

INSERT INTO "Transactions" ("userId", "createdAt", "amount") VALUES 
    ( 1, '2021-01-02',  100 ), 
    ( 1, '2021-01-08', -100 ), 
    ( 1, '2021-01-15', -200 ),
    ( 1, '2021-01-22',  200 ),

    ( 2, '2021-01-02', -100 ), 
    ( 2, '2021-01-02',  100 ), 
    ( 2, '2021-01-15', -200 ),
    ( 2, '2021-01-16',  200 ), 

    ( 3, '2021-01-02',  100 ), 
    ( 3, '2021-01-08', -100 ), 
    ( 3, '2021-01-15', -200 ),
    ( 3, '2021-01-22',  200 ),

    ( 4, '2021-02-02',   50 ), 
    ( 4, '2021-02-08', -100 ), 
    ( 4, '2021-02-15', -200 ),
    ( 4, '2021-02-22',  200 ),

    ( 5, '2021-02-02',  200 ), 
    ( 5, '2021-02-08', -400 ), 
    ( 5, '2021-02-15', -600 ),
    ( 5, '2021-02-22',  200 );

INSERT INTO "UserActions" ("userId", "createdAt", "action") VALUES 
    ( 1, '2021-01-01', 'PLAY' ), 
    ( 1, '2021-01-01', 'PLAY' ), 
    ( 1, '2021-01-02', 'DEPOSIT' ), 
    ( 1, '2021-01-08', 'DEPOSIT' ), 
    ( 1, '2021-01-09', 'PLAY' ), 
    ( 1, '2021-01-15', 'PLAY' ), 
    ( 1, '2021-01-22', 'PLAY' ), 

    ( 2, '2021-01-01', 'PLAY' ), 
    ( 2, '2021-01-01', 'PLAY' ), 
    ( 2, '2021-01-02', 'DEPOSIT' ), 
    ( 2, '2021-01-08', 'DEPOSIT' ), 
    ( 2, '2021-01-09', 'PLAY' ), 
    ( 2, '2021-01-15', 'PLAY' ), 
    ( 2, '2021-01-22', 'PLAY' ), 

    ( 3, '2021-01-01', 'PLAY' ), 
    ( 3, '2021-01-01', 'PLAY' ), 
    ( 3, '2021-01-02', 'DEPOSIT' ), 
    ( 3, '2021-01-08', 'DEPOSIT' ), 
    ( 3, '2021-01-09', 'PLAY' ), 
    ( 3, '2021-01-15', 'PLAY' ), 
    ( 3, '2021-01-22', 'PLAY' ), 

    ( 4, '2021-02-01', 'DEPOSIT' ), 
    ( 4, '2021-02-01', 'PLAY' ), 
    ( 4, '2021-02-02', 'DEPOSIT' ), 
    ( 4, '2021-02-08', 'DEPOSIT' ), 
    ( 4, '2021-02-09', 'PLAY' ), 
    ( 4, '2021-02-15', 'PLAY' ), 
    ( 4, '2021-02-22', 'PLAY' ), 

    ( 5, '2021-02-01', 'DEPOSIT' ), 
    ( 5, '2021-02-01', 'PLAY' ), 
    ( 5, '2021-02-02', 'PLAY' ), 
    ( 5, '2021-02-08', 'PLAY' ), 
    ( 5, '2021-02-09', 'PLAY' ), 
    ( 5, '2021-02-15', 'DEPOSIT' ), 
    ( 5, '2021-02-22', 'PLAY' );

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 5



INSERT 0 20



INSERT 0 35


-- org
SELECT 
    usr.id as user_id,
    usr."onboardedAt" as user_opened_account_at,
    usr."closedAt" as user_closed_account_at,
    '2021-01-01' as start_range_date,
    '2021-01-08' as end_range_date,
    tx.tx_count as tx_count,
    last_user_action.action as last_user_action
FROM "Users" usr

LEFT JOIN (
    SELECT 
        "userId",
        COUNT("id") as "tx_count"
    FROM "Transactions"
    WHERE "createdAt" >= '2021-01-01' AND "createdAt" < '2021-01-08'
    GROUP BY "userId"
) tx ON usr.id = tx."userId"

LEFT JOIN (
    SELECT "userId", "action"
    FROM "UserActions"
    WHERE "createdAt" >= '2021-01-01' AND "createdAt" < '2021-01-08'
    ORDER BY "createdAt" DESC 
    LIMIT 1
) last_user_action ON usr.id = last_user_action."userId"

WHERE usr.id = 1
ORDER BY user_id, start_range_date


user_id
user_opened_account_at
user_closed_account_at
start_range_date
end_range_date
tx_count
last_user_action


1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-01
2021-01-08
1
DEPOSIT



SELECT 1


SELECT id AS user_id, u."onboardedAt", u."closedAt"
     , week_start, COALESCE(t.tx_count, 0) AS tx_count, a.last_user_action
FROM   "Users" u
CROSS  JOIN  generate_series(date_trunc('week', u."onboardedAt"), u."closedAt", interval '1 week') AS week_start
LEFT   JOIN (
   SELECT "userId" AS id, date_trunc('week', t."createdAt") AS week_start, count(*) AS tx_count
   FROM   "Transactions" t
   GROUP  BY 1, 2
   ) t  USING (id, week_start)
LEFT   JOIN (
   SELECT DISTINCT ON (1, 2)
          "userId" AS id, date_trunc('week', a."createdAt") AS week_start, action AS last_user_action
   FROM   "UserActions" a
   ORDER  BY 1, 2, "createdAt" DESC
   ) a USING (id, week_start)
ORDER  BY id, week_start;


user_id
onboardedAt
closedAt
week_start
tx_count
last_user_action


1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2020-12-28 00:00:00+00
1
DEPOSIT

1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-04 00:00:00+00
1
PLAY

1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-11 00:00:00+00
1
PLAY

1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-18 00:00:00+00
1
PLAY

1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-25 00:00:00+00
0
null

1
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-02-01 00:00:00+00
0
null

2
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2020-12-28 00:00:00+00
2
DEPOSIT

2
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-04 00:00:00+00
0
PLAY

2
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-11 00:00:00+00
2
PLAY

2
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-18 00:00:00+00
0
PLAY

2
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-25 00:00:00+00
0
null

2
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-02-01 00:00:00+00
0
null

3
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2020-12-28 00:00:00+00
1
DEPOSIT

3
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-04 00:00:00+00
1
PLAY

3
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-11 00:00:00+00
1
PLAY

3
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-18 00:00:00+00
1
PLAY

3
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-01-25 00:00:00+00
0
null

3
2021-01-01 00:00:00+00
2021-02-01 00:00:00+00
2021-02-01 00:00:00+00
0
null

4
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-01 00:00:00+00
1
DEPOSIT

4
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-08 00:00:00+00
1
PLAY

4
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-15 00:00:00+00
1
PLAY

4
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-22 00:00:00+00
1
PLAY

4
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-03-01 00:00:00+00
0
null

5
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-01 00:00:00+00
1
PLAY

5
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-08 00:00:00+00
1
PLAY

5
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-15 00:00:00+00
1
DEPOSIT

5
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-02-22 00:00:00+00
1
PLAY

5
2021-02-01 00:00:00+00
2021-03-01 00:00:00+00
2021-03-01 00:00:00+00
0
null



SELECT 28

user_id	onboardedAt	closedAt	week_start	tx_count	last_user_action
1	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2020-12-28 00:00:00+00	1	DEPOSIT
1	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-04 00:00:00+00	1	PLAY
1	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-11 00:00:00+00	1	PLAY
1	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-18 00:00:00+00	1	PLAY
1	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-25 00:00:00+00	0	null
1	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-02-01 00:00:00+00	0	null
2	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2020-12-28 00:00:00+00	2	DEPOSIT
2	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-04 00:00:00+00	0	PLAY
2	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-11 00:00:00+00	2	PLAY
2	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-18 00:00:00+00	0	PLAY
2	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-25 00:00:00+00	0	null
2	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-02-01 00:00:00+00	0	null
3	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2020-12-28 00:00:00+00	1	DEPOSIT
3	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-04 00:00:00+00	1	PLAY
3	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-11 00:00:00+00	1	PLAY
3	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-18 00:00:00+00	1	PLAY
3	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-01-25 00:00:00+00	0	null
3	2021-01-01 00:00:00+00	2021-02-01 00:00:00+00	2021-02-01 00:00:00+00	0	null
4	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-01 00:00:00+00	1	DEPOSIT
4	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-08 00:00:00+00	1	PLAY
4	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-15 00:00:00+00	1	PLAY
4	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-22 00:00:00+00	1	PLAY
4	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-03-01 00:00:00+00	0	null
5	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-01 00:00:00+00	1	PLAY
5	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-08 00:00:00+00	1	PLAY
5	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-15 00:00:00+00	1	DEPOSIT
5	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-02-22 00:00:00+00	1	PLAY
5	2021-02-01 00:00:00+00	2021-03-01 00:00:00+00	2021-03-01 00:00:00+00	0	null