CREATE TABLE IF NOT EXISTS _new_achievements (
person_id INT NOT NULL,
season_id INT NOT NULL,
`player` INT NOT NULL, `player_detail` LONGTEXT DEFAULT NULL, `tournament_entries` INT NOT NULL, `tournament_entries_detail` LONGTEXT DEFAULT NULL, `tournament_wins` INT NOT NULL, `tournament_wins_detail` LONGTEXT DEFAULT NULL, `league_entries` INT NOT NULL, `league_entries_detail` LONGTEXT DEFAULT NULL, `perfect_runs` INT NOT NULL, `perfect_runs_detail` LONGTEXT DEFAULT NULL, `flawless_runs` INT NOT NULL, `flawless_runs_detail` LONGTEXT DEFAULT NULL, `perfect_run_crushes` INT NOT NULL, `perfect_run_crushes_detail` LONGTEXT DEFAULT NULL, `ancient_grudges` INT NOT NULL, `ancient_grudges_detail` LONGTEXT DEFAULT NULL, `burning_vengeances` INT NOT NULL, `burning_vengeances_detail` LONGTEXT DEFAULT NULL, `deckbuilder` INT NOT NULL, `deckbuilder_detail` LONGTEXT DEFAULT NULL, `pioneer` INT NOT NULL, `pioneer_detail` LONGTEXT DEFAULT NULL, `variety_player` INT NOT NULL, `variety_player_detail` LONGTEXT DEFAULT NULL, `specialist` INT NOT NULL, `specialist_detail` LONGTEXT DEFAULT NULL, `generalist` INT NOT NULL, `generalist_detail` LONGTEXT DEFAULT NULL, `completionist` INT NOT NULL, `pd500_top8s` INT NOT NULL, `pd500_top8s_detail` LONGTEXT DEFAULT NULL, `pd500_wins` INT NOT NULL, `pd500_wins_detail` LONGTEXT DEFAULT NULL,
PRIMARY KEY (season_id, person_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
WITH
flawless_decks1 AS
(
SELECT
d.id AS id
FROM
deck AS d
INNER JOIN
deck_match AS dm
ON
dm.deck_id = d.id
INNER JOIN
deck_match AS odm
ON
dm.match_id = odm.match_id AND odm.deck_id <> d.id
WHERE
d.competition_id IN (
SELECT
id
FROM
competition
WHERE
competition_series_id IN
(
SELECT
id
FROM
competition_series
WHERE
competition_type_id
IN (
SELECT
id
FROM
competition_type
WHERE
name = 'League'
)
)
)
GROUP BY
d.id
HAVING
SUM(dm.games) = 10 AND SUM(odm.games) = 0
),
flawless_decks2 AS
(
SELECT
d.id AS id
FROM
deck AS d
INNER JOIN
deck_match AS dm
ON
dm.deck_id = d.id
INNER JOIN
deck_match AS odm
ON
dm.match_id = odm.match_id AND odm.deck_id <> d.id
WHERE
d.competition_id IN (
SELECT
id
FROM
competition
WHERE
competition_series_id IN
(
SELECT
id
FROM
competition_series
WHERE
competition_type_id
IN (
SELECT
id
FROM
competition_type
WHERE
name = 'League'
)
)
)
GROUP BY
d.id
HAVING
SUM(dm.games) = 10 AND SUM(odm.games) = 0
)
,
crushes AS
(
SELECT
-- MAX here is just to fool MySQL to give us the id of the deck that crushed the perfect run from an aggregate function. There is only one value to MAX.
MAX(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN odm.deck_id ELSE NULL END) AS crusher_id,
d.id AS crushee_id
FROM
deck AS d
INNER JOIN
deck_match AS dm
ON
dm.deck_id = d.id
INNER JOIN
deck_match AS odm
ON
dm.match_id = odm.match_id AND odm.deck_id <> d.id
WHERE
d.competition_id IN (
SELECT
id
FROM
competition
WHERE
competition_series_id IN
(
SELECT
id
FROM
competition_series
WHERE
competition_type_id
IN (
SELECT
id
FROM
competition_type
WHERE
name = 'League'
)
)
)
GROUP BY
d.id
HAVING
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >=4
AND
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 1
AND
SUM(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN 1 ELSE 0 END) = 1
),
crush_records AS (SELECT crusher_id, COUNT(crushee_id) AS crush_count, GROUP_CONCAT(crushee_id) AS crushee_ids FROM crushes GROUP BY crusher_id)
, knockouts AS
(
SELECT
dm1.deck_id AS winner_deck_id,
dm2.deck_id AS loser_deck_id,
p1.id AS winner_id,
p2.id AS loser_id,
season.season_id,
`match`.date
FROM
deck AS d
LEFT JOIN
person AS p1
ON
d.person_id = p1.id
LEFT JOIN
deck_match AS dm1
ON
d.id = dm1.deck_id
LEFT JOIN
`match`
ON
dm1.match_id = `match`.id
LEFT JOIN
deck_match AS dm2
ON
`match`.id = dm2.match_id AND dm2.deck_id != dm1.deck_id
LEFT JOIN
deck AS d2
ON
dm2.deck_id = d2.id
LEFT JOIN
person AS p2
ON
d2.person_id = p2.id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
WHERE
dm1.games > dm2.games AND elimination > 0
),
ancient_grudge_deck_ids AS
(
SELECT
k2.winner_deck_id AS id,
k1.winner_deck_id AS grudge_id,
CONCAT(k1.winner_deck_id, ",", k2.winner_deck_id) AS both_ids
FROM
knockouts AS k1
JOIN
knockouts AS k2
ON
k1.season_id = k2.season_id AND k1.winner_id = k2.loser_id AND k1.loser_id = k2.winner_id AND k2.date > k1.date
), burning_vengeance_decks AS
(
SELECT
distinct(dm1.deck_id) AS id,
CONCAT(dm1.deck_id, ",", odm1.deck_id) AS both_ids
FROM
deck_match AS dm1
INNER JOIN
deck_match AS odm1
ON
odm1.match_id = dm1.match_id AND odm1.deck_id != dm1.deck_id
INNER JOIN
`match` AS m1
ON
m1.id = dm1.match_id
INNER JOIN
deck_match AS dm2
ON
dm1.deck_id = dm2.deck_id AND dm2.match_id != dm1.match_id
INNER JOIN
deck_match AS odm2
ON
odm2.match_id = dm2.match_id AND odm2.deck_id = odm1.deck_id
INNER JOIN
`match` AS m2
ON
m2.id = dm2.match_id
WHERE
dm1.games < odm1.games AND m1.elimination = 0 AND dm2.games > odm2.games AND m2.elimination > 0
ORDER BY
id
),
repeats AS
(
SELECT
d1.id AS original, d2.id AS copy, d1.person_id != d2.person_id AS newplayer, d1.person_id as original_person_id
FROM
deck AS d1
JOIN
deck AS d2
ON d1.decklist_hash = d2.decklist_hash AND d1.created_date < d2.created_date
),
imitators AS (SELECT original, GROUP_CONCAT(copy) AS imitator_ids FROM repeats WHERE newplayer GROUP BY original)
,
pioneer_decks AS
(
SELECT
d.id
FROM
deck AS d
LEFT JOIN
deck AS d2 ON d.archetype_id = d2.archetype_id AND d.created_date > d2.created_date
LEFT JOIN
archetype as a ON d.archetype_id = a.id
WHERE
d2.created_date IS NULL and d.archetype_id IS NOT NULL
),
first_arch_league_runs AS
(
SELECT
MIN(d.id) as deck_id,
d.person_id AS person_id,
season.season_id,
d.archetype_id AS archetype_id
FROM
deck AS d
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
WHERE
ct.name = 'League' AND dc.wins + dc.losses >= 5
GROUP BY
person_id,
season_id,
archetype_id
),
top_ns AS
(
SELECT
d.id as deck_id,
p.id AS person_id,
season.season_id,
d.archetype_id
FROM
person AS p
LEFT JOIN
deck AS d
ON
d.person_id = p.id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
WHERE
d.finish <= c.top_n AND ct.name = 'Gatherling'
),
arch_top_n_count1 AS
(
SELECT person_id, season_id, archetype_id, COUNT(DISTINCT deck_id) AS n FROM top_ns GROUP BY person_id, season_id, archetype_id
),
arch_top_n_count2 AS
(
SELECT person_id, season_id, archetype_id, COUNT(DISTINCT deck_id) AS n FROM top_ns GROUP BY person_id, season_id, archetype_id
)
, first_arch_top_ns AS (SELECT MIN(deck_id) AS deck_id, person_id, season_id FROM top_ns GROUP BY person_id, season_id, archetype_id)
SELECT
p.id AS person_id,
season.season_id,
COUNT(CASE WHEN ct.name IN ('League', 'Gatherling') THEN 1 ELSE NULL END) > 0 AS `player`, GROUP_CONCAT(DISTINCT CASE WHEN ct.name IN ('League', 'Gatherling') THEN d.id ELSE NULL END) AS `player_detail`, COUNT(DISTINCT CASE WHEN ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS `tournament_entries`, GROUP_CONCAT(DISTINCT CASE WHEN ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS `tournament_entries_detail`, COUNT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS `tournament_wins`, GROUP_CONCAT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS `tournament_wins_detail`, COUNT(DISTINCT CASE WHEN ct.name = 'League' THEN d.id ELSE NULL END) AS `league_entries`, GROUP_CONCAT(DISTINCT CASE WHEN ct.name = 'League' THEN d.id ELSE NULL END) AS `league_entries_detail`, SUM(CASE WHEN ct.name = 'League' AND dc.wins >= 5 AND dc.losses = 0 THEN 1 ELSE 0 END) AS `perfect_runs`, GROUP_CONCAT(CASE WHEN ct.name = 'League' AND dc.wins >= 5 AND dc.losses = 0 THEN d.id ELSE NULL END) AS `perfect_runs_detail`, SUM(CASE WHEN d.id IN (SELECT id FROM flawless_decks1) THEN 1 ELSE 0 END) AS `flawless_runs`, GROUP_CONCAT(CASE WHEN d.id IN (SELECT id FROM flawless_decks2) THEN d.id ELSE NULL END) AS `flawless_runs_detail`, SUM(CASE WHEN crush_records.crush_count IS NULL THEN 0 ELSE crush_records.crush_count END) AS `perfect_run_crushes`, GROUP_CONCAT(crush_records.crushee_ids) AS `perfect_run_crushes_detail`, COUNT(DISTINCT agdi.grudge_id) AS `ancient_grudges`, GROUP_CONCAT(DISTINCT CASE WHEN agdi.id IS NOT NULL THEN agdi.both_ids ELSE NULL END) AS `ancient_grudges_detail`, COUNT(DISTINCT CASE WHEN d.id IN (SELECT id FROM burning_vengeance_decks) THEN d.id ELSE NULL END) AS `burning_vengeances`, GROUP_CONCAT(bvd.both_ids) AS `burning_vengeances_detail`, COUNT(DISTINCT CASE WHEN d.id IN (SELECT original FROM repeats WHERE newplayer) AND d.id NOT IN (SELECT copy FROM repeats) THEN d.id ELSE NULL END) AS `deckbuilder`, GROUP_CONCAT(CASE WHEN d.id IN (SELECT original FROM repeats WHERE newplayer) AND d.id NOT IN (SELECT copy FROM repeats) THEN CONCAT(d.id, ',', imitators.imitator_ids) ELSE NULL END) AS `deckbuilder_detail`, SUM(CASE WHEN d.id IN (SELECT * FROM pioneer_decks) THEN 1 ELSE 0 END) AS `pioneer`, GROUP_CONCAT(CASE WHEN d.id IN (SELECT * FROM pioneer_decks) THEN d.id ELSE NULL END) AS `pioneer_detail`, CASE WHEN COUNT(DISTINCT CASE WHEN falr.deck_id IS NOT NULL THEN d.archetype_id ELSE NULL END) >= 3 THEN True ELSE False END AS `variety_player`,
CASE WHEN
COUNT(DISTINCT CASE WHEN falr.deck_id IS NOT NULL THEN d.archetype_id ELSE NULL END) >= 3
THEN
GROUP_CONCAT(falr.deck_id)
ELSE
NULL
END
AS `variety_player_detail`, CASE WHEN EXISTS (SELECT * FROM arch_top_n_count1 AS atnc WHERE p.id = atnc.person_id AND season.season_id = atnc.season_id AND n >= 3) THEN TRUE ELSE FALSE END AS `specialist`, GROUP_CONCAT(CASE WHEN d.finish <= c.top_n AND ct.name = 'Gatherling' AND d.archetype_id IN (SELECT archetype_id FROM arch_top_n_count2 AS atnc WHERE p.id = atnc.person_id AND season.season_id = atnc.season_id AND n >= 3) THEN d.id ELSE NULL END) AS `specialist_detail`, CASE WHEN COUNT(DISTINCT CASE WHEN d.finish <= c.top_n AND ct.name = 'Gatherling' THEN d.archetype_id ELSE NULL END) >= 3 THEN True ELSE False END AS `generalist`,
CASE WHEN
COUNT(DISTINCT CASE WHEN d.finish <= c.top_n AND ct.name = 'Gatherling' THEN d.archetype_id ELSE NULL END) >= 3
THEN
GROUP_CONCAT(DISTINCT CASE WHEN d.id IN (SELECT deck_id FROM first_arch_top_ns) THEN d.id ELSE NULL END)
ELSE
NULL
END AS `generalist_detail`, CASE WHEN COUNT(CASE WHEN ct.name = 'League' THEN 1 ELSE NULL END) > 0 AND COUNT(CASE WHEN d.retired = 1 THEN 1 ELSE NULL END) = 0 THEN True ELSE False END AS `completionist`, COUNT(DISTINCT CASE WHEN d.finish <= 8 AND ct.name = 'Gatherling' AND c.name LIKE '%%Penny Dreadful 500%%' THEN d.id ELSE NULL END) AS `pd500_top8s`, GROUP_CONCAT(DISTINCT CASE WHEN d.finish <= 8 AND ct.name = 'Gatherling' AND c.name LIKE '%%Penny Dreadful 500%%' THEN d.id ELSE NULL END) AS `pd500_top8s_detail`, COUNT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' AND c.name LIKE '%%Penny Dreadful 500%%' THEN d.id ELSE NULL END) AS `pd500_wins`, GROUP_CONCAT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' AND c.name LIKE '%%Penny Dreadful 500%%' THEN d.id ELSE NULL END) AS `pd500_wins_detail`
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
LEFT JOIN deck_cache AS season ON d.id = season.deck_id
LEFT JOIN
competition AS c ON d.competition_id = c.id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
LEFT JOIN crush_records ON d.id = crush_records.crusher_id LEFT JOIN ancient_grudge_deck_ids AS agdi ON d.id = agdi.id LEFT JOIN burning_vengeance_decks AS bvd ON d.id = bvd.id LEFT JOIN imitators ON imitators.original = d.id LEFT JOIN first_arch_league_runs AS falr ON d.id = falr.deck_id
GROUP BY
p.id,
season.season_id
HAVING
season.season_id IS NOT NULL
```
[](slow_query, 82.6, mysql)Reported on decksite by mysql-perf
Location Hash: ee74675c54fc47b7e2cd21d3cb8cba4ce73475ec