Total Sales by Year
ASKED IN INTERVIEWAirbnb
Problem Statement
Create a report showing the total revenue per city for the years 2023, 2024, and 2025.
Rules:
- Output columns: city, revenue_2023, revenue_2024, revenue_2025.
- Revenue = price_per_night * num_nights.
- Ensure 0 is returned for years with no sales.
- Sort by city ASC.
Tests your understanding of
Pivot, CASE WHEN, Aggregation and Joins
Input Tables
bookings
| booking_id(INTEGER) | listing_id(INTEGER) | num_nights(INTEGER) | booking_date(DATE) |
|---|---|---|---|
| 1 | 101 | 3 | 2023-05-15 |
| 2 | 101 | 2 | 2024-06-10 |
| 3 | 102 | 5 | 2025-01-01 |
listings
| listing_id(INTEGER) | city(VARCHAR) | price_per_night(DECIMAL) |
|---|---|---|
| 101 | Paris | 200 |
| 102 | New York | 300 |
city_regions
| city(VARCHAR) | region(VARCHAR) |
|---|---|
| Paris | Europe |
Expected Output
| city(VARCHAR) | revenue_2023(DECIMAL) | revenue_2024(DECIMAL) | revenue_2025(DECIMAL) |
|---|---|---|---|
| New York | 0 | 0 | 1500 |
| Paris | 600 | 400 | 0 |
Tags
HardASKED IN INTERVIEWPivotCASE WHENAggregationJoins
25-35 min
45%