Last active
September 4, 2022 10:50
-
-
Save QuantVI/09389439cd2cb98e525c1a2fe72f107b to your computer and use it in GitHub Desktop.
postgre sql wind function queries
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| *, | |
| row_number () over () AS Row_N | |
| FROM Summer_Medals | |
| ORDER BY Row_N ASC; | |
| -------------------- | |
| SELECT | |
| Year, | |
| row_number() over() AS Row_N | |
| FROM ( | |
| SELECT distinct year | |
| FROM Summer_Medals | |
| ORDER BY Year ASC | |
| ) AS Years | |
| ORDER BY Year ASC; | |
| -------------------- | |
| SELECT | |
| Year, Event, Country, | |
| ROW_NUMBER() OVER | |
| (ORDER BY Year DESC, Event ASC) AS Row_N | |
| FROM Summer_Mdeals | |
| WHERE | |
| Medal = 'Gold' | |
| ORDER BY Country ASC, Row_N ASC; | |
| /* The row number is lowest for the most recent year and earliest event alphabeticaly. | |
| The entire result is then sorted alphabetically by country and within countries by increasing row number. */ | |
| -------------------- | |
| /* Lag is function that takes a column name and a number, n, and returns the column's value at the row n rows BEFORE the current row. | |
| Before means lower n row-count/row-index. LAG(column, 1) over (...) means the prvious row's value. */ | |
| WITH Discus_Gold AS ( | |
| SELECT | |
| Year, Country AS Champion | |
| FROM Summer_Medals | |
| WHERE | |
| Year IN (1996, 2000, 2004, 2008, 2012) | |
| AND Gender = 'Men' AND Medal = 'Gold' | |
| AND Event = 'Discus Throw') | |
| SELECT | |
| Year, Champion, | |
| LAG(champion, 1) OVER | |
| (ORDER BY Year ASC) AS Last_Champion | |
| FROM Discus_Gold | |
| ORDER BY Year ASC; | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
| -------------------- | |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Review from DataCamp's SQL Fundamentals Course 5/7 - PostgreSQL Summary Stats and Window Functions