cswork / libraryOpen practice workspace

Coding questions · Meta

Compute multi-account user distribution and unread pct

This question evaluates a data scientist's ability to perform relational data manipulation and aggregation with SQL or Python, covering competencies such as handling one-to-many joins, grouping and bucketing counts, deduplicating across related tables, and computing cohort-level percentages from boolean flags.

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.

  2. 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).

Multi-account user distribution (2, 3, 4+ accounts)

You are given a list of user accounts in the accounts table (each row is one account belonging to a user). Write a SQL query to return the number of users who have exactly 2 accounts, exactly 3 accounts, and 4 or more accounts.

Output columns:

  • accounts_bucket: one of ('2', '3', '4+')
  • user_count: number of users in that bucket

Notes:

  • Users with 0 or 1 account should not appear in the output.
  • Count users based on the number of rows in accounts per user_id.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
user_nameVARCHAR(50)NOT NULL
created_atDATENOT NULL

Sample data:

created_atuser_iduser_name
2025-01-051Ava
2025-01-062Ben
2025-01-073Cleo
2025-01-084Dev
2025-01-095Eli
2025-01-106Fay
2025-01-117Gus
2025-01-128Hana

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
user_idINTNOT NULL
created_atDATENOT NULL
last_visit_atDATE

Sample data:

account_idcreated_atlast_visit_atuser_id
1012025-02-012025-05-301
2012025-02-022025-05-282
2022025-02-152025-05-312
3012025-02-032025-05-203
3022025-03-012025-05-293
3032025-03-20NULL3
4012025-02-042025-05-014
4022025-02-182025-05-154
4032025-03-052025-05-214
4042025-03-252025-05-314
5012025-02-052025-04-305
5022025-02-192025-05-105
5032025-03-062025-05-115
5042025-03-262025-05-125
5052025-04-102025-05-135
6012025-02-062025-05-056
6022025-04-012025-05-256
8012025-02-072025-05-228
8022025-02-202025-05-238
8032025-03-072025-05-248
8042025-04-022025-05-258

Table notifications

columntypeconstraints
notification_idINTPRIMARY KEY
account_idINT
created_atTIMESTAMPNOT NULL
read_atTIMESTAMP
notification_typeVARCHAR(30)NOT NULL

Sample data:

account_idcreated_atnotification_idnotification_typeread_at
2012025-05-20 09:00:001promo2025-05-20 10:00:00
2022025-05-31 08:00:002securityNULL
3012025-05-10 12:00:003system2025-05-10 12:05:00
3022025-05-29 15:00:004promo2025-05-29 16:00:00
3032025-05-30 11:00:005promo2025-05-30 11:10:00
5052025-05-13 09:30:006systemNULL
6012025-05-01 07:00:007promo2025-05-01 07:05:00
6022025-05-25 18:00:008security2025-05-26 08:00:00
8042025-05-25 10:00:009promoNULL
NULL2025-05-28 10:00:0010systemNULL

Expected output

Three rows: counts of users with exactly 2 accounts, exactly 3 accounts, and 4 or more accounts.

accounts_bucketuser_count
22
31
4+3

Hints

  1. First aggregate accounts by user_id to get an account count per user.
  2. Use a CASE expression to bucket counts into 2 / 3 / 4+.

Percent of multi-account users with unread notifications

A user can have multiple accounts (accounts table). Notifications belong to accounts (notifications.account_id). A notification is considered unread when read_at IS NULL.

Write a SQL query to compute, among users with 2 or more accounts, what percentage of users have at least one unread notification across any of their accounts.

Output columns:

  • total_multi_account_users
  • users_with_unread
  • pct_with_unread (as a percentage from 0 to 100, rounded to 2 decimals)

Notes:

  • Users with multiple accounts but zero notifications should be counted in the denominator.
  • Ignore notifications whose account_id is NULL or does not match any account.

Table users

columntypeconstraints
user_idINTPRIMARY KEY
user_nameVARCHAR(50)NOT NULL
created_atDATENOT NULL

Sample data:

created_atuser_iduser_name
2025-01-051Ava
2025-01-062Ben
2025-01-073Cleo
2025-01-084Dev
2025-01-095Eli
2025-01-106Fay
2025-01-117Gus
2025-01-128Hana

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
user_idINTNOT NULL
created_atDATENOT NULL
last_visit_atDATE

Sample data:

account_idcreated_atlast_visit_atuser_id
1012025-02-012025-05-301
2012025-02-022025-05-282
2022025-02-152025-05-312
3012025-02-032025-05-203
3022025-03-012025-05-293
3032025-03-20NULL3
4012025-02-042025-05-014
4022025-02-182025-05-154
4032025-03-052025-05-214
4042025-03-252025-05-314
5012025-02-052025-04-305
5022025-02-192025-05-105
5032025-03-062025-05-115
5042025-03-262025-05-125
5052025-04-102025-05-135
6012025-02-062025-05-056
6022025-04-012025-05-256
8012025-02-072025-05-228
8022025-02-202025-05-238
8032025-03-072025-05-248
8042025-04-022025-05-258

Table notifications

columntypeconstraints
notification_idINTPRIMARY KEY
account_idINT
created_atTIMESTAMPNOT NULL
read_atTIMESTAMP
notification_typeVARCHAR(30)NOT NULL

Sample data:

account_idcreated_atnotification_idnotification_typeread_at
2012025-05-20 09:00:001promo2025-05-20 10:00:00
2022025-05-31 08:00:002securityNULL
3012025-05-10 12:00:003system2025-05-10 12:05:00
3022025-05-29 15:00:004promo2025-05-29 16:00:00
3032025-05-30 11:00:005promo2025-05-30 11:10:00
5052025-05-13 09:30:006systemNULL
6012025-05-01 07:00:007promo2025-05-01 07:05:00
6022025-05-25 18:00:008security2025-05-26 08:00:00
8042025-05-25 10:00:009promoNULL
NULL2025-05-28 10:00:0010systemNULL

Expected output

One row with the number of multi-account users, how many of them have at least one unread notification, and the percentage (0-100) rounded to 2 decimals.

pct_with_unreadtotal_multi_account_usersusers_with_unread
5063

Hints

  1. Build the denominator first: users with at least 2 accounts.
  2. For the numerator, count distinct users who have at least one notification where read_at is NULL.