Data Analyst
Window functions questions
96 questions on Window functions for data analysts, easiest first.
Easy 26
- Window function vs GROUP BYeasy
- An empty OVER clauseeasy
- What PARTITION BY doeseasy
- RANK values for tied scoreseasy
- Filtering on ROW_NUMBER in WHEREeasy
- LAG on the first roweasy
- What LEAD returnseasy
- Each product's share of saleseasy
- Top 3 products in each categoryeasy
- Second highest salary with tieseasy
- A 3-day moving average frameeasy
- Salary vs department averageeasy
- Each customer's first product boughteasy
- Team size on every employee roweasy
- ORDER BY inside and outside OVEReasy
- Computing PERCENT_RANKeasy
- Computing CUME_DISTeasy
- Highest DENSE_RANK valueeasy
- Deleting duplicate rowseasy
- Total count beside a page of rowseasy
- Window functions in MySQLeasy
- Reusing a window aliaseasy
- Highest balance so fareasy
- Rows with a NULL regioneasy
- Looking two rows aheadeasy
- Sorting by a window valueeasy
Medium 40
- Latest order per customermedium
- Running total of salesmedium
- RANK vs DENSE_RANK after a tiemedium
- Month-over-month change with LAGmedium
- Each region's share of total salesmedium
- ROW_NUMBER in a WHERE clausemedium
- What QUALIFY is formedium
- Running total that resets per customermedium
- Computing a running totalmedium
- Value of a 3-row moving averagemedium
- 7-row average with missing daysmedium
- Splitting clicks into sessionsmedium
- Grouping consecutive datesmedium
- Share of total after GROUP BYmedium
- Previous order from another customermedium
- Best seller per city with tiesmedium
- The third purchase amountmedium
- Running count of distinct customersmedium
- Month-over-month growth valuemedium
- Tagging rows with a cohort monthmedium
- Counting ticket status changesmedium
- Finding missing invoice numbersmedium
- LAG in pandasmedium
- YTD for an April–March yearmedium
- Reusing one window definitionmedium
- Filtering before or after the windowmedium
- A centred 3-day averagemedium
- A date-based 7-day framemedium
- Day-over-day change with gapsmedium
- LAG on raw order rowsmedium
- A window inside a windowmedium
- Year-over-year with LAG 12medium
- Spotting customers who lapsedmedium
- Products making 80% of revenuemedium
- Window function after GROUP BYmedium
- Marking duplicates in an UPDATEmedium
- Speeding up a ranking querymedium
- RANK in pandasmedium
- Latest row with DISTINCT ONmedium
- DISTINCT and a window countmedium
Hard 30
- Running total with tied dateshard
- NTILE with 10 rowshard
- Latest order changes between runshard
- 7-day average over missing dayshard
- NULL score ranked firsthard
- Every share shows 0hard
- LAG default vs COALESCEhard
- Forward-filling missing priceshard
- Streaks broken by repeat loginshard
- Runs of the same machine statushard
- Merging overlapping bookingshard
- Largest fall from a peakhard
- PRECEDING with a descending orderhard
- Running totals in an incremental modelhard
- Spark window with no partitionhard
- Average of everyone else in the teamhard
- GROUPS frame with tied dateshard
- Percent of total with LIMIT 10hard
- End dates for history rowshard
- Equal scores in different quartileshard
- A slow filter on a window viewhard
- Order count of zero after a LEFT JOINhard
- Users with a 3-day streakhard
- One partition takes all the timehard
- Five windows, five sortshard
- Products within ₹500 of each pricehard
- What EXCLUDE TIES removeshard
- Median per department in PostgreSQLhard
- Last known value with IGNORE NULLShard
- Ties that are not quite tieshard