Wir erhalten ziemlich viele Fragen zur Aficionado-Auszeichnung (100 aufeinanderfolgende Tage). Meistens liegt das Problem daran, dass ein Benutzer einen oder mehrere Tage verpasst hat, aufgrund von Unterschieden in den Zeitzonen zwischen seiner lokalen Zeit und der Serverzeit.
Ich war immer etwas verwirrt über die Abfrage, die zur Vergabe der Auszeichnung verwendet wird. Ich denke, ich verstehe sie jetzt. Hier ist eine Data-Explorer-Abfrage, die eine ähnliche Logik verwendet. Sie gibt die aufeinanderfolgenden Besuchszeiträume für einen Benutzer zurück. Sie gibt das Startdatum, die Anzahl der aufeinanderfolgenden Tage und das Enddatum für jeden Zeitraum aufeinanderfolgender Besuche zurück. Standardmäßig werden alle Zeiträume mit 10 oder mehr Besuchen zurückgegeben. Um herauszufinden, welche Tage ein Benutzer verpasst hat, setzen Sie den Parameter min_days auf 1.
Wenn der in der ersten Ergebnisspalte zurückgegebene Wert period_end das aktuelle Datum ist, an dem Sie die Abfrage ausführen, ist der Benutzer immer noch im Rennen um die Auszeichnung.
--[params]
-- string :username
-- int :min_days = 10
WITH consecutive_visits AS (
SELECT user_id,
visited_at,
-- Der Wert von s ist für jede Gruppe aufeinanderfolgender Tage gleich
visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
FROM user_visits
WHERE user_id = (SELECT id FROM users WHERE username = :username)
)
SELECT
MIN(visited_at) period_start,
COUNT(*) AS consecutive_days,
MAX(visited_at) period_end
FROM consecutive_visits
GROUP BY user_id, s
HAVING COUNT(*) >= :min_days
ORDER BY period_start DESC