Find multi-account buckets and unread rate
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are analyzing a product in which one **user** can own multiple **accounts**.
Use the following schema:
**Table: `accounts`**
- `account_id` BIGINT
- `user_id` BIGINT
- `created_at` TIMESTAMP
- `last_visited_at` TIMESTAMP
**Table: `notifications`**
- `notification_id` BIGINT
- `account_id` BIGINT
- `created_at` TIMESTAMP
- `read_at` TIMESTAMP NULL
**Relationships**
- `accounts.user_id` identifies the person who owns the account.
- `notifications.account_id` references `accounts.account_id`.
**Definitions**
- A **multi-account user** is a user with at least 2 distinct accounts.
- A notification is **unread** if `read_at IS NULL` as of `2025-01-01 00:00:00 UTC`.
- Use all rows with `created_at <= '2025-01-01 00:00:00 UTC'`.
- Assume all timestamps are stored in UTC.
Write SQL for the following:
1. Using only the `accounts` table, return the number of users who have:
- exactly 2 accounts
- exactly 3 accounts
- 4 or more accounts
Required output columns:
- `account_bucket`
- `user_count`
2. Using `accounts` and `notifications`, among multi-account users, calculate the percentage of users who have at least one unread notification across any of their accounts.
Required output columns:
- `multi_account_users`
- `users_with_unread_notifications`
- `pct_users_with_unread_notifications`
Overview: This question evaluates the ability to manipulate relational data and compute aggregate metrics, testing skills in grouping and counting, filtering by timestamps, joining user-account and notification records, and calculating user-level percentages.
You are given two tables describing a product's user accounts and their notifications.
**`accounts`** — one row per account (a single user may own several accounts):
| column | type | meaning |
|---|---|---|
| `account_id` | BIGINT | unique account id |
| `user_id` | BIGINT | the person who owns the account |
| `created_at` | TIMESTAMP | when the account was created |
| `last_visited_at` | TIMESTAMP | last time the account was visited |
**`notifications`** — one row per notification sent to an account:
| column | type | meaning |
|---|---|---|
| `notification_id` | BIGINT | unique notification id |
| `account_id` | BIGINT | the account the notification was sent to |
| `created_at` | TIMESTAMP | when the notification was created |
| `read_at` | TIMESTAMP | when it was read; `NULL` means still unread |
Apply a fixed cutoff of `'2025-01-01 00:00:00'`: in **both** tables, only consider rows whose `created_at <= '2025-01-01 00:00:00'`.
Return a **single result set** that stacks two parts.
**Part 1 — account buckets.** Using only `accounts`, count how many distinct users own exactly 2 accounts, exactly 3 accounts, and 4 or more accounts. Emit one row per non-empty bucket.
**Part 2 — unread rate among multi-account users.** A user is *multi-account* if they own 2 or more accounts. Among those users, count how many have at least one **unread** notification (`read_at IS NULL`) on **any** of their accounts, and compute that count as a fraction of all multi-account users.
**Output columns (in this exact order):**
- `section` — `'part1'` for bucket rows, `'part2'` for the summary row.
- `account_bucket` — one of `'exactly_2'`, `'exactly_3'`, `'four_or_more'` for Part 1 rows; `NULL` in the Part 2 row.
- `user_count` — number of users in that bucket (Part 1 only); `NULL` in the Part 2 row.
- `multi_account_users` — total count of multi-account users (Part 2 only); `NULL` in Part 1 rows.
- `users_with_unread_notifications` — count of multi-account users with at least one unread notification (Part 2 only); `NULL` in Part 1 rows.
- `pct_users_with_unread_notifications` — `users_with_unread_notifications / multi_account_users`, rounded to 4 decimal places (Part 2 only); `NULL` in Part 1 rows.
**Sort** by `section` ascending, then by `account_bucket` ascending with `NULL` last.
Tables
accounts(account_id BIGINT, user_id BIGINT, created_at TIMESTAMP, last_visited_at TIMESTAMP)
notifications(notification_id BIGINT, account_id BIGINT, created_at TIMESTAMP, read_at TIMESTAMP)
Hints
- Aggregate accounts by user_id with COUNT(DISTINCT account_id), then label each user with a CASE expression for the 2 / 3 / 4+ buckets.
- Apply the same created_at <= '2025-01-01 00:00:00' cutoff to BOTH accounts and notifications before counting anything.
Community answers
Answer by nekkoya
With UseridBucket AS (
SELECT user_id,
COUNT(account_id) AS TAccount,
CASE COUNT(account_id)
WHEN 2 THEN 'exactly_2'
WHEN 3 THEN 'exactly_3'
ELSE 'four_or_more' END AS Bucket
FROM accounts
GROUP BY user_id
HAVING COUNT(account_id) >= 2),
BucketCount AS (
SELECT Bucket, Count(User_id) AS user_count
FROM UserIDBucket
GROUP BY Bucket),
UnreadUser AS (
SELECT COUNT(DISTINCT a.user_id) AS CUnreadUser
FROM UserIDBucket u
JOIN accounts a ON u.user_id = a.user_id
JOIN notifications n ON n.account_id = a.account_id
WHERE n.created_at <= '2025-01-01 00:00:00' AND read_at IS NULL)
SELECT bucket AS 'account_bucket',
'null' AS 'multi_account_users',
'null' AS 'pct_users_with_unread_notifications',
'part1' AS section,
user_count AS user_count,
'null' AS users_with_unread_notifications
FROM BucketCount
UNION
SELECT 'null' AS 'account_bucket',
Cuser AS 'multi_account_users',
ROUND(CUnreadUser * 1.0 / NULLIF(Cuser,0),1) AS 'pct_users_with_unread_notifications',
'part2' AS section,
'null' AS user_count,
CUnreadUser AS users_with_unread_notifications
FROM UnreadUser
CROSS JOIN (SELECT COUNT(user_id) Cuser FROM UseridBucket) K