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

Community Building Data & reporting

AI-generated summary

The discussion is about creating a SQL query to select users who have posted on a specific date or date range. cjk77 initially asks for help with this query. pfaffman provides a link to a related topic, but it doesn’t exactly meet cjk77’s needs. SidV then shares a query that cjk77 finds helpful.

cjk77 eventually comes up with a working query:

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)

This query is later modified for use with a badge. However, due to the added fields, the DISTINCT keyword doesn’t work as expected, resulting in duplicate user IDs. cjk77 is unsure if this is an issue, but believes unchecking the “Badge can be awarded multiple times” option resolves the problem. SidV offers further help if needed.

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!

2
3

Awesome, thank you!!

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:

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:

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?