Compute multi-account actives and unread coverage
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You have two tables.
Table: notifications
+--------+------------+------------+-------------------+--------+
| userid | ds | time | notification_type | status |
+--------+------------+------------+-------------------+--------+
| 101 | 2025-08-12 | 1755283200 | direct | unread |
| 102 | 2025-08-25 | 1756416000 | circle | read |
| 201 | 2025-08-05 | 1754524800 | direct | unread |
| 202 | 2025-08-29 | 1756761600 | direct | unread |
| 302 | 2025-08-03 | 1754352000 | direct | read |
| 303 | 2025-08-18 | 1755542400 | direct | unread |
| 304 | 2025-08-22 | 1755888000 | direct | unread |
| 401 | 2025-08-04 | 1754438400 | direct | read |
| 402 | 2025-08-05 | 1754524800 | circle | read |
| 403 | 2025-08-06 | 1754611200 | direct | read |
| 404 | 2025-08-27 | 1756588800 | direct | unread |
+--------+------------+------------+-------------------+--------+
Table: dim_circle_people
+-----------+--------+------------------+---------------+------------+
| person_id | userid | last_action_date | creation_date | ds |
+-----------+--------+------------------+---------------+------------+
| 1 | 101 | 2025-08-12 | 2024-05-01 | 2025-08-12 |
| 1 | 102 | 2025-08-25 | 2025-01-10 | 2025-08-25 |
| 1 | 103 | 2025-06-20 | 2025-06-01 | 2025-08-25 |
| 2 | 201 | 2025-08-05 | 2025-07-15 | 2025-08-05 |
| 2 | 202 | 2025-08-29 | 2025-08-01 | 2025-08-29 |
| 3 | 301 | 2025-07-31 | 2025-07-01 | 2025-08-01 |
| 3 | 302 | 2025-08-02 | 2025-08-02 | 2025-08-02 |
| 3 | 303 | 2025-08-18 | 2025-08-10 | 2025-08-18 |
| 3 | 304 | 2025-08-21 | 2025-08-21 | 2025-08-21 |
| 4 | 401 | 2025-08-03 | 2025-07-20 | 2025-08-03 |
| 4 | 402 | 2025-08-10 | 2025-07-20 | 2025-08-10 |
| 4 | 403 | 2025-08-15 | 2025-07-20 | 2025-08-15 |
| 4 | 404 | 2025-08-28 | 2025-07-20 | 2025-08-28 |
+-----------+--------+------------------+---------------+------------+
Assumptions:
- "Last month" means 2025-08-01 through 2025-08-31 (inclusive). Treat all timestamps as UTC. If both ds and time exist, use ds to filter month; time is auxiliary.
- A "person" may own multiple accounts (distinct userids map to the same person_id). An account is "active during last month" if its last_action_date is between 2025-08-01 and 2025-08-31 inclusive.
- Unread notifications are rows in notifications with status = 'unread' and ds in 2025-08-01..2025-08-31.
Tasks (write ANSI SQL; CTEs allowed):
(a) Return a single row with three columns: two_accounts, three_accounts, gt_three_accounts — the counts of distinct person_id who had exactly 2, exactly 3, and more than 3 active accounts during 2025-08.
(b) Among people with >=2 active accounts in 2025-08, compute the percentage (0–100 with two decimals) who had at least one unread notification on any of their accounts in 2025-08. Return numerator, denominator, and pct. Ensure a person is counted once even if multiple accounts had unread. Handle persons with zero notifications correctly (they should be in the denominator but not the numerator).
Edge cases to handle explicitly in your SQL: duplicate rows per userid/day in notifications; persons whose some accounts were created during 2025-08; userids present in notifications but missing in dim_circle_people (exclude those from person-level stats).
Overview: This question evaluates proficiency in SQL data manipulation and analytics, including per-person aggregation across multiple accounts, deduplication, date-based filtering, join logic, and cohort-level percentage calculations.
Count people by number of active accounts in 2025-08
You are given two tables: (1) notifications (events at the account/userid level) and (2) dim_circle_people (mapping multiple userids to the same person_id, plus account activity fields).
Definitions (use these exact dates):
- The month is 2025-08-01 through 2025-08-31 (inclusive). Use notifications.ds to filter the month.
- An account (userid) is "active during 2025-08" if dim_circle_people.last_action_date is between 2025-08-01 and 2025-08-31 (inclusive).
- A person may own multiple accounts (multiple userids can map to the same person_id).
Task (a): Return a single row with three columns:
- two_accounts: number of distinct person_id who had exactly 2 active accounts in 2025-08
- three_accounts: number of distinct person_id who had exactly 3 active accounts in 2025-08
- gt_three_accounts: number of distinct person_id who had more than 3 active accounts in 2025-08
Write ANSI SQL; CTEs are allowed.
Tables
notifications(userid INT, ds DATE, event_time BIGINT, notification_type VARCHAR(20), status VARCHAR(10))
dim_circle_people(person_id INT, userid INT, last_action_date DATE, creation_date DATE, ds DATE)
Hints
- First compute active accounts per person (distinct userids) using last_action_date within 2025-08.
- Use conditional aggregation over the per-person active account count to produce the three final columns.
Unread-notification coverage among people with >=2 active accounts in 2025-08
Using the same two tables and definitions:
- Month: 2025-08-01 through 2025-08-31 (inclusive), filtered by notifications.ds.
- An account is active in 2025-08 if dim_circle_people.last_action_date is within the month.
- Unread notifications are notifications rows with status = 'unread' and ds within the month.
Task (b): Among people with at least 2 active accounts in 2025-08, compute the unread coverage:
- numerator: number of distinct person_id (with >=2 active accounts) who had at least one unread notification on any of their accounts in 2025-08
- denominator: number of distinct person_id with >=2 active accounts in 2025-08
- pct: 100 * numerator / denominator, rounded to two decimals
Important edge cases:
- notifications may contain duplicates (same userid and ds repeated); a person should only be counted once.
- Some userids in notifications may not exist in dim_circle_people; exclude those from person-level stats.
- People with zero notifications must still be included in the denominator (but not the numerator).
Write ANSI SQL; CTEs allowed.
Tables
notifications(userid INT, ds DATE, event_time BIGINT, notification_type VARCHAR(20), status VARCHAR(10))
dim_circle_people(person_id INT, userid INT, last_action_date DATE, creation_date DATE, ds DATE)
Hints
- Build the denominator from people who have at least 2 active userids in 2025-08.
- For the numerator, de-duplicate at the person_id level after filtering unread notifications in 2025-08 and excluding unknown userids via a join.