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

数据与报告
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
5 个赞
3

Awesome, thank you!!

1 个赞
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:
https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/top-50-posters.sql

5 个赞
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!

1 个赞
7

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

2 个赞
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?

1 个赞