SQL Quest › SQL Interview Questions › Subqueries & CTEs
Combined Sales and Permits Activity
Build a unified activity feed: 20 most recent DEED sales (label='SALE') + 20 most recent ISSUED permits (label='PERMIT'). For sales show document_date as activity_date, document_amt as amount, label='SALE'. For permits show issuance_date as activity_date, NULL as amount, label='PERMIT'. Order by activity_date descending.
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 |
permits
| permit_id | job_id | bbl | borough | permit_type | permit_status | work_type | filing_date | issuance_date | expiration_date | bldg_type | owner_business_name | owner_business_type | permittee_business_name |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1688411 | 401749756 | 4006730016 | QUEENS | SG | ISSUED | NULL | 01/08/2004 | 01/08/2004 | 06/30/2004 | 2 | HIGH PERFORMANCE FITNESS CENTER | PARTNERSHIP | PAUL SIGNS INC |
| 1756781 | 301820661 | 3026080079 | BROOKLYN | EW | ISSUED | MH | 09/02/2004 | 09/10/2004 | 09/10/2005 | 2 | NULL | 2022-05-09 00:00:00 | APEX REALTY CO. LLC 7183915300 O |
| 3136 | 402213289 | 4095540029 | QUEENS | SG | ISSUED | NULL | 01/30/2006 | 04/11/2006 | 06/30/2006 | 2 | Doral Bank: 7188507290 2022 | INDIVIDUAL | WNITECH DESIGN INC DBA SPACESIGN |
Expected output: Recent sales + permits chronological feed
Hint
Concepts
SELECT UNION ALL Set Operations
Practise the topic: SQL practice questions · CTE practice
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