Achievement for winning three tournaments in a row

[vorpal-buildbot]

Reported on Discord by @bakert

[bakert]

WITH ranked_wins AS (
  SELECT 
    d.person_id,
    d.id AS deck_id,
    d.archetype_id,
    c.id AS competition_id,
    c.name AS competition_name,
    c.start_date,
    cs.competition_type_id,
    p.mtgo_username,
    ROW_NUMBER() OVER (PARTITION BY d.person_id ORDER BY c.start_date) AS win_number
  FROM deck d
  JOIN competition c ON d.competition_id = c.id
  JOIN competition_series cs ON c.competition_series_id = cs.id
  JOIN person p ON d.person_id = p.id
  WHERE d.finish = 1
    AND cs.competition_type_id = 2
)
SELECT DISTINCT 
    a.person_id,
    a.mtgo_username,
    a.deck_id AS first_win_deck_id,
    a.archetype_id AS first_archetype_id,
    a.competition_name AS first_competition_name,
    b.deck_id AS second_win_deck_id,
    b.archetype_id AS second_archetype_id,
    b.competition_name AS second_competition_name,
    c.deck_id AS third_win_deck_id,
    c.archetype_id AS third_archetype_id,
    c.competition_name AS third_competition_name
FROM ranked_wins a
JOIN ranked_wins b ON a.person_id = b.person_id AND b.win_number = a.win_number + 1
JOIN ranked_wins c ON a.person_id = c.person_id AND c.win_number = a.win_number + 2
WHERE NOT EXISTS (
    SELECT 1
    FROM competition comp
    JOIN competition_series cs ON comp.competition_series_id = cs.id
    WHERE cs.competition_type_id = 2
      AND comp.start_date > a.start_date
      AND comp.start_date < c.start_date
      AND comp.id NOT IN (a.competition_id, b.competition_id, c.competition_id)
)
-- This checks for different decks probably lop this off
AND a.archetype_id != b.archetype_id
AND b.archetype_id != c.archetype_id
AND a.archetype_id != c.archetype_id

Has happend 13 times and is very cool.

Coupoutous is the only one to do it with three different decks – that's even cooler.

Could be three-in-a-week as well or instead. APAC or other unattendable tournaments mess it up.

Maybe three that you played in is the best rule?