This should do the trick @ThunderThighs. Add it to your Data Explorer, fill in the start and end date, and you’re off to the races. Dates must match a supported format, one of which is YYYY-MM-DD.
Users with first post within period
-- [params]
-- date :start_date
-- date :end_date
SELECT username
FROM users u
JOIN user_stats us
ON u.id = us.user_id
WHERE us.first_post_created_at BETWEEN :start_date::date AND :end_date::date
-- [params]
-- date :start_date
-- date :end_date
SELECT u.id AS user_id, p.id AS post_id, p.created_at
FROM users u
JOIN user_stats us
ON u.id = us.user_id
JOIN posts p
ON u.id = p.user_id
WHERE p.created_at = us.first_post_created_at
AND us.first_post_created_at BETWEEN :start_date::date AND :end_date::date
Can I ask a bit of a SQL newbie question? What are the u and the us doing on lines two and three? Is it sort of functioning like AS where users can now be referred to as the prefix u and user_stats as the prefix us?
That’s correct. The u and us are table aliases. You can read more about them here: PostgreSQL Table Aliases
It’s essentially shorthand. It allows your to use us.user_id instead of user_stats.user_id to refer to the user_id column in the user_stats table. It’s particularly helpful with long table names and big queries.