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

[image] Active users in the last 30 days Data & reporting Active users in the last 30 days select username from users where last_posted_at > current_timestamp - interval '30' day

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: queries/top-50-posters.sql queries -- https://meta.discourse.org/t/74574/5?u=sidv -- Top 50 posters -- Returns the top 50 posters for a given monthly period. -- Results are ordered by post_count. It accepts a ‘months_ago’ parameter, defaults to 1 to give results for the most -- recently completed calendar month. -- [params] -- int :months_ago = 1 WITH query_period AS ( SELECT date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start, date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end ), user_posts_in_period AS ( SELECT p.user_id FROM posts p INNER JOIN query_period qp ON p.created_at >= qp.period_start This file has been truncated. show original

[image] cjk77: it looks like just what I need to build the query I’m looking for! Sure, you should try, and If you need help, share your query here and we can help you. :+1:

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, 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?

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

Community Building Data & reporting

cjk77 (Chris Klugewicz) October 14, 2018, 1:12am 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!

Topic		Replies	Views
Active users in the last 30 days Data & reporting sql-query	3	477	September 5, 2016
Data explorer query for all active users (Lurkers + Posters) Data & reporting sql-query	5	1113	November 25, 2024
SQL query for last created users of the past 30 days Product Support	2	4547	August 15, 2022
Badge for posts created between 1 AM and 5 AM Development badges	10	1379	February 8, 2021
Users with first post within period Data & reporting sql-query	5	370	September 18, 2019

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

Related topics