Analytical SQL: window functions and reporting
With window functions, recursive CTEs, grouping sets and pivots you produce analysis reports in SQL such as rankings, moving averages, cohorts and funnels.
Level 301 · 19 lessons · 15 practice labs
Lessons
Window functions
- Defining windows with OVER
- Ranking: ROW_NUMBER, RANK, DENSE_RANK
- Previous and next rows: LAG and LEAD
- Running totals
- Frames and moving averages
- FIRST_VALUE, NTILE and percentiles
- Short task: top sellers per region
Advanced queries
- Recursive CTEs: trees and hierarchies
- Gaps and islands
- GROUPING SETS, ROLLUP and CUBE
- Turning rows into columns: pivot
- Top N per group with LATERAL
- Short task: totals for a category tree
Analysis patterns
- Cohorts and retention
- Funnel analysis
- Deriving sessions from events
- Time series and bucketing
- Removing duplicates and picking the latest row
- Capstone: a monthly dashboard for a subscription business
Practice labs
- Pick each store's top seller: Use ROW_NUMBER to return the cashier with the most sales this month in each Lale Market branch.
- Choose the right ranking for ties: Show the difference between RANK, DENSE_RANK and ROW_NUMBER on a contest table and apply the right one to the prize list.
- Split customers into quartiles: Use NTILE and percentile functions to bucket customers by spend and report each bucket's limits.
- Produce a running till balance: Calculate the running balance on every row of Tarçın Restoran's daily till movements.
- Compute a seven-day moving average: Calculate a seven-day moving average of site visits with the correct window frame.
- Catch price changes with LAG: Use LAG to extract only the rows where a product's price actually changed, with the percentage change.
- Walk the org chart: Use a recursive CTE to list every employee under one manager with their level.
- Roll up totals over a category tree: Use a recursive query to roll sales in nested categories up to every parent category.
- Catch a hierarchy with a cycle: Write a recursive query that detects a manager chain looping back on itself without running forever.
- Add subtotals with ROLLUP: Add subtotals and a grand total to sales by region and city with ROLLUP.
- Write a report with months as columns: Pivot Kestane Mobilya's sales so products are rows and months are columns.
- Take the top three per region with LATERAL: Use a LATERAL subquery to return each region's top three customers by revenue and compare its speed with a window solution.
- Pick each customer's latest order: Use a window function to pick each customer's latest order among repeated records.
- Build a retention table by signup month: Calculate monthly retention rates by signup month for Yelken Sigorta's app users.
- Derive sessions and a funnel from events: Split click events into sessions on 30-minute gaps and calculate step rates of the cart-to-payment funnel.
About this course
With window functions, recursive CTEs, grouping sets and pivots you produce analysis reports in SQL such as rankings, moving averages, cohorts and funnels.
What you will learn
- Window functions
- Advanced queries
- Analysis patterns
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.