qHJUKw9T

1DROP TABLE IF EXISTS `PersonsXtra`;
2CREATE TABLE `PersonsXtra` AS
3SELECT personId, personName, personCountryId, personContinentId, gender, comps, eAtt 'eventsAttempted', eSuc 'eventsSucceeded', eAve 'eventsAverage', podiums, gold, silver, bronze, eventPodiums, eventWins, wcPodium, wChampion, records, WR, CR, NR, minWorldRank,
4(CASE
5	WHEN eSuc = 18 AND eAve < 15 THEN 'Bronze'
6	WHEN eSuc = 18 AND eAve = 15 AND WR = 0 AND CR = 0 AND wcPodium = 0 THEN 'Silver'
7	WHEN eSuc = 18 AND eAve = 15 AND (WR > 0 OR CR > 0 OR wcPodium > 0) AND NOT (WR > 0 AND CR > 0 AND wcPodium > 0) THEN 'Gold'
8	WHEN eSuc = 18 AND eAve = 15 AND WR > 0 AND CR > 0 AND wcPodium > 0 THEN 'Platinum'
9    ELSE 'None'
10END) 'membership'
11FROM
12(SELECT a.id 'personId', a.name 'personName', a.countryId 'personCountryId', b.personContinentId, a.gender,
13c.comps, c.eventsAttempted 'eAtt', c.eventsSucceeded 'eSuc', c.eventsAverage 'eAve',
14IFNULL(d.podiums,0) 'podiums', IFNULL(d.gold,0) 'gold', IFNULL(d.silver,0) 'silver', IFNULL(d.bronze,0) 'bronze',
15IFNULL(d.eventPodiums,0) 'eventPodiums', IFNULL(d.eventWins,0) 'eventWins',
16IFNULL(d.wcPodium,0) 'wcPodium', IFNULL(d.wChampion,0) 'wChampion',
17IFNULL(e.records,0) 'records', IFNULL(e.WR,0) 'WR', IFNULL(e.CR,0) 'CR', IFNULL(e.NR,0) 'NR',
18g.minWorldRank
19FROM
20	(SELECT id, name, countryId, gender
21	FROM `Persons`
22	WHERE subid = 1) a
23JOIN
24	(SELECT id, MID(continentId,2,19) 'personContinentId'
25	FROM `Countries`) b
26ON a.countryId = b.id
27LEFT JOIN
28	(SELECT personId,
29	COUNT(DISTINCT competitionId) 'comps',
30	COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') THEN eventId END)) 'eventsAttempted',
31	COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') AND best > 0 THEN eventId END)) 'eventsSucceeded',
32	COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') AND average > 0 THEN eventId END)) 'eventsAverage'
33	FROM `Results`
34	GROUP BY personId) c
35ON a.id = c.personId
36LEFT JOIN
37	(SELECT personId,
38	COUNT(*) 'podiums',
39	COUNT(CASE WHEN pos = 1 THEN 1 END) 'gold',
40	COUNT(CASE WHEN pos = 2 THEN 1 END) 'silver',
41	COUNT(CASE WHEN pos = 3 THEN 1 END) 'bronze',
42	COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') THEN eventId END)) 'eventPodiums',
43	COUNT(DISTINCT (CASE WHEN eventId NOT IN ('333mbo','magic','mmagic') AND pos = 1 THEN eventId END)) 'eventWins',
44	COUNT(CASE WHEN competitionId IN (SELECT competition_id FROM `championships` WHERE championship_type = 'world') THEN 1 END) 'wcPodium',
45	COUNT(CASE WHEN competitionId IN (SELECT competition_id FROM `championships` WHERE championship_type = 'world') AND pos = 1 THEN 1 END) 'wChampion'
46	FROM `Results` WHERE roundTypeId IN ('c','f') AND pos <= 3 AND best > 0 GROUP BY personId) d
47ON a.id = d.personId
48LEFT JOIN
49	(SELECT personId,
50	COUNT(CASE WHEN regionalSingleRecord != '' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord != '' THEN 1 END) 'records',
51	COUNT(CASE WHEN regionalSingleRecord = 'WR' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord = 'WR' THEN 1 END) 'WR',
52	COUNT(CASE WHEN regionalSingleRecord NOT IN ('','NR','WR') THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord NOT IN ('','NR','WR') THEN 1 END) 'CR',
53	COUNT(CASE WHEN regionalSingleRecord = 'NR' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord = 'NR' THEN 1 END) 'NR'
54	FROM `Results`
55	WHERE regionalSingleRecord != '' OR regionalAverageRecord != ''
56	GROUP BY personId) e
57ON a.id = e.personId
58LEFT JOIN
59	(SELECT personId, MIN(worldRank) 'minWorldRank'
60	FROM (SELECT * FROM `RanksSingle` UNION ALL SELECT * FROM `RanksAverage`) f
61	GROUP BY personId) g
62ON a.id = g.personId) z