Heads up! To view this whole video, sign in with your Courses Plus account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Learn what SQL Window Functions are and why they're useful.
Follow along with this workshop:
You can download the database used in this workshop from the Downloads tab and load it into the database of your choice. Or, you can use Mode Analytics, an online database and SQL reporting service. You can create a free account and use our free data-source for this workshop:
- Create a free account on Mode: https://modeanalytics.com/signup
- Create a new Mode Report
- For the "data source" select the Mode Public Warehouse
- Use the
treehouse.chess_data_matchestable
You can then execute the SQL that we show you here in Mode.
Definitions:
Window function An SQL function that aggregates over the results of the query.
PostgreSQL Download
SQL from Video
SELECT PLAYER_NAME,
PLAYER_RANKING,
PLAYER_TEAM,
COUNT(MATCH_ID) AS MATCHES_PLAYED,
SUM(CHECKMATE) AS MATCHES_WON,
SUM(CHECKMATE)/COUNT(MATCH_ID) AS WIN_RATE
FROM treehouse.chess_data_matches
GROUP BY PLAYER_NAME,
PLAYER_RANKING,
PLAYER_TEAM
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
[MUSIC]
0:00
[SOUND] Hi I'm Danielle.
0:04
A data scientist here at Treehouse.
0:06
Window functions are a powerful feature
available in some variations of SQL.
0:08
They let you analyze a row within
the context of an entire result set.
0:13
In other words, you can compare one
row to the other rows in a query.
0:17
For example, you can use them to calculate
each row as a percent of the total or
0:21
to add a moving average to a report.
0:26
Say, you have a report listing your
company's monthly sales by region,
0:28
one row of results per month per region.
0:31
If you want to track the performance
of region over time,
0:36
looking at the sales number
itself can be misleading.
0:38
The region sales may go up in one month,
but
0:41
if the total sales went up more, that
region could still be underperforming.
0:44
You can use window functions
to calculate each region sales
0:48
as a percentage of total monthly sales,
and
0:51
gauge performance, by tracking how the
region's share of sales changes over time.
0:53
You can also use window functions
to aggregate on multiple
0:58
levels within one query,
just by using a function call.
1:01
If you want to see a sum for
row as well as for a category,
1:05
that the row falls into,
window functions can do that.
1:08
In the sales report example,
you could compile your regional totals and
1:12
monthly totals in the same query.
1:15
This replaces the need for
multiple reports, self joins, or
1:17
further aggregation outside
of the original report.
1:21
So it streamlines queries and
keeps everything in one place.
1:24
SQL Server, Oracle, PostgreSQL and
similar database systems,
1:28
including Amazon Redshift and Snowflake,
all support window functions.
1:32
If you're working with any
of these variations of SQL,
1:38
window functions are a great way to
take your SQL skills to the next level.
1:40
In this workshop, we'll be exploring a
dataset of matches at a chess tournament.
1:45
Imagine that you're a reporter for
a local paper, and
1:49
you're writing an article on
the latest community chess tournament.
1:52
You want to understand more about
how the players did, and you know
1:55
that with a little analysis, you'll
find some interesting facts to report.
1:59
If you'd like to follow along,
2:03
you can download the data I'll be using
from the downloads tab below this video.
2:04
Feel free to load it into your
local database and follow along.
2:08
I'll be using PostgreSQL in these videos,
but
2:11
any of the other databases
systems listed above should work.
2:14
PostgreSQL is an open source database
system, you can download it for free.
2:17
Check out the teacher's notes for
more information.
2:22
Let's take a look at the data.
2:25
This is the dataset containing
the results of the chess tournament.
2:27
It contains two records for
each match, one line per player.
2:30
Each record has the player's ranking and
team name.
2:35
It also has a checkmate column, which
indicates if the player won that match.
2:38
It's 1 for a win and 0 for a loss.
2:43
We've put together a preliminary
report using this dataset.
2:46
It shows each player's name, rank, team,
2:49
number of matches played,
number of wins and win rate.
2:53
Here's the underlying SQL for this report.
2:57
These aggregates are obtained
using regular functions.
3:00
So, they give us the count or
sum within a report's grouping.
3:03
In this case, by the player's name,
ranking and team.
3:07
The information that we have
in the report is valuable but
3:13
it leaves a lot of questions unanswered.
3:15
It's missing context, which is key
to making our article interesting.
3:18
We want to know how each player relates
to the others in the tournament, so
3:22
that our article can provide
a comprehensive view of what happened.
3:26
[SOUND] The questions
that we'll explore are,
3:29
how does each player's ranking
compare to the overall field?
3:32
How many total matches were
played at the tournament?
3:35
Which team had the highest
winning percentage?
3:38
Who is the top ranked player on each team?
3:41
And finally, do higher-ranked players
win more often than lower-ranked ones?
3:44
Best of all, we won't need to create
additional reports to find the answers.
3:49
Window functions will help us
answer all of these questions
3:53
within our original report.
3:56
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up