Airbnb: Host Response Rate Within 24 Hours — SQL Problem
Guests on Airbnb often message a host before booking, and a quick answer wins the booking.
- Difficulty: Medium
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #557
Problem statement
Guests on Airbnb often message a host before booking, and a quick answer wins the booking. The host-quality team tracks how many of those inquiries each host answers within 24 hours.
An inquiry counts as answered in time when first_reply_at is set and is at most 24 hours (1,440 minutes) after sent_at; exactly 24 hours still counts. An inquiry never answered (first_reply_at NULL) counts as not answered. For every host with at least one inquiry, return host_id, inquiries, answered_in_24h and response_rate — the percentage answered in time, rounded to 2 decimal places. Order by response_rate descending, then host_id ascending.
A practice scenario set at Airbnb: the tables and data are invented, and this is not a question from, or endorsed by, Airbnb.
Tables
Table: Inquiry
| Column | Type |
|---|---|
| inquiry_id | int |
| host_id | int |
| guest_id | int |
| sent_at | datetime |
| first_reply_at | datetime |
Primary key: inquiry_id.
One row per guest message thread opened with a host. first_reply_at is the host's first answer, NULL if there is none.
Examples
Example 1
Inquiry
| inquiry_id | host_id | guest_id | sent_at | first_reply_at |
|---|---|---|---|---|
| 1 | 7 | 101 | 2025-03-01 09:00:00 | 2025-03-01 09:40:00 |
| 2 | 7 | 102 | 2025-03-02 22:15:00 | 2025-03-03 22:15:00 |
| 3 | 7 | 103 | 2025-03-04 08:00:00 | 2025-03-05 08:01:00 |
| 4 | 8 | 104 | 2025-03-01 12:00:00 | NULL |
| 5 | 8 | 105 | 2025-03-06 18:30:00 | 2025-03-06 19:00:00 |
| 6 | 9 | 106 | 2025-03-07 07:45:00 | 2025-03-07 10:00:00 |
| 7 | 9 | 107 | 2025-03-09 23:50:00 | 2025-03-10 06:10:00 |
Output
| host_id | inquiries | answered_in_24h | response_rate |
|---|---|---|---|
| 9 | 2 | 2 | 100 |
| 7 | 3 | 2 | 66.67 |
| 8 | 2 | 1 | 50 |
How to solve Airbnb: Host Response Rate Within 24 Hours
One group per host gives both counts: COUNT(*) is every inquiry, and a conditional sum counts the ones answered in time. The reply time is TIMESTAMPDIFF(MINUTE, sent_at, first_reply_at), and "within 24 hours" is <= 1440 — an answer exactly a day later is still on time, one minute more is not.
The NULL case works without special code. When first_reply_at is NULL the difference is NULL, the CASE condition is unknown, and the ELSE 0 branch counts it as not answered — which is what the statement says. The alternative spells it out with IS NOT NULL and measures in seconds instead; both agree.
The percentage multiplies by 100 before dividing (/ is decimal division in MySQL) and rounds to two places. The statement fixes the order, and hosts can share a rate, so host_id breaks ties. MySQL lets ORDER BY use the response_rate alias. The alternative computes the counts in a derived table first so the rate does not repeat the long expression. One scan, one group per host.
Reference solution (MySQL)
SELECT host_id,
COUNT(*) AS inquiries,
SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, sent_at, first_reply_at) <= 1440 THEN 1 ELSE 0 END) AS answered_in_24h,
ROUND(100 * SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, sent_at, first_reply_at) <= 1440 THEN 1 ELSE 0 END) / COUNT(*), 2) AS response_rate
FROM Inquiry
GROUP BY host_id
ORDER BY response_rate DESC, host_idAnother way
SELECT host_id, inquiries, answered_in_24h, ROUND(100 * answered_in_24h / inquiries, 2) AS response_rate
FROM (
SELECT host_id, COUNT(inquiry_id) AS inquiries,
COUNT(CASE WHEN first_reply_at IS NOT NULL AND TIMESTAMPDIFF(SECOND, sent_at, first_reply_at) <= 86400 THEN 1 END) AS answered_in_24h
FROM Inquiry GROUP BY host_id
) t
ORDER BY response_rate DESC, host_id← Airbnb: Hosts Meeting a Simplified Superhost Bar · Airbnb: Search-to-Booking Funnel by Platform →