SQL query for users who have posted on a date (or date range)


(Chris Klugewicz) #1

Can anyone help me figure out how to select users who’ve posted on a particular date or range of dates?

Thanks in advance!


(Jay Pfaffman) #2

(Chris Klugewicz) #3

Awesome, thank you!!


(Chris Klugewicz) #4

Hmmm … I’m not sure that will do exactly what I’m looking for – say I want to query for users who’ve posted on January 1st, for example. Assuming the user has posted since then, the last_posted_at field won’t be helpful in that case.


#5

Mmmm maybe this query:


(Chris Klugewicz) #6

Ah, that’s very helpful! I haven’t had time to tweak it, but it looks like just what I need to build the query I’m looking for! Thank you!


#7

Sure, you should try, and If you need help, share your query here and we can help you. :+1:


(Chris Klugewicz) #8

So it turns out to be easier than I thought! This seems to accomplish what I wanted:

SELECT DISTINCT p.user_id AS user_id
FROM posts p
WHERE p.created_at > make_timestamp(2018, 10, 16, 0, 0, 0)
AND p.created_at < make_timestamp(2018, 10, 17, 0, 0, 0)

When I rewrote the query for use with a badge, it looked like this:

SELECT DISTINCT p.user_id AS user_id, created_at AS granted_at, NULL AS post_id
FROM posts p
WHERE p.created_at > make_timestamp(2018, 10, 15, 0, 0, 0)
AND p.created_at  < make_timestamp(2018, 10, 16, 0, 0, 0)

The “DISTINCT” keyword doesn’t work quite so well here because of the added fields, so I get dupes of the various users who’ve posted during the specified time period. I’ve unchecked the “Badge can be awarded multiple times” option, so I think that’s ok. Am I right?