Can anyone help me figure out how to select users who’ve posted on a particular date or range of dates?
Thanks in advance!
Can anyone help me figure out how to select users who’ve posted on a particular date or range of dates?
Thanks in advance!
Awesome, thank you!!
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.
Mmmm maybe this query:
https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/top-50-posters.sql
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!
Sure, you should try, and If you need help, share your query here and we can help you.
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?