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`
Quick Answer: 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.