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.

Loading coding console...