[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