OH! Oh! I know! Let’s call that badge “Quarantine”!
5 Likes
“Self isolation”
3 Likes
So is that emoji to represent a “paradox” …if so, well played Sir Brave Jumping Amphibian
Oh that’s easy!…Quinquennium Cotidie
(latin for “five year every day”)
…then we could start posts with “Hail Quinquennium Cotidie @Troll Maximus!” 
4 Likes
A user has a timezone and we can read it easily, but the underlying issue for this was a design choice in 2013, where a user’s ‘visit’ is recorded just as a plain date: discourse/20121123063630_create_user_visits.rb at 888e68a1637ca784a7bf51a6bbb524dcf7413b13 · discourse/discourse · GitHub
What this means is that we don’t have the underlying data resolution to make a per user timezone friendly database query, to see if they visited every ‘local’ day, as the resolution of the underlying value is just a ‘date’ and not ‘date and time’ (like most DB values in Discourse). I imagine back in 2013 they thought it more efficient or never thought of how it could be used with timezones, which is unfortunate but understandable.
So if we can’t change the underlying data now, all we can do is alter the query of what is considered ‘consecutive days’, perhaps to allow some tolerance of 1 day gaps. Here’s the query that gets run for a badge like ‘Devotee’ (with the days set to 365) each day: discourse/badge_queries.rb at 58d6ee36ee460e2f7f5da0121dcbaa7929b7d7d5 · discourse/discourse · GitHub
WITH consecutive_visits AS (
SELECT user_id
, visited_at
, visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
FROM user_visits
), visits AS (
SELECT user_id
, MIN(visited_at) "start"
, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank"
FROM consecutive_visits
GROUP BY user_id, s
HAVING COUNT(*) >= #{days}
)
SELECT user_id
, "start" + interval '#{days} days' "granted_at"
FROM visits
WHERE "rank" = 1My plan is to create a new badge called something like ‘Approximate Devotee’ 
that allows some skips, so the visited_at doesn’t need to be a continuous run, but can allow for something like 3 or 4 missing days (just single days, to account for UTC accidents). To do that I have to create a local Discourse forum container set, so I can use the PostgresSQL tools and try some things on my own data. I’d need to make the time for this as it’s been a while.
As there are probably DB people on Mudspike, if you send me an alternative for ‘DENSE_RANK()’ that takes skips then you’ll get a ‘DB Genius’ badge and it’ll save me some head-scratching. 
Also, if it helps, this is the query we use to see what the current ‘user visited_at attendance run’ was this - it returns rows of sequences, so could perhaps be useful to develop a new query without the DENSE_RANK/PARTITION efficiency:
-- [params]
-- user_list :users
WITH StartingPoints AS (
SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
FROM user_visits AS A
WHERE NOT EXISTS (
SELECT 1
FROM user_visits AS B
WHERE B.visited_at = A.visited_at - INTERVAL '1 day' AND
B.user_id = A.user_id
) AND user_id IN (:users)
),
EndingPoints AS (
SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
FROM user_visits AS A
WHERE NOT EXISTS (
SELECT 1
FROM user_visits AS B
WHERE B.visited_at = A.visited_at + INTERVAL '1 day' AND
B.user_id = A.user_id
) AND user_id IN (:users)
)
SELECT u.username, S.visited_at AS start_range, E.visited_at AS end_range, (E.visited_at - S.visited_at + 1) AS Days
FROM StartingPoints AS S
JOIN EndingPoints AS E ON E.rownum = S.rownum
JOIN users u ON u.id=S.user_id AND
u.id IN (:users)
ORDER BY u.id ASC, S.visited_at DESC
5 Likes
You took the words right out of my mouth.
2 Likes
“Give a man a fish and he’ll feed for a day, give a man a DB query problem and he’ll hate you forever”
6 Likes
Not quite how I would do it, but that will probably work too.
Don’t you think, @BeachAV8R?
Sometimes I feel woefully indadequate…
2 Likes
I can read that SQL pretty well … but I might not be able to come up with it on my own
1 Like
Well sure…we could do all that…but a true “Devotee” should be the kind of person who checks Mudspike as much as humanly possible…Wake up in the morning-check Mudspike; Get a cup of coffee-check Mudspike; Got to work-check Mudspike; for the pilots in the group…get transferred to another ATC controller-check Mudspike, …
…Eat lunch, get a snack, take a nap, walk the dog, feed the cat, take out the trash-check Mudspike; kiss the wife good night-check Mudspike…
…and for us pushing 60 crowd, get up in the middle of the night for a call-of-nature-check Mudspike …all 5 times! 
2 Likes
Thinking about SQL queries and annual prostate check-ups do feel pretty similar to me, so there’s that. 
3 Likes
I had my prostrate removed so, WooHoo! No more annual checks.
(…that’s not why I had it removed though…just a side benefit)
2 Likes
This is me…
2 Likes
I can’t wait to do the prep!
2 Likes
Also, if it helps, this is the query we use to see what the current ‘user visited_at attendance run’ was this - it returns rows of sequences, so could perhaps be useful to develop a new query without the DENSE_RANK/PARTITION efficiency:
I’m fighting with the OCD part of me that sees a database query puzzle and wants^H^H^H^H^Hhas to find the solution. It has kicked in and I’m resisting the urge to ask for the schema… 
Picture a dog and a lamp post…
1 Like
I don’t know. I might have tidied up the end around partamount with a little slidgy four technique to reduce the server overhead fumulgation. I mean, Frog’s way works pretty good, but there are server throughput anomalous query functions that could be brought into the ecosystem through distributed client side call sequencing that might bring efficiencies of scale in ranges that he might not have fully deprecated. TL;DR - 5/7 execution.
6 Likes
What about a litmus configuration?
1 Like
You know on an HOTAS, where for an AGM-65-D on a A10-C you would TMS Forward if SOI MAV on the MFCD using a SPI once DSMS MSL is set up with the TGP TDC Slave hand-off, well, it’s like that, but probably less complicated
5 Likes
I do all that. But on step 17 I eat a SAM. Back to helicopters for me. I can just hover around the base sling loading latrine supplies from the west fenceline to the east fenceline.
5 Likes
And that’s when I went ”you go guns and press the trigger”.
2 Likes
…OK, did that…now what? 
5 Likes