· 7 years ago · Jan 26, 2019, 07:52 PM
1CREATE TABLE IF NOT EXISTS _new_achievements (
2 person_id INT NOT NULL,
3 season_id INT NOT NULL,
4 `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, `ancient_grudges` INT NOT NULL, `ancient_grudges_detail` LONGTEXT DEFAULT NULL, `recent_grudges` INT NOT NULL, `deckbuilder` INT NOT NULL, `pioneer` INT NOT NULL, `pioneer_detail` LONGTEXT DEFAULT NULL, `variety_player` INT NOT NULL, `specialist` INT NOT NULL, `generalist` INT NOT NULL, `completionist` INT NOT NULL,
5 PRIMARY KEY (season_id, person_id),
6 FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
7 FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
8 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
9 WITH
10 flawless_decks AS
11 (
12 SELECT
13 d.id AS id
14 FROM
15 deck AS d
16 INNER JOIN
17 deck_match AS dm
18 ON
19 dm.deck_id = d.id
20 INNER JOIN
21 deck_match AS odm
22 ON
23 dm.match_id = odm.match_id AND odm.deck_id <> d.id
24 WHERE
25 d.competition_id IN (
26 SELECT
27 id
28 FROM
29 competition
30 WHERE
31 competition_series_id IN
32 (
33 SELECT
34 id
35 FROM
36 competition_series
37 WHERE
38 competition_type_id
39 IN (
40 SELECT
41 id
42 FROM
43 competition_type
44 WHERE
45 name = 'League'
46 )
47 )
48 )
49 GROUP BY
50 d.id
51 HAVING
52 SUM(dm.games) = 10 AND SUM(odm.games) = 0
53 )
54 , knockouts AS
55 (
56 SELECT
57 dm1.deck_id AS winner_deck_id,
58 dm2.deck_id AS loser_deck_id,
59 p1.id AS winner_id,
60 p2.id AS loser_id,
61 season.id AS season_id,
62 `match`.date
63 FROM
64 deck AS d
65 LEFT JOIN
66 person AS p1
67 ON
68 d.person_id = p1.id
69 LEFT JOIN
70 deck_match AS dm1
71 ON
72 d.id = dm1.deck_id
73 LEFT JOIN
74 `match`
75 ON
76 dm1.match_id = `match`.id
77 LEFT JOIN
78 deck_match AS dm2
79 ON
80 `match`.id = dm2.match_id AND dm2.deck_id != dm1.deck_id
81 LEFT JOIN
82 deck AS d2
83 ON
84 dm2.deck_id = d2.id
85 LEFT JOIN
86 person AS p2
87 ON
88 d2.person_id = p2.id
89
90 LEFT JOIN
91 (
92 SELECT
93 `start`.id,
94 `start`.code,
95 `start`.start_date AS start_date,
96 `end`.start_date AS end_date
97 FROM
98 season AS `start`
99 LEFT JOIN
100 season AS `end` ON `end`.id = `start`.id + 1
101 ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
102
103 WHERE
104 dm1.games > dm2.games AND elimination > 0
105 ),
106 ancient_grudge_deck_ids AS
107 (
108 SELECT
109 k2.winner_deck_id AS id
110 FROM
111 knockouts AS k1
112 JOIN
113 knockouts AS k2
114 ON
115 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
116 ),
117 repeats AS
118 (
119 SELECT
120 d1.id AS original, d2.id AS copy, d1.person_id != d2.person_id AS newplayer
121 FROM
122 deck AS d1
123 JOIN
124 deck AS d2
125 ON d1.decklist_hash = d2.decklist_hash AND d1.created_date < d2.created_date
126 )
127 ,
128 pioneer_decks AS
129 (
130 SELECT
131 d.id
132 FROM
133 deck AS d
134 LEFT JOIN
135 deck AS d2 ON d.archetype_id = d2.archetype_id AND d.created_date > d2.created_date
136 LEFT JOIN
137 archetype as a ON d.archetype_id = a.id
138 WHERE
139 d2.created_date IS NULL and d.archetype_id IS NOT NULL
140 )
141 SELECT
142 p.id AS person_id,
143 season.id AS season_id,
144 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_decks) THEN 1 ELSE 0 END) AS `flawless_runs`, GROUP_CONCAT(CASE WHEN d.id IN (SELECT id FROM flawless_decks) THEN d.id ELSE NULL END) AS `flawless_runs_detail`,
145 SUM(CASE WHEN d.id IN
146 (
147 SELECT
148 -- 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.
149 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 deck_id
150 FROM
151 deck AS d
152 INNER JOIN
153 deck_match AS dm
154 ON
155 dm.deck_id = d.id
156 INNER JOIN
157 deck_match AS odm
158 ON
159 dm.match_id = odm.match_id AND odm.deck_id <> d.id
160 WHERE
161 d.competition_id IN (
162 SELECT
163 id
164 FROM
165 competition
166 WHERE
167 competition_series_id IN
168 (
169 SELECT
170 id
171 FROM
172 competition_series
173 WHERE
174 competition_type_id
175 IN (
176 SELECT
177 id
178 FROM
179 competition_type
180 WHERE
181 name = 'League'
182 )
183 )
184 )
185 GROUP BY
186 d.id
187 HAVING
188 SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >=4
189 AND
190 SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 1
191 AND
192 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
193 )
194 THEN 1 ELSE 0 END)
195 AS `perfect_run_crushes`, COUNT(DISTINCT CASE WHEN d.id IN (SELECT id FROM ancient_grudge_deck_ids) THEN d.id ELSE NULL END) AS `ancient_grudges`, GROUP_CONCAT(DISTINCT CASE WHEN d.id IN (SELECT * FROM ancient_grudge_deck_ids) THEN d.id ELSE NULL END) AS `ancient_grudges_detail`, COUNT(DISTINCT CASE WHEN d.id in
196 (
197 SELECT
198 distinct(dm1.deck_id) AS deck_id
199 FROM
200 deck_match AS dm1
201 INNER JOIN
202 deck_match AS odm1
203 ON
204 odm1.match_id = dm1.match_id AND odm1.deck_id != dm1.deck_id
205 INNER JOIN
206 `match` AS m1
207 ON
208 m1.id = dm1.match_id
209 INNER JOIN
210 deck_match AS dm2
211 ON
212 dm1.deck_id = dm2.deck_id AND dm2.match_id != dm1.match_id
213 INNER JOIN
214 deck_match AS odm2
215 ON
216 odm2.match_id = dm2.match_id AND odm2.deck_id = odm1.deck_id
217 INNER JOIN
218 `match` AS m2
219 ON
220 m2.id = dm2.match_id
221 WHERE
222 dm1.games < odm1.games AND m1.elimination = 0 AND dm2.games > odm2.games AND m2.elimination > 0
223 ORDER BY
224 deck_id
225 ) THEN d.id ELSE NULL END) AS `recent_grudges`, COUNT(DISTINCT CASE WHEN d.id IN (SELECT original FROM repeats WHERE newplayer = TRUE) AND d.id NOT IN (SELECT copy FROM repeats) THEN d.id ELSE NULL END) AS `deckbuilder`, 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 dc.wins + dc.losses >= 5 AND ct.name = 'League' THEN d.archetype_id ELSE NULL END) >= 3 THEN True ELSE False END AS `variety_player`,
226 CASE WHEN EXISTS
227 (
228 SELECT
229 p.id
230 FROM
231 (
232 SELECT p.id AS inner_pid, season.id AS inner_seasonid, COUNT(d.id) AS archcount
233 FROM
234 person AS p
235 LEFT JOIN
236 deck AS d
237 ON
238 d.person_id = p.id
239
240 LEFT JOIN
241 (
242 SELECT
243 `start`.id,
244 `start`.code,
245 `start`.start_date AS start_date,
246 `end`.start_date AS end_date
247 FROM
248 season AS `start`
249 LEFT JOIN
250 season AS `end` ON `end`.id = `start`.id + 1
251 ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
252
253
254 LEFT JOIN
255 competition AS c ON d.competition_id = c.id
256 LEFT JOIN
257 competition_series AS cs ON cs.id = c.competition_series_id
258 LEFT JOIN
259 competition_type AS ct ON ct.id = cs.competition_type_id
260
261 WHERE
262 d.finish <= c.top_n AND ct.name = 'Gatherling'
263 GROUP BY
264 p.id,
265 season.id,
266 d.archetype_id
267 HAVING archcount >= 3
268 ) AS spec_archs
269 WHERE
270 p.id = inner_pid AND season.id = inner_seasonid
271 )
272 THEN TRUE ELSE FALSE END
273 AS `specialist`, 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(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`
274 FROM
275 person AS p
276 LEFT JOIN
277 deck AS d ON d.person_id = p.id
278 LEFT JOIN
279 deck_cache AS dc ON dc.deck_id = d.id
280
281 LEFT JOIN
282 (
283 SELECT
284 `start`.id,
285 `start`.code,
286 `start`.start_date AS start_date,
287 `end`.start_date AS end_date
288 FROM
289 season AS `start`
290 LEFT JOIN
291 season AS `end` ON `end`.id = `start`.id + 1
292 ) AS season ON season.start_date <= d.created_date AND (season.end_date IS NULL OR season.end_date > d.created_date)
293
294
295 LEFT JOIN
296 competition AS c ON d.competition_id = c.id
297 LEFT JOIN
298 competition_series AS cs ON cs.id = c.competition_series_id
299 LEFT JOIN
300 competition_type AS ct ON ct.id = cs.competition_type_id
301
302 GROUP BY
303 p.id,
304 season.id
305 HAVING
306 season.id IS NOT NULL