Write SQL for multi-account metrics
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
A consumer app allows one user to own multiple accounts. Use SQL to answer the following questions.
Assume the database has these tables:
`accounts`
- `account_id` BIGINT — primary key
- `user_id` BIGINT — the owner of the account
- `created_at` TIMESTAMP — when the account was created
- `last_visit_at` TIMESTAMP — the most recent time the account was visited
`notifications`
- `notification_id` BIGINT — primary key
- `account_id` BIGINT — foreign key to `accounts.account_id`
- `sent_at` TIMESTAMP — when the notification was sent
- `read_at` TIMESTAMP — when the notification was read; `NULL` means the notification is still unread
Assumptions:
- One user can have many accounts.
- One account can have many notifications.
- All timestamps are in UTC.
- A "multi-account user" is a user with at least 2 distinct accounts.
- A user "has unread notifications" if at least one of their accounts has at least one notification with `read_at IS NULL`.
Write SQL for both tasks below:
1. Count how many users have exactly 2 accounts, exactly 3 accounts, and 4 or more accounts.
- Required output columns: `account_bucket`, `user_count`
- Use bucket values: `'2'`, `'3'`, `'4+'`
2. Among multi-account users only, compute the percentage of users who currently have at least one unread notification across any of their accounts.
- Required output columns: `users_with_unread`, `multi_account_users`, `pct_users_with_unread`
- Return the percentage as a decimal between 0 and 1.
Quick Answer: This question evaluates proficiency in SQL for multi-table aggregation, grouping, joins, and conditional counting within a user-account-notification schema, categorized under Data Manipulation (SQL/Python) and emphasizing practical application of query formulation and metric computation.