Airbnb: Host Response Rate Within 24 Hours

Medium
557.

Airbnb: Host Response Rate Within 24 Hours

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

ColumnType
inquiry_idint
host_idint
guest_idint
sent_atdatetime
first_reply_atdatetime

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.

Example

The rows must come back in the order shown.

Inquiry

inquiry_idhost_idguest_idsent_atfirst_reply_at
171012025-03-01 09:00:002025-03-01 09:40:00
271022025-03-02 22:15:002025-03-03 22:15:00
371032025-03-04 08:00:002025-03-05 08:01:00
481042025-03-01 12:00:00NULL
581052025-03-06 18:30:002025-03-06 19:00:00
691062025-03-07 07:45:002025-03-07 10:00:00
791072025-03-09 23:50:002025-03-10 06:10:00

Output

host_idinquiriesanswered_in_24hresponse_rate
922100
73266.67
82150
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
inquiry_idint
host_idint
guest_idint
sent_atdatetime
first_reply_atdatetime

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_idhost_idguest_idsent_atfirst_reply_at
171012025-03-01 09:00:002025-03-01 09:40:00
271022025-03-02 22:15:002025-03-03 22:15:00
371032025-03-04 08:00:002025-03-05 08:01:00
481042025-03-01 12:00:00NULL
581052025-03-06 18:30:002025-03-06 19:00:00
691062025-03-07 07:45:002025-03-07 10:00:00
791072025-03-09 23:50:002025-03-10 06:10:00

Output

host_idinquiriesanswered_in_24hresponse_rate
922100
73266.67
82150

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_id

Another 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 →