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

cjk77 · 2018-10-11 16:00:37 UTC

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

Thanks in advance!

pfaffman · 2018-10-12 12:02:27 UTC

cjk77 · 2018-10-11 19:41:20 UTC

Awesome, thank you!!

cjk77 · 2018-10-12 12:04:41 UTC

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.

SidV · 2018-10-12 12:24:30 UTC

Mmmm maybe this query:

github.com

SidVal/discourse-data-explorer/blob/queries/queries/top-50-posters.sql

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

cjk77 · 2018-10-14 01:12:49 UTC

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!

SidV · 2018-10-15 22:41:01 UTC

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

cjk77 · 2018-10-18 11:30:06 UTC

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?