Window Functions: Aggregate & Sliding

WITH Country_Medals AS ( SELECT Year, Country, COUNT(*) AS Medals, medal FROM olympics WHERE Country IN ('CHN', 'KOR', 'JPN') AND Medal = 'Gold' AND Year >= 2000 GROUP BY Year, Country) SELECT Country, Year, Medals, MAX(Medals) OVER (PARTITION BY Country ORDER BY Year ASC) AS Max_Medals, medal FROM Country_Medals ORDER BY Country ASC, Year ASC; SELECT country, year, COUNT(medal) medals, MAX(COUNT(medal)) OVER(PARTITION BY country ORDER BY year) max_medals, medal FROM olympics WHERE YEAR >= 2000 AND country IN ('CHN', 'JPN','KOR') AND medal = 'Gold' GROUP BY year, country

Maximum country medals by year Getting the maximum of a country's earned medals so far helps you determine whether a country has broken its medals record by comparing the current year's earned medals and the maximum so far. Solve by CTE or SUBQUERY

WITH France_Medals AS ( SELECT Year, COUNT(*) AS Medals, medal, country FROM olympics WHERE Country = 'FRA' AND Medal = 'Gold' AND Year >= 2000 GROUP BY Year) SELECT Year, Medals, MIN(Medals) OVER (ORDER BY Year ASC) AS Min_Medals, medal, country FROM France_Medals ORDER BY Year ASC; SELECT year, COUNT(medal) medals, MIN(COUNT(medal)) OVER(ORDER BY year) min_medals, medal, country FROM olympics WHERE country = 'FRA' AND medal = 'Gold' AND year >= 2000 GROUP BY year

Minimum country medals by year So far, you've seen MAX and SUM, aggregate functions normally used with GROUP BY, being used as window functions. You can also use the other aggregate functions, like MIN, as window functions.

WITH Russian_Medals AS ( SELECT Year, COUNT(*) AS Medals, country, medal FROM olympics WHERE Country = 'RUS' AND Medal = 'Gold' AND Year >= 1980 GROUP BY Year) SELECT Year, Medals, AVG(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Medals_MA, country, medal FROM Russian_Medals ORDER BY Year ASC; SELECT year, COUNT(medal) medals, AVG(COUNT(medal)) OVER(ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) medals_average, country, medal FROM olympics WHERE country = 'RUS' AND medal = 'Gold' GROUP BY year

Moving average of Russian medals Calculate the 3-year moving average of medals earned. Meaning 2 years before and now.

momentum and trends eliminating seasonality

Moving averages indicate momen___________ and tre_____________ Useful in elim_____________ seas_________________

WITH Chinese_Medals AS ( SELECT Athlete, COUNT(*) AS Medals, country, year, medal FROM olympics WHERE Country = 'CHN' AND Medal = 'Gold' AND Year >= 2000 GROUP BY Athlete) SELECT Athlete, Medals, MAX(Medals) OVER (ORDER BY Athlete ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Max_Medals, country, year, medal FROM Chinese_Medals ORDER BY Athlete ASC; SELECT athlete, COUNT(medal) medals, MAX(COUNT(medal)) OVER(ORDER BY athlete ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) max_medals, country, year, medal FROM olympics WHERE medal = 'Gold' AND year >= 2000 AND country = 'CHN' GROUP BY athlete

Moving maximum of Chinese athletes' medals Return the athletes, medals earned, and the maximum medals earned, comparing only the last two and current athletes, ordering by athletes' names in alphabetical order.

WITH Scandinavian_Medals AS ( SELECT Year, COUNT(*) AS Medals, medal, country FROM olympics WHERE Country IN ('DEN', 'NOR', 'FIN', 'SWE', 'ISL') AND Medal = 'Gold' GROUP BY Year) SELECT Year, Medals, MAX(Medals) OVER (ORDER BY Year ASC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS Max_Medals, medal, country FROM Scandinavian_Medals ORDER BY Year ASC; SELECT year, COUNT(medal) medals, MAX(COUNT(medal)) OVER(ORDER BY year ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) max_medal, medal, country FROM olympics WHERE medal = 'Gold' AND country IN ('DEN','SWE','FIN','NOR') GROUP BY year

Moving maximum of Scandinavian athletes' medals Return the year, medals earned, and the maximum medals earned, comparing only the current year and the next year.

WITH Country_Medals AS ( SELECT Year, Country, COUNT(*) AS Medals, medal FROM olympics GROUP BY Year, Country) SELECT Year, Country, Medals, SUM(Medals) OVER (PARTITION BY Country ORDER BY Year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Medals_MA, medal FROM Country_Medals ORDER BY Country ASC, Year ASC; SELECT year, country, COUNT(medal) medals, SUM(COUNT(medal)) OVER(PARTITION BY country ORDER BY year ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) medals_average, medal FROM olympics GROUP BY year, country

Moving total of countries' medals What if your data is split into multiple groups spread over one or more columns in the table? Even with a defined frame, if you can't somehow separate the groups' data, one group's values will affect the average of another group's values. Calculate the 3-year moving sum of medals earned per country.

SELECT date, season, home_goal, away_goal, SUM(home_goal) OVER(ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) running_total, AVG(home_goal) OVER(ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) running_avg FROM matches WHERE season = '2011/2012' AND awayteam_id = 9908

Now let's see how FC Utrecht performs when they're the away team. You'll notice that the total for the season is at the bottom of the data set you queried. Depending on your results, this could be pretty long, and scrolling down is not very helpful. In this exercise, you will slightly modify the query from the previous exercise by sorting the data set in reverse order and calculating a backward running total keep the score as home but change the hometeam to awayteam.

duplicates single

Range Between treats dupl___________ in Over's Order By subclause as a sin________ entity. Rows between does not.

WITH Athlete_Medals AS ( SELECT Athlete, COUNT(*) AS Medals, Medal, Country, Year FROM olympics WHERE Country = 'USA' AND Medal = 'Gold' AND Year >= 2000 GROUP BY Athlete) SELECT Athlete, Medals, SUM(Medals) OVER (ORDER BY Athlete ASC) AS Max_Medals, Medal, Country, Year FROM Athlete_Medals ORDER BY Athlete ASC; SELECT athlete, count(medal) medals, SUM(COUNT(medal)) OVER(ORDER BY athlete) running_medals, medal, country, year FROM olympics WHERE medal = 'Gold' AND year >= 2000 AND country = 'USA' GROUP BY athlete

Running totals of athlete medals The running total (or cumulative sum) of a column helps you determine what each row's contribution is to the total sum. For the window function, make sure to use 2 aggregate functions and order by athlete. Can be completed with CTE or Subquery.

SELECT date, season, home_goal, away_goal, SUM(home_goal) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total, AVG(home_goal) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg FROM matches WHERE hometeam_id = 9908 AND season = '2011/2012';

Slide to the left! In this exercise, you will expand on the examples discussed in the video, calculating the running total of goals scored by the FC Utrecht when they were the home team during the 2011/2012 season. Use the unbounded preceding to current row for both windows. Filter by hometeam_id to 9908

averages totals

Using frames with aggregate window functions allow you to calculate many common metrics, including moving ave___________ and to__________. These metrics track the change in performance over time.