# Data explorer query to return certain post notices?

**URL:** https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090
**Category:** Data & reporting
**Tags:** post-notices, sql-query
**Created:** [November 18, 2020, 3:07am UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090 "2020-11-18T03:07:16Z")
**Posts on this page:** 1
**Showing post:** 2

<div class="post-metadata">

### Author: ![nathank](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/nathank/32/290039_2.png) [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [November 18, 2020, 11:19pm UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090/2 "2020-11-18T23:19:28Z")

</div>

Answering my own question, here is a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query to pull those users who have most recently done their first post:

```plaintext
-- [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:

> [@Users with first post within period](https://meta.discourse.org/t/users-with-first-post-within-period/275155/4):
>
> 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

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](https://meta.discourse.org/t/return-all-non-pm-topics-that-have-not-received-a-staff-reply/275165) would be awesome but as I’m a SQL newbie it would take me ages to work out how to do it.

---

_[View the full topic](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090)._