# Badge granted to our staff members that have read the guidelines placed in our hidden staff category
**URL:** https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [24 februari 2015 om 15:15 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721 "2015-02-24T15:15:55Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [24 februari 2015 om 15:15 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/1 "2015-02-24T15:15:55Z")
We’d like to have a badge granted to our staff members that have read the guidelines placed in our hidden staff category.
This would (presumably) be very similar to the “Read Guidelines” badge:
```
SELECT user_id, read_faq granted_at
FROM user_stats
WHERE read_faq IS NOT NULL AND (user_id IN (:user_ids) OR :backfill)
```
With a modification to allow us to specify specific topics or post, and add more in the future.
Could anyone help with that?
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [26 februari 2015 om 13:41 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/2 "2015-02-26T13:41:20Z")
> [@john\_mardlin](#):
>
> This would (presumably) be very similar to the “Read Guidelines” badge:
Unlikely because `read_faq` is ‘special’ and not reprogrammable or extendable. The following may do what you’re after however:
```plaintext
SELECT user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE topic_id = /* put the topic id of your guidelines here */
AND post_number = /* this will probably be 1 since I suspect it's the only thing there */
AND msecs > 10000 /* they've looked at it for more than 10 seconds */
```
---
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [26 februari 2015 om 15:18 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/3 "2015-02-26T15:18:04Z")
Thanks very much @PJH!
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [26 februari 2015 om 15:19 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/4 "2015-02-26T15:19:20Z")
Test it first… I’m not sure how/when the msecs column gets updated.
I’d also recommend running it -at most- daily, with no revocation.
---
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [26 februari 2015 om 22:25 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/5 "2015-02-26T22:25:16Z")
Works, thanks! Though this probably isn’t the ideal solution, n the interest of giving back to the community, here’s our multipost version.
```
SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
JOIN (
SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
JOIN (
SELECT post_timings.user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE topic_id = 206
AND post_number = 1
AND msecs > 10000
) a ON a.user_id = post_timings.user_id
WHERE topic_id = 1066
AND post_number = 1
AND msecs > 10000
) b ON b.user_id = post_timings.user_id
WHERE topic_id = 1043
AND post_number = 1
AND msecs > 10000
```
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [27 februari 2015 om 09:00 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/6 "2015-02-27T09:00:00Z")
How about:
```sql
SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE
( topic_id = 206 AND post_number = 1 AND msecs > 10000) OR
( topic_id = 1066 AND post_number = 1 AND msecs > 10000) OR
( topic_id = 1043 AND post_number = 1 AND msecs > 10000)
GROUP BY user_id
HAVING count(*) = 3
```
Edit: Changed since it was pointed out to me that the original wouldn’t have worked.
---
### Author:  [@chapel](https://meta.discourse.org/u/chapel)
#### Post date: [28 februari 2015 om 19:41 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/7 "2015-02-28T19:41:07Z")
Couldn’t this be rewritten as:
```sql
SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND ( topic_id = 206 OR topic_id = 1066 OR topic_id = 1043 )
GROUP BY user_id
HAVING count(*) = 3
```
I’m no SQL expert, just seems like it should be possible given the duplication in the original query.
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [1 maart 2015 om 10:55 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/8 "2015-03-01T10:55:09Z")
> [@chapel](#):
>
> just seems like it should be possible given the duplication in the original query.
Presuming 10 seconds is sufficient for all the posts, and the first post in each topic is the one being targeted, yes…
---
### Author:  [@chapel](https://meta.discourse.org/u/chapel)
#### Post date: [1 maart 2015 om 11:00 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/9 "2015-03-01T11:00:12Z")
Yeah, not sure what a good read time is, but 10 seconds seems okay.
I would also assume the first post is the focus.
Also couldn’t this be simplified a bit more? (Again naive SQL assumptions)
```sql
SELECT count(*), user_id, CURRENT_TIMESTAMP AS granted_at
FROM post_timings
WHERE post_number = 1
AND msecs > 10000
AND topic_id IN ( 206, 1066, 1043 )
GROUP BY user_id
HAVING count(*) = 3
```
---
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [3 maart 2015 om 02:42 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/10 "2015-03-03T02:42:40Z")
All are pretty great, though this one strikes me as the most extensible.
But this is just code golf now, right?
---
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [3 maart 2015 om 04:02 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/11 "2015-03-03T04:02:11Z")
> [@john\_mardlin](#):
>
> But this is just code golf now, right?
I had the same thought myself…
---
### Author:  [@chapel](https://meta.discourse.org/u/chapel)
#### Post date: [3 maart 2015 om 06:09 UTC](https://meta.discourse.org/t/badge-granted-to-our-staff-members-that-have-read-the-guidelines-placed-in-our-hidden-staff-category/276721/12 "2015-03-03T06:09:52Z")
I think it boils down to maintainable queries. Duplication is a pain to work around.