SQL
Realistic Scenarios: Analysis and Optimisation
Six end to end problems of the kind real work produces - a sales report, a retention table, a data quality audit, a duplicate cleanup and two optimisa...
#Data Analysis
Answering analytical questions with SQL.
10 notes tagged
SQL
Advanced Date Based Analysis
Period over period comparison, cohort retention, complete date ranges with no missing buckets, and rolling windows over time.
SQL
Gaps and Islands, and Comparing Adjacent Rows
Finding missing values in a sequence and grouping consecutive runs together - the pattern behind streak counting, session detection and attendance rep...
SQL
Top N Per Group
Three ways to get the best row in each group - window function, correlated subquery and lateral join - and how to pick between them.
SQL
Conditional Aggregation, Pivot and Unpivot
Turning rows into columns and back again with portable SQL. Conditional aggregation, the PIVOT operator where it exists, and unpivoting with UNION ALL...
SQL
Frames, Running Totals and Moving Averages
The frame clause decides which rows each calculation sees. Learn ROWS versus RANGE, running totals, moving averages, and when a window beats GROUP BY.
SQL
LAG, LEAD, FIRST_VALUE and LAST_VALUE
Reading other rows from the current row: the previous value, the next value, and the first or last in the window - with the LAST_VALUE frame trap.
SQL
ROW_NUMBER, RANK, DENSE_RANK and NTILE
The four ranking functions, how each handles ties, and the top N per group pattern that makes ROW_NUMBER the most used window function in SQL.
SQL
Multi Column Grouping and Grouping Rules
Grouping by several columns, grouping by expressions, and the totals features - ROLLUP, CUBE and GROUPING SETS - that add subtotal rows.
SQL
Aggregates, NULL and Aggregate Expressions
Aggregating an expression rather than a column: weighted totals, conditional counts and the difference between counting rows and counting facts.