Interview Question Bank · Dedupe both sides · LEFT JOIN · cohort by request month
12. Friend request acceptance by month
HardGeneralQuestion #204
Concept: Dedupe both sides · LEFT JOIN · cohort by request monthshow
An acceptance rate needs a clean denominator and numerator. Reduce requests to one row per sender-receiver pair (dated by its first request), reduce accepts to distinct pairs, then LEFT JOIN accepts onto requests, so stray accepts with no request are ignored and months with no accepts stay at zero. Group by the request's month, not the accept's, so a late answer still counts for the month it was asked.
Example (different data):
WITH inv AS ( SELECT host_id, guest_id, MIN(invited_on) AS first_invite FROM invites GROUP BY host_id, guest_id ) SELECT to_char(first_invite, 'YYYY-MM') AS month, COUNT(*) AS invites FROM inv GROUP BY 1;
Your task
Threadly, a hobby forum, wants to know how often friend requests get accepted, by the month the request was sent. People sometimes re-send a request, and the accept log has a duplicate row and an accept with no matching request.
Count each sender-receiver pair once, in the month of its first request. For each month, return request_month (YYYY-MM), requests (pairs), accepted (pairs that were ever accepted by the receiver) and acceptance_rate (accepted / requests, rounded to 2 decimals). Sort by request_month.
Starting Postgres in your browser…
Checking your access…