Table Schema
Inspect
Table
| Column | Type |
|---|---|
| booking_id | integer |
| listing_id | integer |
| check_in | date |
| check_out | date |
| Column | Type |
|---|---|
| listing_id | integer |
| date | date |
| is_available | boolean |
Listing
Occupancy
Rate
Calculate occupancy rate = booked days / total available days for each listing.
Inspect
Sample Data
Input
Output
Sample Input: bookings
| booking_id | listing_id | check_in | check_out |
|---|---|---|---|
| 1 | 101 | 2024-01-01 | 2024-01-03 |
| 2 | 101 | 2024-01-05 | 2024-01-07 |
| 3 | 102 | 2024-01-02 | 2024-01-04 |
Sample Input: calendar
| listing_id | date | is_available |
|---|---|---|
| 101 | 2024-01-01 | false |
| 101 | 2024-01-02 | false |
| 101 | 2024-01-03 | true |
| 101 | 2024-01-04 | true |
| 101 | 2024-01-05 | false |
| 102 | 2024-01-02 | false |
| 102 | 2024-01-03 | false |
Expected Output
| listing_id | occupancy_rate |
|---|---|
| 101 | 0.60 |
| 102 | 1.00 |
SQL Editor
Run
Query
Waiting for query
| listing_id | occupancy_rate |
|---|---|
| 101 | 0.60 |
| 102 | 1.00 |
Hints
Unlock
Clues
Hint 01: Identify the grouping level required by the output.
Hint 02: Aggregate with COUNT, SUM, AVG, or a window function as needed.
Hint 03: Filter after aggregation with HAVING or after ranking with an outer query.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT listing_id, ROUND(1.0 * SUM(CASE WHEN is_available = false THEN 1 ELSE 0 END) / COUNT(*), 2) AS occupancy_rate FROM calendar GROUP BY listing_id;
Explanation
Step By
Step
01
Read the expected output columns to determine the final grain.
02
Aggregate or rank the input rows to calculate the requested metric.
03
Filter, sort, and alias the final columns to match the output.
Related Questions