Demo of SQL skills. (Untick Reader mode to view code)

CREATE TABLE restaurant_inspection AS SELECT * FROM NYC_Open_Data_Restaurant_Inspection_Results_sample5k.csv;

-- Extract and define most recent date. In reality this would be today/yesterday.
WITH most_recent AS (SELECT MAX("INSPECTION DATE") AS mostrecent,
					 EXTRACT(MONTH FROM mostrecent) AS mostrecentmonth,
					 EXTRACT(YEAR FROM mostrecent) AS mostrecentyear
					FROM restaurant_inspection),
				
-- 	Extract year and month from inspection date and reformat as YYYY, MM		
years_and_months AS (SELECT camis, 
					 "INSPECTION DATE", 
					 EXTRACT('YEAR' FROM "INSPECTION DATE") AS inspection_year,
					 EXTRACT('MONTH' FROM "INSPECTION DATE") AS inspection_month,
					 inspection_year || ', '|| inspection_month AS actual_month,
					 (SELECT mostrecent FROM most_recent) - "INSPECTION DATE" AS AGE
					 FROM restaurant_inspection)
						 
SELECT actual_month,
	   inspection_year,
	   inspection_month,
	   COUNT(*) AS Inspections
FROM years_and_months
/*Add a condition so that we only ever have data from the most recent 365 days and we only ever have the enitre months count or this month so far i.e. exclude data from this month last year */
WHERE AGE < 365
AND NOT (inspection_month = (SELECT mostrecentmonth FROM most_recent) AND inspection_year = (SELECT mostrecentyear FROM most_recent) -1) 
GROUP BY actual_month, inspection_year, inspection_month
ORDER BY inspection_year, inspection_month;

-- Calculate total revenue grouped by warehouse and product line
WITH product_line_total_by_area AS (SELECT warehouse, 
										   product_line, 
										   SUM(total) AS revenue
									FROM sales
									GROUP BY warehouse, product_line)

-- Calculate total revenue by area (warehouse), then use the product_line_total_by_area CTE to calculate the contribution of each product line to the overall revenue of each warehouse.
SELECT warehouse,
	   product_line, 
	   revenue, 
	   SUM(revenue) OVER(PARTITION BY warehouse) AS warehouse_total,
	   ROUND((revenue/(SUM(revenue) OVER(PARTITION BY warehouse)) *100.0), 1)                                      AS pct_contribution_to_warehouse_total
FROM product_line_total_by_area
ORDER BY warehouse_total DESC, pct_contribution_to_warehouse_total DESC;

-- Create grouping bins
WITH bins AS (SELECT generate_series(0,12,3) AS lower,
	                 generate_series(3,15,3) AS upper)

-- Exclude payment values of 0
, not_free AS (SELECT *
		  	   FROM dvdrentals.payment
			   WHERE amount > 0)
			   
-- Utilize the upper and lower bounds to group the payment values into the newly created bins
SELECT lower, 
	   upper, 
	   '£' || lower || ' - £' || upper AS Bin, 
	   COUNT(amount)
FROM bins
LEFT JOIN not_free
ON amount >= lower
AND amount < upper
GROUP BY lower, upper
ORDER BY lower;

WITH Goals AS ( -- CTE to stack home and away teams and goals on top of each other to allow for correct aggregation
SELECT country_id
	   ,C.name
       ,season
	   ,hometeam_id AS team_id
	   ,home_goal AS goals
FROM soccer.match AS M
LEFT JOIN soccer.country AS C -- Join to country table in order to include Country name in dataset
ON M.country_id = C.id
WHERE C.name = 'England' -- Filter to only retrn matches played in English League

UNION ALL

SELECT  country_id
       ,C.name
	   ,season
	   ,awayteam_id
	   ,away_goal
FROM soccer.match AS M
LEFT JOIN soccer.country AS C
ON M.country_id = C.id
WHERE C.name = 'England'
)

,

GoalsBySeason AS ( -- CTE to calculate each total goals in each season
SELECT T.team_long_name AS Team
	   ,G.season
	   ,SUM(G.Goals) AS TotalGoals
FROM Goals AS G
LEFT JOIN soccer.team AS T -- Join to retrieve string team names from team table
ON G.team_id = T.team_api_id
GROUP BY G.season, T.team_long_name
)

