SQL pattern · P15 · 18 problems

Window functions and ranking

Graded problems on window functions and ranking, from easy to hard.

18 problems · about 3 h 10 min in all
Start with problem 01Rank each customer's orders by amount · 5 min
Learn it first · P15The rank that skips a number after a tieRANK() leaves a gap after ties, so the third distinct value lands at rank 4, not 3.Read the lesson →

Easy

3 problems · about 18 min
01Rank each customer's orders by amount5 min →02Top product per category by price5 min →03Show each product's current price on every price change8 min →

Medium

11 problems · about 1 h 50 min
04ROW_NUMBER vs RANK vs DENSE_RANK on tied amounts8 min →05Each product's share of its category revenue10 min →06Rank products within each order (repeat lines merged)10 min →07Top 3 best-selling products per category10 min →08Top-spending customer per country10 min →09Second-biggest order per customer8 min →10Per-customer median attached to every order row8 min →11Where each salary sits within its department12 min →12Spend quartiles that keep tied customers together12 min →13Compare each order with the customer's other orders12 min →14Top 3 sellers, keeping everyone tied for a place8 min →

Hard

4 problems · about 1 h 10 min
15Top 3 line items per order by list-price value12 min →16Median per customer WITHOUT PERCENTILE_CONT (portable)15 min →17Flag trades that break above the previous five20 min →18A top-3 leaderboard per region, with a business tie rule20 min →
Up next · P16

The three rows mistaken for three days

17 practice problems. A ROWS frame counts positions, not calendar time, so 2 PRECEDING can span a month.

Practise P16Read P16