Window_Functions_in_SQL.pdf

Visit https://dsmlbootcamp.com

Transcript

1. Window Functions in SQL
2. • Partition rows into “windows” • Computation for each window • Return new value for each row
3. Aggregation vs. Windows
4. Syntax for Window Functions <function_type> OVER ( PARTITION BY clause ORDER BY clause ROWS or RANGE clause ) AS column_name
5. What We’ll Cover
6. Introduction • Database & system setup • Window function syntax • Window function execution
7. Window Function Basics • OVER clause • PARTITION BY clause • ORDER BY clause
8. Aggregation Functions • SUM, AVG • COUNT • MAX, MIN
9. Ranking Functions • ROW_NUMBER • RANK, DENSE_RANK, CUME_DIST • NTILE, PERCENT_RANK
10. Value Functions • LAG, LEAD • FIRST_VALUE, LAST_VALUE, NTH_VALUE
11. Advanced Windowing • ROWS BETWEEN Clause • RANGE BETWEEN Clause • WINDOW Alias
12. Database & System Setup
13. Classic Models Database
14. • Offices & employees worldwide • Customers are toy/gift stores • Dedicated sales representative • Pay for multiple orders at once
15. Entity Relationship Diagram (ERD)
16. Local Database Setup • Download the SQL script • Install and start MySQL Server • Execute on MySQL Workbench
17. Problem Solving Approach • Understand inputs & outputs • Articulate solution verbally • Construct query step-by-step
18. Window Function Basics
19. Syntax for Window Functions <function_type> OVER ( PARTITION BY clause ORDER BY clause ROWS or RANGE clause ) AS column_name
20. Window Function Execution
21. OVER Clause • Required in window functions • Preceded by function type • Followed by selection clauses
22. OVER Clause Q: Show the list of all the payments made by the customers of Classic Models, and assign a unique payment number to each payment.
23. OVER Clause SELECT *, ROW_NUMBER() OVER () AS paymentNumber FROM payments;
24. PARTITION BY Clause • Optional but commonly used • Used to group rows in windows • Followed by column name
25. PARTITION BY Clause Q: Express each payment made by a customer as a fraction of the total payment made by the customer. Show the checkNumber, customerNumber, paymentDate, amount & fractionOfTotal.
26. PARTITION BY Clause SELECT *, amount / ( SUM(amount) OVER (PARTITION BY customerNumber) ) AS fractionOfTotal FROM payments;
27. ORDER BY Clause • Determine order within window • Followed by column name • Limits frame to current row
28. ORDER BY Clause Q: Show the running total of payments received by Classic Models, starting from the oldest to the newest. Show the checkNumber, customerNumber, paymentDate, amount & runningTotal.
29. ORDER BY Clause SELECT *, SUM(amount) OVER (ORDER BY paymentDate) AS runningTotal FROM payments;
30. ORDER BY Clause
31. Aggregation Functions
32. SUM & AVG Q: Show the running total and running average of payments by each customer, starting from the oldest to the newest. Also output the customerNumber, checkNumber, and paymentDate.
33. SUM & AVG SELECT *, SUM(amount) OVER ( PARTITION BY customerNumber ORDER BY paymentDate) AS customerRunningTotal, AVG(amount) OVER ( PARTITION BY customerNumber ORDER BY paymentDate) AS customerRunningAverage FROM payments;
34. COUNT Q: Determine the team size for each employee i.e. how many people report to the same manager as the employee. Output employeeNumber, lastName, firstName, reportsTo, and teamSize.
35. COUNT SELECT employeeNumber, lastName, firstName, reportsTo, COUNT(*) OVER (PARTITION BY reportsTo) AS teamSize FROM employees;
36. MAX & MIN Q: For each order, show the order number, total order amount, and the difference between the order amount and the maximum/minimum order amount for the customer.
37. MAX & MIN SELECT orderNumber, customerNumber, orderValue, ROUND(MAX(orderValue) OVER (PARTITION BY customerNumber) - orderValue, 2) AS belowMax, ROUND(orderValue - MIN(orderValue) OVER (PARTITION BY customerNumber), 2) AS aboveMin FROM (SELECT o.orderNumber, o.customerNumber, SUM(od.quantityOrdered * od.priceEach) AS orderValue FROM orders o JOIN orderdetails od ON o.orderNumber = od.orderNumber GROUP BY o.orderNumber, o.customerNumber) AS ordersWithValues;
38. Ranking Functions
39. ROW_NUMBER Q: Provide serial numbers for the customers of each sales representative, sorted by dates of each customer’s earliest orders.
40. ROW_NUMBER SELECT customerNumber, customerName, salesRepEmployeeNumber, earliestOrder, ROW_NUMBER() OVER (PARTITION BY salesRepEmployeeNumber ORDER BY earliestOrder) AS serialNumber FROM (SELECT c.customerNumber, c.customerName, c.salesRepEmployeeNumber, MIN(o.orderDate) AS earliestOrder FROM customers c JOIN orders o ON c.customerNumber = o.customerNumber WHERE c.salesRepEmployeeNumber IS NOT NULL GROUP BY c.customerNumber, c.salesRepEmployeeNumber ) AS co;
41. RANK & DENSE_RANK Q: Rank the offices of Classic Models based on the number of employees (lowest first). Compare the outputs of ROW_NUMBER, RANK, DENSE_RANK and CUME_DIST.
42. RANK & DENSE_RANK SELECT *, ROW_NUMBER() OVER (ORDER BY employeeCount) AS rowNumber, RANK() OVER (ORDER BY employeeCount) AS rankNumber, DENSE_RANK() OVER (ORDER BY employeeCount) AS denseRankNumber, CUME_DIST() OVER (ORDER BY employeeCount) AS cumeDist FROM (SELECT o.officeCode, city, COUNT(*) AS employeeCount FROM offices o JOIN employees e ON o.officeCode = e.officeCode GROUP BY o.officeCode, city) AS oe;
43. NTILE & PERCENT_RANK • NTILE: put rows into buckets • PERCENT_RANK: fraction of values <= than current value • Typically used with ORDER BY
44. NTILE & PERCENT_RANK Q: Compute the quartile and percentile for each payment made by a customer. Output checkNumber, paymentDate, amount, customerNumber, quartile, and percentRank.
45. NTILE & PERCENT_RANK SELECT *, NTILE(4) OVER (PARTITION BY customerNumber ORDER BY amount) AS quartile, PERCENT_RANK() OVER (PARTITION BY customerNumber ORDER BY amount) AS percentRank FROM payments;
46. Value Functions
47. LAG Q: For each order, show the number of days elapsed since the last order placed by the same customer. Output orderNumber, orderDate, customerNumber, and daysSinceLastOrder.
48. LAG SELECT orderNumber, orderDate, customerNumber, DATEDIFF(orderDate, LAG(orderDate, 1) OVER (PARTITION BY customerNumber ORDER BY orderDate) ) AS daysSinceLastOrder FROM orders;
49. LEAD Q: List orders for each customer sorted by order amount (highest to lowest) and display the difference in amount between each order and the next highest order by the same customer.
50. LEAD SELECT *, orderValue - LEAD(orderValue, 1) OVER (PARTITION BY customerNumber ORDER BY orderValue DESC) AS higherBy FROM (SELECT o.orderNumber, o.orderDate, o.customerNumber, SUM(od.quantityOrdered * od.priceEach) AS orderValue FROM orders o JOIN orderdetails od ON o.orderNumber = od.orderNumber GROUP BY o.orderNumber, o.customerNumber) AS ordersWithValues;
51. First, Last & Nth Value Q: For each payment made by a customer, compute the difference between the payment amount and the first/second/last payment made by the customer.
52. First, Last & Nth Value
53. SELECT *, ROUND(amount - FIRST_VALUE(amount) OVER customersWindow, 2) AS diffFirst, ROUND(amount - LAST_VALUE(amount) OVER customersWindow, 2) AS diffLast, ROUND(amount - NTH_VALUE(amount, 2) OVER customersWindow, 2) AS diffSecond FROM payments WINDOW customersWindow AS (PARTITION BY customerNumber ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING); First, Last & Nth Value
54. Advanced Windowing
55. ROWS BETWEEN Clause
56. RANGE BETWEEN Clause
57. WINDOW Alias
58. Summary
59. Syntax for Window Functions <function_type> OVER ( PARTITION BY clause ORDER BY clause ROWS or RANGE clause ) AS column_name
60. Window Function Execution
61. Window Functions Basics • OVER clause • PARTITION BY clause • ORDER BY clause
62. Aggregation Functions • SUM, AVG • COUNT • MAX, MIN
63. Ranking Functions • ROW_NUMBER • RANK, DENSE_RANK, CUME_DIST • NTILE, PERCENT_RANK
64. Value Functions • LAG, LEAD • FIRST_VALUE, LAST_VALUE, NTH_VALUE
65. Advanced Windowing • ROWS BETWEEN Clause • RANGE BETWEEN Clause • WINDOW Alias
66. Resources • LearnSQL.com • Mode SQL Tutorial • StrataScratch
67. Window Functions in SQL
68. Practice Problems
69. Q: Find the percentage of the total spend a customer spent on each order on Amazon. Output the customer’s name, order details, and the percentage of their total spend for each order transaction rounded to the nearest whole number. Amazon Interview Question
70. Amazon Interview Question CUSTOMERS id name city address phone_number ORDERS id customer_id order_date order_quantity order_details order_cost
71. SELECT c.name, o.order_details, ROUND(o.order_cost * 100 / CAST(SUM(o.order_cost) OVER (PARTITION BY c.name) AS FLOAT) ) AS percentage_of_total FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY c.name Amazon Interview Question
72. Q: You’re given a dataset of Uber rides with the traveling distance and cost for each ride. For each date, find the difference between the distance per dollar for that date and the average distance per dollar for that month. Uber Interview Question
73. Uber Interview Question REQUESTS id date status distance cost
74. Q: Estimate the growth of Airbnb each year using the number of hosts registered as the growth metric. rate_of_growth = 100 * (hosts_registered_this_year - hosts_registered_last_year) / hosts_registered_last_year Airbnb Interview Question
75. Airbnb Interview Question PROPERTIES id price property_type room_type host_since …
76. Q: Find the top three unique salaries for each department in the company. Order the results alphabetically by department, and then by highest salary to lowest. Twitter Interview Question
77. Twitter Interview Question EMPLOYEES id name designation department salary …
78. Q: ABC Insurance Corp has developed a ML based predictive model to identify propensity of fraud claims. List the top 5 percentile claims identified by the model from each state in the US. Display the policy number, state, claim cost, and fraud score for each result. Netflix Interview Question
79. Netflix Interview Question CLAIMS policy_number state claim_cost fraud_score …
80. Interview Question Solutions
81. Window Functions in SQL