How to write an SQL query joining on datetime for each row

user_5892703 0
Viewed 476

I have the following data of data with news (Each article with a publication date and time) and views (Recording each view at a particular timestamp):

news

id, published_timestamp
1, 2018-11-16 13:30:24
2, 2018-12-03 13:59:56
...

views

view_id, post_id, viewed_timestamp
a, 1, 2018-11-16 13:30:24
b, 1, 2018-11-16 13:30:25
c, 1, 2018-11-16 13:30:26
d, 2, 2018-12-03 13:59:56
e, 2, 2018-12-03 13:59:57
f, 2, 2018-12-05 13:59:57
...

Getting lifetime views of each news article would be simply:

SELECT 
post_id, count(view_id) 
FROM views 
GROUP BY post_id

How do I write an SQL query (I'm using AWS Redshift) that can join the views relative to the published datetime? For example, I want to get only views for each article within 1 day of its posting.

amazon-redshift analytics aggregate sql
2 Answers

You need something like this

select n.id, n.published_timestamp,
       count(v.post_id) as views__within_1_day_of_post
from news as n
left join views as v on n.id=v.post_id 
                    and v.viewed_timestamp between n.published_timestamp and n.published_timestamp + interval '1 day'
group by 1, 2
user_63436170 
SELECT V.post_id,  count(V.view_id)
FROM views as V INNER JOIN news as N on N.id = V.post_id
WHERE Date(N.published_timestamp) = Date(V.viewed_timestamp)
GROUP BY V.post_id
  1. Here the Inner Join gets all the views records of post.
  2. the Date() function gets the Date value only from the DATETIME Column
  3. in the WHERE clause I checked, if publish date and view date are same to filter out other days
  4. then the GROUP BY will count the views records, which is only for the published day
user_102701650
Related