SQL interview question: products with no sales
Problem
Shared data rulesFor each city and category, count the products on sale that nobody in that city bought in September 2026. Count older products and new arrivals separately.
Example
products| product_id | category | added_at | is_active | Row note |
|---|---|---|---|---|
| 1 | Beverages | 2025-03-01 | 1 | |
| 2 | Beverages | 2025-04-10 | 1 | |
| 3 | Beverages | 2026-09-24 | 1 | ← new arrival |
| 4 | Sweets & snacks | 2025-02-01 | 1 | |
| 5 | Sweets & snacks | 2025-02-01 | 0 | ← off sale |
- Product 3: new arrival
- Product 5: off sale
orders| order_id | city | status | created_at | Row note |
|---|---|---|---|---|
| 101 | Boston | delivered | 2026-09-05 13:00 | |
| 102 | Boston | delivered | 2026-08-20 13:00 | ← August — outside the period |
| 103 | Boston | delivered | 2026-09-03 11:00 | |
| 104 | Washington | delivered | 2026-09-10 12:00 |
- Order 102: August — outside the period
order_items| order_id | product_id |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 4 |
| 104 | 1 |
| city | category | unsold_old | unsold_new |
|---|---|---|---|
| Boston | Beverages | 1 | 1 |
| Boston | Sweets & snacks | 0 | 0 |
| Washington | Beverages | 1 | 1 |
| Washington | Sweets & snacks | 1 | 0 |
In Boston only beverage 1 sold in September: products 2 (old) and 3 (new) are unsold. In Washington product 4 is unsold; in Boston it sold, and product 5 is off sale.
Notes
- A new arrival is a product with added_at in September 2026; the rest are older.
- Cities are all cities in orders; a pair with no unsold products is a row of zeros.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.
Skills it tests
More interview questions
- SQL: Longest consecutive order-day streakHard · Consecutive streaks
- SQL: Weekly cohort retention tableHard · Retention
- SQL: Sessionization from event gapsHard · Time intervals
- SQL: Time between consecutive purchasesMedium · Compare with previous
- SQL: Median delivery time by groupMedium · Median and percentiles