Find multi-account buckets and unread rate

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