Drill 12 of 12 in General questions
0

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…