Coding questions · Meta
Compute unread and multi-account user percentages
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.
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.
Table people_users
| column | type | constraints |
|---|---|---|
| person_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
Sample data:
| person_id | user_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 4 | 41 |
Table notifications
| column | type | constraints |
|---|---|---|
| log_id | INT | PRIMARY KEY, NOT NULL |
| notification_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| read_at | TIMESTAMP | |
| notification_type | VARCHAR(20) | NOT NULL |
Sample data:
| created_at | log_id | notification_id | notification_type | read_at | user_id |
|---|---|---|---|---|---|
| 2025-08-30 10:00:00 | 1 | 100 | message | NULL | 10 |
| 2025-08-31 12:00:00 | 2 | 101 | mention | 2025-08-31 12:30:00 | 10 |
| 2025-09-01 08:00:00 | 3 | 102 | message | NULL | 11 |
| 2025-09-01 09:00:00 | 4 | 103 | follow | 2025-09-01 09:05:00 | 20 |
| 2025-08-28 15:00:00 | 5 | 104 | message | NULL | 30 |
| 2025-08-29 17:00:00 | 6 | 105 | like | 2025-08-29 17:02:00 | 41 |
| 2025-08-28 15:00:00 | 7 | 104 | message | NULL | 30 |
| 2025-08-31 12:00:00 | 8 | 101 | mention | 2025-08-31 12:30:00 | 10 |
Expected output
One row with: numerator_users_with_unread, denominator_all_users, pct_users_with_unread (rounded to 2 decimals). Denominator counts distinct user_ids from people_users, including users with no notifications.
| denominator_all_users | numerator_users_with_unread | pct_users_with_unread |
|---|---|---|
| 6 | 3 | 50 |
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).
Table people_users
| column | type | constraints |
|---|---|---|
| person_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
Sample data:
| person_id | user_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 4 | 41 |
Table notifications
| column | type | constraints |
|---|---|---|
| log_id | INT | PRIMARY KEY, NOT NULL |
| notification_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| read_at | TIMESTAMP | |
| notification_type | VARCHAR(20) | NOT NULL |
Sample data:
| created_at | log_id | notification_id | notification_type | read_at | user_id |
|---|---|---|---|---|---|
| 2025-08-30 10:00:00 | 1 | 100 | message | NULL | 10 |
| 2025-08-31 12:00:00 | 2 | 101 | mention | 2025-08-31 12:30:00 | 10 |
| 2025-09-01 08:00:00 | 3 | 102 | message | NULL | 11 |
| 2025-09-01 09:00:00 | 4 | 103 | follow | 2025-09-01 09:05:00 | 20 |
| 2025-08-28 15:00:00 | 5 | 104 | message | NULL | 30 |
| 2025-08-29 17:00:00 | 6 | 105 | like | 2025-08-29 17:02:00 | 41 |
| 2025-08-28 15:00:00 | 7 | 104 | message | NULL | 30 |
| 2025-08-31 12:00:00 | 8 | 101 | mention | 2025-08-31 12:30:00 | 10 |
Expected output
One row per notification_type in notifications with numerator (distinct users with ≥1 unread of that type), denominator (distinct users from people_users), and percent rounded to 2 decimals.
| denominator_all_users | notification_type | numerator_users_with_unread | pct_users_with_unread |
|---|---|---|---|
| 6 | follow | 0 | 0 |
| 6 | like | 0 | 0 |
| 6 | mention | 0 | 0 |
| 6 | message | 3 | 50 |
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:
- What percentage of persons (distinct person_id) have multiple accounts (> 1 user_id)?
- 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)
Table people_users
| column | type | constraints |
|---|---|---|
| person_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
Sample data:
| person_id | user_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 4 | 41 |
Expected output
One row containing both the person-level and user-level percentages related to multi-account persons, rounded to 2 decimals.
| multi_account_persons | pct_persons_multi_account | pct_user_ids_in_multi_account_persons | total_persons | total_user_ids | user_ids_in_multi_account_persons |
|---|---|---|---|---|---|
| 2 | 50 | 66.67 | 4 | 6 | 4 |
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).
Table people_users
| column | type | constraints |
|---|---|---|
| person_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
Sample data:
| person_id | user_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 4 | 41 |
Table notifications
| column | type | constraints |
|---|---|---|
| log_id | INT | PRIMARY KEY, NOT NULL |
| notification_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| read_at | TIMESTAMP | |
| notification_type | VARCHAR(20) | NOT NULL |
Sample data:
| created_at | log_id | notification_id | notification_type | read_at | user_id |
|---|---|---|---|---|---|
| 2025-08-30 10:00:00 | 1 | 100 | message | NULL | 10 |
| 2025-08-31 12:00:00 | 2 | 101 | mention | 2025-08-31 12:30:00 | 10 |
| 2025-09-01 08:00:00 | 3 | 102 | message | NULL | 11 |
| 2025-09-01 09:00:00 | 4 | 103 | follow | 2025-09-01 09:05:00 | 20 |
| 2025-08-28 15:00:00 | 5 | 104 | message | NULL | 30 |
| 2025-08-29 17:00:00 | 6 | 105 | like | 2025-08-29 17:02:00 | 41 |
| 2025-08-28 15:00:00 | 7 | 104 | message | NULL | 30 |
| 2025-08-31 12:00:00 | 8 | 101 | mention | 2025-08-31 12:30:00 | 10 |
Expected output
One row with numerator, denominator, and percentage (2 decimals). The result should not change if duplicate rows exist for the same notification_id.
| denominator_all_users | numerator_users_with_unread | pct_users_with_unread |
|---|---|---|
| 6 | 3 | 50 |
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.