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.
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