[SQL] Intermediate SQL(1)

---

SQL Intermediate(1)

• CASE WHEN

---

import sqlite3
%load_ext sql
%sql sqlite:////Users/jisu/Dropbox_Carl/Dropbox/JISU/Data/Jupyter_Projects/blog/data_engineer/sql/3_intermediate_sql/database.sqlite

'Connected: @/Users/jisu/Dropbox_Carl/Dropbox/JISU/Data/Jupyter_Projects/blog/data_engineer/sql/3_intermediate_sql/database.sqlite'

---

CASE WHEN

• CASE WHEN을 WHERE 안에 넣어 filter 할 수 있음
  • 이때, IS NOT NULL을 통해 그 이외의 조건들 제외 가능
• CASE WHEN을 Aggregate function 안에 넣어 aggregate된 데이터를 산출 할 수 있음

%%sql
SELECT
	 season
	,date
	,home_team_goal
	,away_team_goal
FROM Match
WHERE 
	CASE WHEN home_team_api_id = 8633 AND home_team_goal > away_team_goal 
		 	THEN "Barcelona wins in Home"
		 WHEN away_team_api_id = 8633 AND away_team_goal > home_team_goal 
		 	THEN "Barcelona wins in Away"
		 END IS NOT NULL
LIMIT 3;

 * sqlite:////Users/jisu/Dropbox_Carl/Dropbox/JISU/Data/Jupyter_Projects/blog/data_engineer/sql/3_intermediate_sql/database.sqlite
Done.

season	date	home_team_goal	away_team_goal
2008/2009	2008-11-08 00:00:00	4	3
2008/2009	2008-11-22 00:00:00	1	0
2008/2009	2008-12-20 00:00:00	1	0

• number of soccer matches played in a given European country in 2008/2009 and 2009/2010

%%sql
SELECT
	 c.name AS country
	,COUNT(CASE WHEN m.season = "2008/2009" THEN m.id END) AS "matches 2008/2009"
	,COUNT(CASE WHEN m.season = "2009/2010" THEN m.id END) AS "matches 2009/2010"
FROM Country AS c
LEFT JOIN Match AS m
	ON c.id = m.country_id
GROUP BY c.name
LIMIT 3;

 * sqlite:////Users/jisu/Dropbox_Carl/Dropbox/JISU/Data/Jupyter_Projects/blog/data_engineer/sql/3_intermediate_sql/database.sqlite
Done.

country	matches 2008/2009	matches 2009/2010
Belgium	306	210
England	380	380
France	380	380

• total number of matches won by the home team in each country during the 2008/2009 and 2009/2010 seasons.

%%sql
SELECT 
	 c.name AS country
	,SUM(CASE WHEN m.season = "2008/2009" AND m.home_team_goal > m.away_team_goal
		 	THEN 1 ELSE 0 END) AS "Home Team Wins in 2008/2009"
	,SUM(CASE WHEN m.season = "2009/2010" AND m.home_team_goal > m.away_team_goal
		 	THEN 1 ELSE 0 END) AS "Home Team Wins in 2009/2010"
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY c.name
LIMIT 3;

 * sqlite:////Users/jisu/Dropbox_Carl/Dropbox/JISU/Data/Jupyter_Projects/blog/data_engineer/sql/3_intermediate_sql/database.sqlite
Done.

country	Home Team Wins in 2008/2009	Home Team Wins in 2009/2010
Belgium	149	97
England	173	193
France	165	179

• examine percent of ties in each country in 2013/2014 and 2014/2015 seasons.

%%sql
SELECT 
	c.name AS country,
	ROUND(
		AVG(CASE WHEN m.season='2013/2014' AND m.home_team_goal = m.away_team_goal 
				THEN 1
			 WHEN m.season='2013/2014' AND m.home_team_goal != m.away_team_goal 
			 	THEN 0
			 END)
		 ,2) AS pct_ties_2013_2014,
	ROUND(
		AVG(CASE WHEN m.season='2014/2015' AND m.home_team_goal = m.away_team_goal 
				THEN 1
			 WHEN m.season='2014/2015' AND m.home_team_goal != m.away_team_goal 
		 		THEN 0
			 END),
		 2) AS pct_ties_2014_2015
FROM Country AS c
LEFT JOIN Match AS m
ON c.id = m.country_id
GROUP BY country
LIMIT 3;

 * sqlite:////Users/jisu/Dropbox_Carl/Dropbox/JISU/Data/Jupyter_Projects/blog/data_engineer/sql/3_intermediate_sql/database.sqlite
Done.

country	pct_ties_2013_2014	pct_ties_2014_2015
Belgium	0.17	0.25
England	0.21	0.24
France	0.28	0.23