Add an achievement for max win streak or win streak 10+/20+ or something

[vorpal-buildbot]

WITH MatchData AS ( SELECT d.person_id, m.date, dm.games, CASE WHEN dm.games = 2 THEN 1 ELSE 0 END AS is_win FROM deck_match dm JOIN match m ON dm.match_id = m.id JOIN deck d ON dm.deck_id = d.id ), StreakGroups AS ( SELECT person_id, date, is_win, SUM(CASE WHEN is_win = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY person_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp FROM MatchData ), WinStreaks AS ( SELECT person_id, date, is_win, ROW_NUMBER() OVER (PARTITION BY person_id, grp ORDER BY date) AS streak_pos, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date) AS overall_pos FROM StreakGroups WHERE is_win = 1 ), StreakLengths AS ( SELECT person_id, COUNT(*) AS streak_length, MAX(date) AS end_date FROM WinStreaks GROUP BY person_id, overall_pos - streak_pos ) SELECT p.mtgo_username, MAX(sl.streak_length) AS longest_win_streak FROM StreakLengths sl JOIN person p ON sl.person_id = p.id GROUP BY p.mtgo_username HAVING longest_win_streak >= 10 ORDER BY longest_win_streak DESC;

Reported on Discord by @bakert