Q01: Combined Reaction Volume (medium) Topic: Set Operations & Deduplication Meta's Feed and Reels pipelines log user reactions across distinct event streams. When creators cross-post Reels to Feed, dual-stream ingestion and network retries frequently generate duplicate events across both tables. Computing net user engagement requires uniting these disparate streams and eliminating duplicates across a three-part composite key: user ID, content ID, and timestamp. Problem Statement Calculate the number of distinct reaction events across both post_reactions and reel_reactions for each user during Q1 2024 (January 1 through March 31, inclusive). An event is uniquely identified by the tuple (user_id, content_id, reaction_date), ignoring reaction_id and reaction_type. Restrict the result to active users with at least 5 distinct events. Return user_id and distinct_reaction_count, sorted by count descending, then user_id ascending. Input Schemas & Sample Fixtures Table: post_reactions reaction_iduser_idcontent_idreaction_typereaction_date 110100like2024-01-05210101love2024-01-06310102like2024-01-07410103wow2024-01-08520200like2024-01-07 Table: reel_reactions reaction_iduser_idcontent_idreaction_typereaction_date 110100like2024-01-05210104haha2024-01-09320201wow2024-01-08 Expected Output user_iddistinct_reaction_count 105 Solution Walkthrough Project the Composite Event Key: Extract user_id, content_id, and reaction_date from both tables. Dropping surrogate keys such as reaction_id and ephemeral attributes like reaction_type aligns duplicate events across both streams. Restrict both queries to the Q1 2024 interval using BETWEEN '2024-01-01' AND '2024-03-31'.Unify and Deduplicate with UNION: Stack both streams using UNION rather than UNION ALL. Unlike UNION ALL, standard set UNION automatically removes duplicate tuples across streams and within client retry batches.Aggregate Events by User: Wrap the unified stream within a derived table, group by user_id, and calculate total unique events with COUNT(*).Enforce the Activity Threshold: Filter the grouped aggregates with HAVING COUNT(*) >= 5 to isolate qualifying active users. Sort the final output by distinct_reaction_count descending, breaking ties with user_id ascending. Verified SQL Solution SELECT user_id, COUNT(*) AS distinct_reaction_count FROM ( SELECT user_id, content_id, reaction_date FROM post_reactions WHERE reaction_date BETWEEN '2024-01-01' AND '2024-03-31' UNION SELECT user_id, content_id, reaction_date FROM reel_reactions WHERE reaction_date BETWEEN '2024-01-01' AND '2024-03-31' ) combined_reactions GROUP BY user_id HAVING COUNT(*) >= 5 ORDER BY distinct_reaction_count DESC, user_id ASC; Practice Combined Reaction Volume in In-Browser Sandbox →
Q02: Employee Hierarchy (medium) Topic: Self-Joins & NULL Handling Enterprise personnel systems track organizational reporting chains by pairing employees with their direct supervisors within a single table. Evaluating these self-referential structures requires traversing parent-child relationships while preserving executives and founders who lack a reporting manager. Problem Statement Return each employee's name alongside their direct manager's name. Top-level personnel without a manager must remain in the result with NULL displayed under manager. Sort the output by employee name in descending alphabetical order (Z to A). Input Schemas & Sample Fixtures Table: employees idnamemanager_id 1Alice SmithNULL2Bob Jones13Charlie BrownNULL4Diana Prince35Evan WrightNULL Expected Output employeemanager Evan WrightNULLDiana PrinceCharlie BrownCharlie BrownNULLBob JonesAlice SmithAlice SmithNULL Solution Walkthrough Establish Self-Referential Aliases: Query the employees table twice under distinct aliases: designate e for individual contributors and m for their respective managers.Preserve Top Leadership with a LEFT JOIN: Join on e.manager_id = m.id using an outer join. An INNER JOIN silently drops top executives whose manager_id is NULL, whereas a LEFT JOIN retains all employees regardless of managerial assignment.Project Columns and Sort: Select e.name AS employee alongside m.name AS manager. Order the result set by e.name DESC to present employees in reverse alphabetical sequence. Verified SQL Solution SELECT e.name AS employee, m.name AS manager FROM employees AS e LEFT JOIN employees AS m ON e.manager_id = m.id ORDER BY e.name DESC; Practice Employee Hierarchy in In-Browser Sandbox →
Q03: Duplicate Employee Names (easy) Topic: Grouping & Duplicate Detection Global identity systems must detect collision patterns when distinct employees share identical names across regional offices. Auditing these rosters requires identifying repeated names while preventing missing entries (NULL) from grouping into artificial duplicate clusters. Problem Statement Identify employee names that appear more than once in the directory. Return each duplicate name once, ignoring records where name is NULL. Sort the results in descending alphabetical order. Input Schemas & Sample Fixtures Table: employees employee_idnamedepartment 1Chris CoxEngineering2Chris CoxMarketing3Sheryl SandbergSales4Javier OlivanFinance Expected Output name Chris Cox Solution Walkthrough Exclude Missing Identifiers: Filter the dataset with WHERE name IS NOT NULL prior to grouping. Because SQL groups all NULL values into a single bucket, omitting this filter would falsely report unassigned entries as duplicate identities.Partition Rows by Name: Group records by name to consolidate shared entries across departments.Isolate Shared Names with HAVING: Apply HAVING COUNT(*) > 1 to discard unique employees and keep only names associated with multiple records.Sort Descending: Apply ORDER BY name DESC to list qualifying names in reverse alphabetical order. Verified SQL Solution SELECT name FROM employees WHERE name IS NOT NULL GROUP BY name HAVING COUNT(*) > 1 ORDER BY name DESC; Practice Duplicate Employee Names in In-Browser Sandbox →
Q04: Friend Requests Sent per User (easy) Topic: Inner Joins & Multi-Column Grouping Social network growth analysis measures user connection intent through outbound relationship invitations. Meta interviewers test how candidates join core dimensional entities, aggregate transaction volume, and guard against name collision across independent accounts. Problem Statement For every user who has initiated at least one friend request, find their name and total outbound invitations (regardless of status: accepted, pending, or rejected). Label the count total_requests_sent. Sort by total_requests_sent descending, breaking ties by user name ascending. Input Schemas & Sample Fixtures Table: users user_idname 1Mark Zuckerberg2Sheryl Sandberg3Chris Cox Table: friend_requests request_idsender_idstatusrequest_date 1011accepted2023-01-011021pending2023-01-051032accepted2023-02-01 Expected Output nametotal_requests_sent Mark Zuckerberg2Sheryl Sandberg1 Solution Walkthrough Join Users to Requests: Join users u with friend_requests f on f.sender_id = u.user_id. The INNER JOIN filters out inactive accounts with zero invitations, satisfying the problem constraint without secondary conditions.Group by Surrogate Key and Display Name: Group by both u.user_id and u.name. Including the primary key ensures that distinct users who share identical names do not accidentally collapse into a single aggregated row.Aggregate Request Volume: Compute COUNT(*) AS total_requests_sent to tally all invitation records for each qualifying user.Sort Deterministically: Order results using ORDER BY total_requests_sent DESC, u.name ASC to rank top senders first, resolving ties alphabetically. Verified SQL Solution SELECT u.name, COUNT(*) AS total_requests_sent FROM users u JOIN friend_requests f ON f.sender_id = u.user_id GROUP BY u.user_id, u.name ORDER BY total_requests_sent DESC, u.name ASC; Practice Friend Requests Sent per User in In-Browser Sandbox →