--QUALIFY clause unsupported, therefore RANK used inside a subquery, then filtered with WHERE to show Top Goal Scorers
SELECT season,
	   Team AS TopGoalScorers
	   ,TotalGoals
FROM (SELECT season 
	   ,Team
	   ,TotalGoals
	   ,RANK() OVER (PARTITION BY season ORDER BY TotalGoals DESC) AS rank
FROM GoalsBySeason) AS TotalGoalsRanked
WHERE rank = 1

--V1 of above query. Included to show there is often more than one way to get to get the same result --

-- filter to only return matches played by English teams.
WITH england_country_id AS (SELECT id
					   FROM soccer.country
					   WHERE name = 'England')
					   
,english_matches AS (SELECT *
				     FROM soccer.match
					 WHERE country_id IN (SELECT id
										  FROM england_country_id))

-- 2 joins to get the home team name and away team name from the soccer.team table
, english_match_details AS (SELECT season,
								   soccer.league.name AS league,
								   date, 
								   hometeam_id, 
								   hometeams.team_long_name AS hometeam,
								   awayteam_id, 
								   awayteams.team_long_name AS awayteam,
								   home_goal, 
								   away_goal
						   FROM english_matches
						   LEFT JOIN soccer.team AS hometeams
						   ON hometeam_id = hometeams.team_api_id
   						   LEFT JOIN soccer.team AS awayteams
						   ON awayteam_id = awayteams.team_api_id
						   LEFT JOIN soccer.league USING(country_id))
				
/* many subsequent CTEs to get each teams total goals per season, then identify the highest scoring team for each season */

-- calculate total home goals for each team, for each season
, homegoals AS (SELECT season, 
				       hometeam, 
				       SUM(home_goal) AS total_home_goals
				FROM english_match_details
				GROUP BY season, hometeam)
				
-- calculate total away goals for each team, for each season
, awaygoals AS (SELECT season, 
				       awayteam, 
				 	   SUM(away_goal) AS total_away_goals
				FROM english_match_details
				GROUP BY season, awayteam)

-- union the 2 datasets from the 2 previous CTEs
, unionhomeandawaygoals AS (SELECT season, 
								   hometeam AS team, 
							 	   total_home_goals AS total_goals
						    FROM homegoals
						    UNION ALL
						    SELECT season, 
							       awayteam, 
							       total_away_goals
						    FROM awaygoals)

-- group by season and team then sum home goals and away goals to get each teams total goals for each season
, totalseasongoals AS (SELECT season, 
					          team, 
					          SUM(total_goals) AS season_goals
					   FROM unionhomeandawaygoals
					   GROUP BY season, team
					   ORDER BY team, season)

-- calaculate highest number of total goals scored by a team for each season
,seasontop AS (SELECT season, 
		 	          team, 
		              season_goals, 
		              MAX(season_goals) OVER(PARTITION BY season) AS seasontopscorer
		       FROM totalseasongoals)

-- return team name and goals scored for the highest scoring team for each season
SELECT season, 
	   team, 
	   season_goals 
FROM seasontop
WHERE season_goals = seasontopscorer
ORDER BY season;

SELECT c.name,
	   ROUND(AVG(m.home_goal + m.away_goal),1) AS avg_goals_per_match
FROM soccer.match AS m
LEFT JOIN soccer.country AS c
ON m.country_id = c.id
GROUP BY c.name
HAVING AVG(m.home_goal + m.away_goal) > (SELECT AVG(m.home_goal + m.away_goal)
										 FROM soccer.match AS m)
ORDER BY avg_goals_per_match DESC;	

WITH chelsea_points AS (
SELECT date,
	   CASE WHEN hometeam_id = 8455 THEN CASE WHEN home_goal > away_goal THEN 3
											  WHEN away_goal > home_goal THEN 0
											  ELSE 1 END 
		    WHEN awayteam_id = 8455 THEN CASE WHEN home_goal > away_goal THEN 0
											  WHEN away_goal > home_goal THEN 3
											  ELSE 1 END 
	   END AS points
FROM soccer.match
WHERE season = '2014/2015'
AND (hometeam_id = 8455 OR awayteam_id = 8455)
ORDER BY date DESC)

SELECT date,
	   points,
	   SUM(points) OVER (ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED 		                          FOLLOWING)
FROM chelsea_points;