# 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:** 6
**Page:** 1
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [November 18, 2020, 3:07am UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090/1 "2020-11-18T03:07:16Z")
Is there a way to extend this feature a little and send a notification to a user (or group) if a First time or Returning notice has been posted?
We’ve got someone who wishes to be in the role of ‘welcoming committee’ but who doesn’t read every post on a large forum. It would be great to have it as a notification for them.
---
### Author:  [@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")
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.
---
### Author:  [@simonk](https://meta.discourse.org/u/simonk)
#### Post date: [November 19, 2020, 12:03am UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090/3 "2020-11-19T00:03:13Z")
> [@nathank](#):
>
> 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.
I think you would want something like:
```sql
AND us.first_post_created_at > NOW() - INTERVAL '7 DAYS'
```
([postgresql date/time docs](https://www.postgresql.org/docs/9.0/functions-datetime.html))
> [@nathank](#):
>
> 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.
To exclude PMs, you need to join to the `topics` table (following the `posts.topic_id` foreign key) and check the `archetype` column. Add this before the `WHERE` clause:
```sql
JOIN topics t ON p.topic_id = t.id
```
…and this before the `ORDER BY`:
```sql
AND t.archetype = 'regular'
```
---
### Author:  [@simonk](https://meta.discourse.org/u/simonk)
#### Post date: [November 19, 2020, 12:20am UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090/4 "2020-11-19T00:20:59Z")
The post notices are actually stored in the `post_custom_fields` table, so you can get a more precise list of posts like this:
```sql
-- [params]
-- int :days_ago = 7
SELECT p.created_at,
p.id AS post_id,
p.user_id,
pcf.value AS "notice type"
FROM post_custom_fields pcf
INNER JOIN posts p ON pcf.post_id = p.id
INNER JOIN topics t ON p.topic_id = t.id
WHERE pcf.name = 'notice_type'
AND p.created_at > NOW() - INTERVAL ':days_ago days'
AND t.archetype = 'regular'
ORDER BY p.created_at ASC
```
---
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [November 19, 2020, 4:33am UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090/5 "2020-11-19T04:33:37Z")
> [@simonk](#):
>
> you can get a more precise list of posts like this:
Absolutely brilliant! Thank you, that is so much better.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [July 10, 2023, 9:30am UTC](https://meta.discourse.org/t/data-explorer-query-to-return-certain-post-notices/271090/6 "2023-07-10T09:30:02Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.