Compute unread and multi-account user percentages
MetaData Scientist · Technical Screen
Last updated
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.
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.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.