Data explorer query to return certain post notices?

Data & reporting
,
2

Answering my own question, here is a Data Explorer query to pull those users who have most recently done their first post:

-- [params]
-- date :start_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 NOW()
ORDER BY us.first_post_created_at desc

This is a minor modification of this query by @tshenry:

It could be improved by having a set time which it looks back from, e.g. one week or one month - but this defeated me as I couldn’t work out how to get NOW() - 7 or the like to work.

Also, excluding PMs as per another of @tshenry’s queries would be awesome but as I’m a SQL newbie it would take me ages to work out how to do it.

7 Likes