Badge granted to our staff members that have read the guidelines placed in our hidden staff category

data & reporting
1

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?

1 Like
2

Unlikely because read_faq is ‘special’ and not reprogrammable or extendable. The following may do what you’re after however:

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 */
11 Likes
3

Thanks very much @PJH!

4

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.

1 Like
5

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
6

How about:

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.

3 Likes
7

Couldn’t this be rewritten as:

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.

3 Likes
8

Presuming 10 seconds is sufficient for all the posts, and the first post in each topic is the one being targeted, yes…

9

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)

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
5 Likes
10

All are pretty great, though this one strikes me as the most extensible.

But this is just code golf now, right?

4 Likes
11

I had the same thought myself…

1 Like
12

I think it boils down to maintainable queries. Duplication is a pain to work around.

2 Likes