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

[bakert]

    SELECT
        d.person_id,
        m.id AS match_id,
        m.date,
        COALESCE(m.round, 0) AS round,
        dm.games,
        d.id AS deck_id,
        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,
        match_id,
        date,
        round,
        is_win,
        deck_id,
        SUM(CASE WHEN is_win = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY person_id ORDER BY date, round ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
    FROM
        MatchData
),
WinStreaks AS (
    SELECT
        person_id,
        match_id,
        date,
        round,
        is_win,
        deck_id,
        grp,
        ROW_NUMBER() OVER (PARTITION BY person_id, grp ORDER BY date, round) AS streak_pos
    FROM
        StreakGroups
    WHERE
        is_win = 1
),
StreakLengths AS (
    SELECT
        person_id,
        grp,
        COUNT(*) AS streak_length,
        MIN(date) AS start_date,
        MAX(date) AS end_date
    FROM
        WinStreaks
    GROUP BY
        person_id,
        grp
)
SELECT
    p.mtgo_username,
    sl.streak_length AS longest_win_streak,
    GROUP_CONCAT(DISTINCT ws.deck_id ORDER BY ws.date, ws.round) AS deck_ids
FROM
    StreakLengths sl
    JOIN WinStreaks ws ON sl.person_id = ws.person_id AND sl.grp = ws.grp
    JOIN person p ON sl.person_id = p.id
WHERE
    sl.streak_length >= 1
GROUP BY
    p.mtgo_username, sl.streak_length
ORDER BY
    longest_win_streak DESC;```