Compute multi-account user distribution and unread pct
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are working on a product where a **user** can have multiple **accounts**, and each account can receive **notifications**.
### Tables
Assume the following schemas:
**users**
- `user_id` BIGINT PRIMARY KEY
- `created_at` TIMESTAMP
**accounts**
- `account_id` BIGINT PRIMARY KEY
- `user_id` BIGINT NOT NULL REFERENCES `users(user_id)`
- `created_at` TIMESTAMP
**notifications**
- `notification_id` BIGINT PRIMARY KEY
- `account_id` BIGINT NOT NULL REFERENCES `accounts(account_id)`
- `created_at` TIMESTAMP
- `is_read` BOOLEAN -- `FALSE` means unread
### Tasks
1) **Account-count distribution**: Return the number of users who have:
- exactly **2** accounts
- exactly **3** accounts
- **4 or more** accounts
**Required output columns**: `account_bucket`, `num_users`.
2) **Unread-notification rate among multi-account users**: Among users with **2+ accounts**, compute the **percentage of users** who have **at least one unread notification** across any of their accounts.
**Required output columns**: `pct_users_with_unread` (as a percent or decimal; specify which you choose).
Overview: This question evaluates a data scientist's ability to perform relational data manipulation and aggregation with SQL or Python, covering competencies such as handling one-to-many joins, grouping and bucketing counts, deduplicating across related tables, and computing cohort-level percentages from boolean flags.
Multi-account user distribution (2, 3, 4+ accounts)
You are given a list of user accounts in the `accounts` table (each row is one account belonging to a user). Write a SQL query to return the number of users who have exactly 2 accounts, exactly 3 accounts, and 4 or more accounts.
Output columns:
- `accounts_bucket`: one of ('2', '3', '4+')
- `user_count`: number of users in that bucket
Notes:
- Users with 0 or 1 account should not appear in the output.
- Count users based on the number of rows in `accounts` per `user_id`.
Tables
users(user_id INT, user_name VARCHAR(50), created_at DATE)
accounts(account_id INT, user_id INT, created_at DATE, last_visit_at DATE)
notifications(notification_id INT, account_id INT, created_at TIMESTAMP, read_at TIMESTAMP, notification_type VARCHAR(30))
Hints
- First aggregate `accounts` by `user_id` to get an account count per user.
- Use a CASE expression to bucket counts into 2 / 3 / 4+.
Percent of multi-account users with unread notifications
A user can have multiple accounts (`accounts` table). Notifications belong to accounts (`notifications.account_id`). A notification is considered **unread** when `read_at` IS NULL.
Write a SQL query to compute, among users with **2 or more accounts**, what percentage of users have **at least one unread notification** across any of their accounts.
Output columns:
- `total_multi_account_users`
- `users_with_unread`
- `pct_with_unread` (as a percentage from 0 to 100, rounded to 2 decimals)
Notes:
- Users with multiple accounts but zero notifications should be counted in the denominator.
- Ignore notifications whose `account_id` is NULL or does not match any account.
Tables
users(user_id INT, user_name VARCHAR(50), created_at DATE)
accounts(account_id INT, user_id INT, created_at DATE, last_visit_at DATE)
notifications(notification_id INT, account_id INT, created_at TIMESTAMP, read_at TIMESTAMP, notification_type VARCHAR(30))
Hints
- Build the denominator first: users with at least 2 accounts.
- For the numerator, count distinct users who have at least one notification where `read_at` is NULL.