Drill 4 of 4 in Marketplace questions
0

Interview Question Bank · Interval math · AVG over NULLs · COUNT FILTER

4. Host response time and the 1-hour reply rate

MediumMarketplaceQuestion #269

Concept: Interval math · AVG over NULLs · COUNT FILTERshow

EXTRACT(EPOCH FROM later - earlier) / 3600 turns a timestamp gap into decimal hours. AVG skips NULLs, so an average over a nullable gap already covers only the rows that have one. A rate is different: decide its denominator on purpose. If unanswered rows count as failures, divide by COUNT(*) and count successes with FILTER, since a NULL gap never passes the condition.

Example (different data):

SELECT courier,
  ROUND(AVG(EXTRACT(EPOCH FROM delivered_at - picked_at)) / 3600, 2) AS avg_hours,
  ROUND(100.0 * COUNT(*) FILTER (WHERE delivered_at - picked_at <= INTERVAL '2 hours') / COUNT(*), 1) AS fast_pct
FROM parcels
GROUP BY courier;

Your task

Burrowstay shows guests how quickly each host replies to booking inquiries. Some inquiries never got a reply at all.

For each host with inquiries, return host (their name), inquiries (all of them), avg_response_hours (average of responded_at - sent_at in hours over answered inquiries, rounded to 2) and within_1h_pct (inquiries answered within 60 minutes, inclusive, as a % of all inquiries, rounded to 1; unanswered ones count as not within the hour). Sort by within_1h_pct descending, then host.

Starting Postgres in your browser…

Checking your access…