SQL
Advanced Date Based Analysis
Period over period comparison, cohort retention, complete date ranges with no missing buckets, and rolling windows over time.
#Advanced
Advanced level material.
60 notes tagged
SQL
Hierarchical Data and Recursive Queries
Storing and querying trees: the adjacency list, walking it with a recursive CTE, and the materialised path and closure table alternatives.
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
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
OVER, PARTITION BY and Window ORDER BY
Window functions compute across related rows without collapsing them. Learn the OVER clause, PARTITION BY, window ORDER BY, and how they differ from G...
SQL
BCNF and Functional Dependencies
The formal notation behind normalisation, and the stricter normal form that catches what 3NF allows through when candidate keys overlap.
HTML
Web Storage and Browser APIs from a Page
localStorage, sessionStorage and geolocation. What a page can remember, what it must ask permission for, and what should never be stored in the browse...
HTML
The canvas Element
A blank bitmap you draw on with JavaScript. Powerful for graphics and games, and invisible to every reader who cannot see the screen.
SQL
Recursive CTEs
Query a hierarchy of unknown depth, generate a series of rows, or follow a chain of references - all with one self referencing CTE.