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…