Find multi-account buckets and unread rate

Quick 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.

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.

|Home/Data Manipulation (SQL/Python)/Meta
Meta logo
Meta
Jan 21, 2026, 12:00 AM
mediumData ScientistTechnical ScreenData Manipulation (SQL/Python)
6
0

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
Loading comments...