Analytics and capstone
Gaps & islands, deduplication and percentiles
Advanced SQL is a precisely defined pattern.
Understand the concept
Gaps and islands find runs of consecutive values. For dates, deduplicate days before grouping by the stable difference between day and ROW_NUMBER. A run of dates differs from a run of event rows.
Deduplication requires a business key and a deterministic survivor rule. A latest row cannot be defined without ordering. Real duplicate deletion needs transaction and audit planning; this example only selects. PERCENTILE_CONT interpolates, while PERCENTILE_DISC chooses an observed value. Account for NULLs and empty groups. Means, medians and upper percentiles answer different questions; avoid generalising from tiny data.
Build controlled pattern and distribution queries.
Deleting duplicates before defining their business key.
One topic. Three levels of depth.
SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price) OVER () AS MedianPrice FROM lab.Product;The median product price is 1800.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Find the observed median product price.
Show the solution and explanation
SELECT DISTINCT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Price) OVER () AS ObservedMedian FROM lab.Product;The middle observed value among seven prices is 1800.