Katkat
Back

Write SQL for multi-account metrics

MetaData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question tests your ability to perform complex aggregations across multiple tables, specifically using conditional logic and joins to compute user engagement metrics.

Problem Statement

You are given two tables representing user accounts and their notification activity.

Tables

accounts

ColumnType
account_idint
regionvarchar
is_activeboolean

notifications

ColumnType
notification_idint
account_idint
statusvarchar (e.g., 'sent', 'delivered', 'opened')
timestampdatetime

Task

Write a SQL query to return the following metrics per region for all active accounts:

  1. Total number of notifications sent.
  2. The 'open rate', defined as the number of notifications with status 'opened' divided by the number of notifications 'delivered'.

Only include regions that have at least 100 total notifications sent. If a region has no notifications delivered, the open rate should be treated as 0.

Examples

Input: accounts: (1, 'NA', true), (2, 'NA', true), (3, 'EU', true) notifications: (101, 1, 'sent', ...), (102, 1, 'delivered', ...), (103, 1, 'opened', ...)

Output:

regiontotal_sentopen_rate
NA30.5

Constraints

  • Use account_id to join tables.
  • Ensure is_active is true.
  • Handle division by zero for the open rate calculation.

Hints

Hint 1

Start by joining the two tables on `account_id` while filtering for `is_active = true`.

Hint 2

Use conditional aggregation (e.g., CASE statements inside SUM) to count specific notification statuses.

Hint 3

To handle the open rate division by zero, use `NULLIF(count_delivered, 0)` or a CASE statement.

Follow-up questions

  • How would you modify this query if you needed to calculate the rolling 7-day average of notifications sent per region?
  • What would be the performance impact of adding an index on `account_id` versus `region` in the `accounts` table?
  • If the `notifications` table contained millions of rows daily, how would you optimize the computation of these metrics?