Calculate DAU/MAU ratio per region

Question

Write a query to compute DAU/MAU ratio grouped by region, handling edge cases like nulls and duplicates.

Codemia · Accepted Answer

To calculate the Daily Active Users (DAU) and Monthly Active Users (MAU) ratio per region, we need to work with a dataset that includes user activity records. This dataset typically contains the following fields:
- `user_id`: Unique identifier for users
- `activity_date`: Date when the activity occurred
- `region`: Geographical region of the user

The final query should return the DAU/MAU ratio for each region, ensuring that we accurately count distinct users for each time period while handling potential data quality issues such as nulls and duplicates.

1. **Identify DAU**: Use a Common Table Expression (CTE) to calculate distinct users per day (`activity_date`) grouped by `region`.
2. **Identify MAU**: Create another CTE to calculate distinct users per month, similarly grouped by `region`.
3. **Join DAU and MAU**: Join the two CTEs on `region` to combine the daily and monthly user counts.
4. **Calculate Ratio**: Compute the DAU/MAU ratio for each region, ensuring to handle cases where MAU might be zero to avoid division errors.

```sql
WITH daily_active_users AS (
    SELECT
        region,
        COUNT(DISTINCT user_id) AS daily_users,
        activity_date
    FROM
        user_activity
    GROUP BY
        region, activity_date
),
monthly_active_users AS (
    SELECT
        region,
        COUNT(DISTINCT user_id) AS monthly_users,
        DATE_TRUNC('month', activity_date) AS activity_month
    FROM
        user_activity
    GROUP BY
        region, DATE_TRUNC('month', activity_date)
)
SELECT
    d.region,
    SUM(d.daily_users) AS total_dau,
    SUM(m.monthly_users) AS total_mau,
    CASE
        WHEN SUM(m.monthly_users) = 0 THEN 0
        ELSE SUM(d.daily_users) * 1.0 / SUM(m.monthly_users)
    END AS dau_mau_ratio
FROM
    daily_active_users d
LEFT JOIN
    monthly_active_users m ON d.region = m.region
GROUP BY
    d.region;
```

To optimize this query for large datasets:
- **Indexing**: Ensure that there are indexes on `user_id`, `activity_date`, and `region` in the `user_activity` table to speed up the grouping and counting operations.
- **Materialized Views**: Consider using materialized views for the daily and monthly active users if the data does not change frequently, allowing for quicker access to pre-computed results.
- **Batch Processing**: If real-time processing is not required, aggregate the data in batch jobs at regular intervals (e.g., nightly) to improve performance during query execution.

1. **NULL Values**: Ensure that `user_id`, `activity_date`, and `region` are not null. If they are, we should exclude those records from the counts.
2. **Duplicates**: If a user can have multiple activities in a day or month, using `COUNT(DISTINCT user_id)` will handle duplicates effectively.
3. **Zero Users**: Handle cases where MAU is zero to prevent division by zero errors in the ratio calculation. The query already addresses this with a CASE statement.

Calculate DAU/MAU ratio per region

Figma

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Figma Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions