Today I completed Day 61 of #100daysofcode and #python learning. Like previous today also continue to learned from Datacamp about SQL properties (CASE Statements in aggregate, COUNT and CASE WHEN with multiple conditions). Tried to solve assignment from Coursera.
SQL code
In the code we used country and match table to determine the total number of matches won by the home team in each country during the 2012/2013, 2013/2014, and 2014/2015 seasons.
SELECT
c.name AS country,
-- Sum the total records in each season where the home team won
SUM(CASE WHEN m.season = '2012/2013' AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2012_2013,
SUM(CASE WHEN m.season = '2013/2014' AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2013_2014,
SUM(CASE WHEN m.season = '2014/2015' AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY country;
Day 61 Of #100DaysOfCode and #Python
— Durga Pokharel (@mathdurga) February 27, 2021
* https://t.co/RdUBj1KUUxAlgorithmic Toolbox
* SQL (COUNT and CASE WHEN with multiple conditions)#womenintech #100DaysOfCode #CodeNewbie #DEVCommunity #beginner pic.twitter.com/z6Ul8aIeaK
Top comments (0)