Friend request acceptance rate by month

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 16 minutes. Part of the Pro drill bank.

A social app wants to know how many friend requests get accepted each month. The data is messy: the same pair can be requested several times, and the same accept can also be repeated. A request pair is (sender_id, send_to_id) from friend_requests. It was accepted if the same pair appears as (requester_id, accepter_id) in friend_accepts. For each month in report_months, look at the pairs that were requested in that month (by request_date) and return month_start, requested_pairs (the number of distinct pairs), accepted_pairs (how many of those pairs were accepted) and accept_rate (accepted divided by requested, rounded to 2 decimals, and 0 when there were no requests). Sort by month_start.

Requirements

• Count each pair once per month, even if it was requested several times.
• A pair is accepted when the same pair exists in friend_accepts.
• Return a row for every month in report_months, even with no requests.
• accept_rate is accepted pairs divided by requested pairs, rounded to 2 decimals, and 0 when nothing was requested.
• Sort by month_start.

Constraints

• The tables are friend_requests, friend_accepts and report_months.
• Repeated pairs exist in both tables, so use DISTINCT pairs and test acceptance with EXISTS.
• Start from report_months with a LEFT JOIN, so months without requests stay in the result.

Examples

Input: friend_requests sender_id | send_to_id | request_date 1 | 2 | 2024-01-03 1 | 2 | 2024-01-20 1 | 3 | 2024-01-05 2 | 3 | 2024-01-09 3 | 4 | 2024-01-11 1 | 2 | 2024-02-02 4 | 5 | 2024-02-07 5 | 1 | 2024-02-15 friend_accepts requester_id | accepter_id | accept_date 1 | 2 | 2024-01-04 1 | 2 | 2024-02-03 2 | 3 | 2024-01-10 4 | 5 | 2024-02-08 9 | 8 | 2024-02-09 report_months month_start 2024-01-01 2024-02-01 2024-03-01 Output: month_start | requested_pairs | accepted_pairs | accept_rate 2024-01-01 | 4 | 2 | 0.5 2024-02-01 | 3 | 2 | 0.67 2024-03-01 | 0 | 0 | 0 Why this passes: January has 4 distinct pairs and 2 were accepted (0.50). February has 3 distinct pairs and 2 were accepted (0.67). March has no requests, so its rate is 0.

Input: friend_requests sender_id | send_to_id | request_date 1 | 2 | 2024-01-03 1 | 2 | 2024-01-09 friend_accepts requester_id | accepter_id | accept_date 1 | 2 | 2024-01-04 1 | 2 | 2024-01-10 report_months month_start 2024-01-01 Output: month_start | requested_pairs | accepted_pairs | accept_rate 2024-01-01 | 1 | 1 | 1 Why this passes: One pair requested twice and accepted twice is one pair, rate 1.00.

Topics: lakebench, sql, distinct, rate, left join.

More SQL interview questions · All interview problems · Learn data engineering