El Classics

el_classicos.sql

SELECT 
CASE 
	WHEN radiant_team < dire_team 
	THEN radiant_team
	ELSE dire_team 
END as lhs, 
CASE 
	WHEN radiant_team > dire_team 
	THEN radiant_team
	ELSE dire_team 
END as rhs,
count(*) as c 
FROM mtch
WHERE 
	radiant_team != "Unknown" and 
	dire_team != "Unknown" and
	date > '2014-07-22'
GROUP BY CASE 
	WHEN radiant_team < dire_team 
	THEN radiant_team
	ELSE dire_team 
END, CASE 
	WHEN radiant_team > dire_team 
	THEN radiant_team
	ELSE dire_team 
END
ORDER BY c desc
LIMIT 30;