Compute unread and multi-account user percentages
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You’re given two tables. Write ANSI-SQL to answer parts (a)–(d). Treat a notification as unread if read_at IS NULL. Denominator for user-level percentages is all user_ids present in people_users (include users with zero notifications).
Schema and small samples:
people_users
person_id | user_id
--------- | -------
1 | 10
1 | 11
2 | 20
3 | 30
4 | 40
4 | 41
notifications
notification_id | user_id | created_at | read_at | notification_type
--------------- | ------- | ------------------- | ------------------- | -----------------
100 | 10 | 2025-08-30 10:00:00 | NULL | message
101 | 10 | 2025-08-31 12:00:00 | 2025-08-31 12:30:00 | mention
102 | 11 | 2025-09-01 08:00:00 | NULL | message
103 | 20 | 2025-09-01 09:00:00 | 2025-09-01 09:05:00 | follow
104 | 30 | 2025-08-28 15:00:00 | NULL | message
105 | 41 | 2025-08-29 17:00:00 | 2025-08-29 17:02:00 | like
Tasks:
(a) What percentage of users have at least one unread notification right now? Return both numerator, denominator, and percentage with 2 decimals.
(b) Break (a) down by notification_type (i.e., percentage of users who have ≥1 unread of each type). Users may appear in multiple types; report each type’s numerator, denominator, and percentage.
(c) What percentage of persons (distinct person_id) have multiple accounts (>1 user_id)? Report both the percent of persons with multiple accounts and the percent of user_ids that belong to such multi-account persons.
(d) Make your queries robust to duplicate rows in notifications (e.g., same notification_id accidentally logged twice). Explain your deduping assumption briefly in a SQL comment.
Overview: This question evaluates understanding of SQL data manipulation skills such as aggregations, joins, NULL handling, deduplication reasoning, and user- versus person-level computations for accurate percentage metrics.
Percentage of users with at least one unread notification
You are given two tables: people_users (all users in the system) and notifications (notification events). Treat a notification as unread if read_at IS NULL.
Compute the percentage of users who have at least one unread notification.
- The denominator for this user-level percentage is ALL distinct user_ids present in people_users (include users with zero notifications).
- Return numerator (users with ≥1 unread), denominator (all users), and percentage rounded to 2 decimals.
Tables
people_users(person_id INT, user_id INT)
notifications(log_id INT, notification_id INT, user_id INT, created_at TIMESTAMP, read_at TIMESTAMP, notification_type VARCHAR(20))
Hints
- Denominator is all distinct user_ids from people_users (not from notifications).
- Use COUNT(DISTINCT user_id) for the numerator to avoid counting multiple notifications per user.
Unread-user percentage by notification type
Using the same tables and unread definition (read_at IS NULL), break down the result by notification_type.
For each notification_type, compute:
- numerator: number of distinct users who have ≥1 unread notification of that type
- denominator: number of distinct users in people_users (include users with zero notifications)
- percentage: numerator / denominator * 100, rounded to 2 decimals
A user can appear in multiple types. Report one row per notification_type present in notifications (even if its numerator is 0).
Tables
people_users(person_id INT, user_id INT)
notifications(log_id INT, notification_id INT, user_id INT, created_at TIMESTAMP, read_at TIMESTAMP, notification_type VARCHAR(20))
Hints
- Compute the denominator once from people_users and reuse it for every type.
- To include types with 0 unread users, build a list of all types then LEFT JOIN unread users.
Multi-account person percentages
Using people_users, answer both of the following:
1) What percentage of persons (distinct person_id) have multiple accounts (> 1 user_id)?
2) What percentage of user_ids belong to persons who have multiple accounts?
Return:
- multi_account_persons, total_persons, pct_persons_multi_account (2 decimals)
- user_ids_in_multi_account_persons, total_user_ids, pct_user_ids_in_multi_account_persons (2 decimals)
Tables
people_users(person_id INT, user_id INT)
Hints
- First compute how many user_ids each person_id has, then filter to those with > 1.
- For the user_id percentage, count distinct user_ids belonging to multi-account persons divided by all distinct user_ids.
Robust unread-user percentage with notification deduplication
The notifications table may contain accidental duplicate rows for the same real notification (e.g., the same notification_id logged twice).
Write an ANSI-SQL query that returns the same output as question (a): numerator, denominator, and percent of users with ≥1 unread notification, BUT make it robust to duplicates in notifications.
Add a brief SQL comment explaining your deduplication assumption (e.g., how you choose which row to keep per notification_id).
Tables
people_users(person_id INT, user_id INT)
notifications(log_id INT, notification_id INT, user_id INT, created_at TIMESTAMP, read_at TIMESTAMP, notification_type VARCHAR(20))
Hints
- Use a window function like ROW_NUMBER() to pick one row per notification_id.
- After deduplication, compute the unread-user numerator using DISTINCT user_id.