How can I find count of comments per user per hour in SQL?-CodePudding

I have two tables:

Table1

post_id  | num_comments
3421     | 1
6472     | 3
7294     | 0
4891     | 4

Table2

post_id  | user_id | timestamp
3421     | 100     | 2022-03-30 06:47:30.000000000
6472     | 100     | 2022-03-30 06:48:00.000000000
6472     | 101     | 2022-03-30 06:52:30.000000000
6472     | 101     | 2022-03-30 06:53:30.000000000
4891     | 102     | 2022-03-30 06:57:30.000000000
4891     | 102     | 2022-03-30 06:59:30.000000000
4891     | 103     | 2022-03-30 06:59:45.000000000
4891     | 104     | 2022-03-30 07:01:00.000000000

As you can see, each post_id can have multiple user_id assigned to it. I need to find the count of comments per user_id per hour.

I have tried the below code but it only gives me the count of comments per post per hour, not per user. I need the count per user per hour.

select t1.user_id, date_part(hour, t2.timestamp) as hour, 
    count(t2.num_comments) as comments
from Table1 t1 join Table2 t2 
    on t1.post_id = t2.post_id
group by t1.user_id, t2.timestamp;

CodePudding user response：

Aggregate by user and hour:

SELECT t1.user_id, DATE_FORMAT(t2.timestamp, '%Y-%m-%d %H:00:00') AS hour,
       COUNT(t2.num_comments) AS comments
FROM Table1 t1
INNER JOIN Table2 t2
    ON t1.post_id = t2.post_id
GROUP BY 1, 2;

Note that if you want to guarantee that every hour between two certain dates will appear in your report, and your data might not have every hour present, then you will have to make use of something known as a calendar table, which is a table containing all dates/hours which you want to appear.