Intermediate and Advanced Analytics

This is the track that turns SQL users into analysts. You'll learn window functions from the ground up: how a window keeps every row while GROUP BY collapses them, how PARTITION BY and ORDER BY shape the window, and how frames decide exactly which rows count. With them you'll rank with and without ties, pick the top N in every group, compare rows with their neighbours, build running totals and moving averages that stay correct when dates repeat or go missing, and describe distributions with medians and percentiles. The second half applies these tools to the analyses businesses ask for every week: month-over-month and year-over-year growth, complete time series built on date spines, signup cohorts and honest retention and churn figures. You'll work on Wadi Market's orders, Mizan Cloud's subscriptions and product events, Nakheel Hotels' nightly stays and Masar Holding's salaries.

Lessons

1. 1Window functions: calculations that keep every rowAdd a value computed over many rows (a total, an average, a count) next to each individual row, without collapsing the rows the way GROUP BY does.22 min · 4 exercisesFree
2. 2PARTITION BY: a separate window for each groupSplit the window into groups with PARTITION BY so each row is compared with its own category, order or customer, and see what ORDER BY inside OVER changes.24 min · 4 exercises Members
3. 3ROW_NUMBER: numbering rows within groupsNumber rows 1, 2, 3 … inside each partition to find first and latest records, sequence events and remove duplicates, with an ORDER BY that never leaves ties to chance.24 min · 4 exercises Members
4. 4RANK and DENSE_RANK: rankings with tiesRank rows when values can tie: compare ROW_NUMBER, RANK and DENSE_RANK on real salaries and choose the one that answers the question.24 min · 4 exercises Members
5. 5Top N per groupReturn the best (or worst) few rows of every group, such as the top 3 products in each category, with a ranking window in a CTE and a filter outside it.24 min · 4 exercises Members
6. 6LAG and LEAD: comparing a row with its neighboursRead a value from the previous or next row of the same group to measure changes, time between events and gaps.26 min · 4 exercises Members
7. 7Running totals and the default frame trapAccumulate values row by row with SUM … OVER (ORDER BY …), reset them per group, and avoid the RANGE default that silently lumps tied rows together.26 min · 4 exercises Members
8. 8Window frames: choosing exactly which rows countControl the frame of a window with ROWS, RANGE and GROUPS, bounded by PRECEDING and FOLLOWING, and avoid the classic LAST_VALUE surprise.28 min · 4 exercises Members
9. 9Moving averages: smoothing noisy seriesSmooth daily and monthly series with trailing and centred moving averages, flag incomplete windows, and compute moving ratios such as ADR correctly.28 min · 4 exercises Members
10. 10Percentiles and distributionsDescribe a distribution properly with medians and percentiles (percentile_cont, percentile_disc), and place each row within it using NTILE, PERCENT_RANK and CUME_DIST.28 min · 4 exercises Members
11. 11Period-over-period: month-over-month and year-over-yearCompare each period with the previous one and with the same period last year using date_trunc, LAG and safe percentage arithmetic.26 min · 4 exercises Members
12. 12Time series without gaps: date spinesBuild complete daily, weekly or monthly series with generate_series, join the data onto them so empty periods show zero, and combine spines with categories.28 min · 4 exercises Members
13. 13Cohort analysisGroup customers by when they started, measure what each cohort does in the months that follow, and lay the result out as a cohort table.30 min · 4 exercises Members
14. 14Customer retention and churnTurn cohorts into retention curves and churn rates: count only customers who could have been observed, define "retained" precisely, and measure repeat purchase and monthly logo churn.32 min · 4 exercises Members