Hi there. I’m a SQL newb. I would like to be able to pull a list of first time posters by username within a defined time period.
Note this is the first time they post, not joined. Our use case is different than most so post is important.
Does anyone have something like that? Appreciate any assistance!
THANK YOU!
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.
-- [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
This is super helpful.
How would you go about augmenting this to actually retrieve those posts?
Would something like this meet your needs?
-- [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
That’s perfect. Thanks a ton.
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?
No problem!
That’s correct. The u and us are table aliases. You can read more about them here: PostgreSQL Alias
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.