Compute multi-account user distribution and unread pct

You are working on a product where a user can have multiple accounts, and each account can receive notifications.

Tables

Assume the following schemas:

users

• user_id BIGINT PRIMARY KEY
• created_at TIMESTAMP

accounts

• account_id BIGINT PRIMARY KEY
• user_id BIGINT NOT NULL REFERENCES users(user_id)
• created_at TIMESTAMP

notifications

• notification_id BIGINT PRIMARY KEY
• account_id BIGINT NOT NULL REFERENCES accounts(account_id)
• created_at TIMESTAMP
• is_read BOOLEAN -- FALSE means unread

Tasks

1. Account-count distribution: Return the number of users who have:

• exactly 2 accounts
• exactly 3 accounts
• 4 or more accounts

Required output columns: account_bucket, num_users.

1. Unread-notification rate among multi-account users: Among users with 2+ accounts, compute the percentage of users who have at least one unread notification across any of their accounts.

Required output columns: pct_users_with_unread (as a percent or decimal; specify which you choose).