I have table A and table B. Each row in table A represents every time a user sends a message. Each row in table B represents every time a user buys a gift.

Goal: for each time a user sends a message, calculate how many gifts they've purchased within 7 days before the timestamp they sent the message. Some users never send messages and some never purchased gifts. If the user in table A didn't have gift purchased within 7 days, the count should be 0.

Table A:

Table B:

Expected output:

Query I tried:

SELECT A.user_id, A.time, coalesce(count(*), 0) as count
FROM A
LEFT JOIN B ON A.user_id = B.user_id AND B.time >= A.time - INTERVAL '7 days' AND B.time < A.time
GROUP BY 1,2

The count returned doesn't match the expected result however, not sure if I'm doing the join and conditions correctly.

CodePudding user response：

You need to count the values from the possibly NULL columns i.e. from table B in order to get the correct counts of non-existent matches. i.e. being more specific in COUNT(*) to COUNT(b.column_from_b_table). See modification with working demo fiddle below:

SELECT 
    A.user_id, 
    A.time, 
    coalesce(count(B.user_id), 0) as count
FROM A
LEFT JOIN B ON A.user_id = B.user_id AND 
               B.time >= A.time - INTERVAL '7 days' AND 
               B.time < A.time
GROUP BY 1,2;

View on DB Fiddle

Let me know if this works for you.