SQL Quest › SQL Interview Questions › Window Functions
YoY Sales Volume Per Borough
Compute year-over-year DEED count change per borough. First aggregate to (borough, year, deed_count) — extract the year from document_date. Then use LAG to get prev year's count. Show borough_code, year, deed_count, prev_year_count, yoy_change. Order by borough, year.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
sales
| document_id | record_type | doc_type | recorded_borough | document_date | document_amt | recorded_datetime |
|---|---|---|---|---|---|---|
| 2025081800333001 | A | DEED | 1 | 2025-08-14 | 1080000000 | 2025-08-18 |
| 2024012300948001 | A | DEED | 1 | 2024-01-22 | 963000000 | 2024-01-24 |
| 2025081800439001 | A | DEED | 1 | 2025-08-14 | 810000000 | 2025-08-20 |
Expected output: Year-over-year deal flow trends
Hint
Concepts
SELECT Window Functions LAG Date Functions GROUP BY
Practise the topic: SQL practice questions · Window function practice · Date function practice · GROUP BY exercises · Advanced SQL interview questions
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test