SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 16 minutes. Part of the Pro drill bank.
friend_requests and friend_accepts can both contain the same pair more than once. A request pair is (sender_id, send_to_id); it was accepted if the same pair (requester_id, accepter_id) appears in friend_accepts. For each month in report_months, consider the pairs requested in that month (by request_date) and return: requested_pairs: distinct pairs requested accepted_pairs: how many of those pairs were accepted accept_rate: accepted / requested, rounded to 2 decimals, and 0 when nothing was requested Columns: month_start, requested_pairs, accepted_pairs, accept_rate. Order by month_start.
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
Interview-style drill: Per month, distinct accepted pairs over distinct requested pairs, with 0 for months with no requests.
`friend_requests` and `friend_accepts` can both contain the same pair more than once. A request pair is `(sender_id, send_to_id)`; it was accepted if the same pair `(requester_id, accepter_id)` appears in `friend_accepts`. For each month in `report_months`, consider the pairs requested in that month (by `request_date`) and return: - `requested_pairs`: distinct pairs requested - `accepted_pairs`: how many of those pairs were accepted - `accept_rate`: accepted / requested, rounded to 2 decimals, and 0 when nothing was requested Columns: `month_start`, `requested_pairs`, `accepted_pairs`, `accept_rate`. Order by `month_start`.