Compute reservation diff for largest member
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given copies(copy_id, reserved_by_member_id) and members(member_id, referred_by_member_id), find the member with the largest member_id. Return a single row with: member_id, referred_by_member_id, and diff_of_reserved_copy_num defined as (number of copies reserved by that member) minus (number of copies reserved by their referrer). If the referrer is NULL or has no reservations, treat their count as 0.
Overview: This question evaluates competency in relational data manipulation and aggregation using SQL or Python, including joins, grouping, and handling missing referrer relationships.
Read the full Meta Data Engineer interview experience this question came from
You are given two tables:
1) copies(copy_id, reserved_by_member_id)
2) members(member_id, referred_by_member_id)
Each row in copies represents a copy that has been reserved by a member. Each row in members represents a member and the member who referred them (if any).
Write a SQL query to find the member with the largest member_id and return a single row with the following columns:
- member_id
- referred_by_member_id
- diff_of_reserved_copy_num, defined as:
(number of copies reserved by that member)
minus
(number of copies reserved by their referrer)
If the referrer is NULL or the referrer has no reservations, treat the referrer's reservation count as 0.
Return exactly one row for the member with the largest member_id.
Tables
members(member_id INT, referred_by_member_id INT)
copies(copy_id INT, reserved_by_member_id INT)
Hints
- First aggregate the number of copies reserved per member using GROUP BY.
- Find the member with the largest member_id, then join their row to their referrer’s reservation count and subtract, using COALESCE when the referrer is NULL or has no reservations.