Calculate DAU/MAU ratio per user

Question

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

Codemia · Accepted Answer

In this problem, we need to compute the Daily Active Users (DAU) and Monthly Active Users (MAU) ratios for each user in the given dataset. The data involved includes user activity logs, which typically contain user IDs and timestamps of their logins or actions. The goal is to calculate how many unique users were active on a daily basis (DAU) and how many were active in a month (MAU), then derive the ratio of DAU to MAU for each user. Handling edge cases such as NULL values and duplicates is crucial to ensure accurate calculations.

1. **Identify the Data**: Assume we have a table named `user_activity` with columns `user_id` (VARCHAR) and `activity_date` (DATE).
2. **Calculate DAU**: Use a `COUNT(DISTINCT activity_date)` grouped by `user_id` to get the number of unique days each user was active.
3. **Calculate MAU**: Similarly, use a `COUNT(DISTINCT activity_date)` for a monthly period (e.g., the last month) grouped by `user_id` to compute the number of unique months each user was active.
4. **Compute Ratio**: Finally, take the ratio of DAU to MAU, ensuring to use `COALESCE` to handle cases where MAU could be 0, to avoid division by zero errors.

```sql
WITH DAU_CTE AS (
    SELECT user_id, COUNT(DISTINCT activity_date) AS dau
    FROM user_activity
    WHERE activity_date >= CURRENT_DATE - INTERVAL '1 day'
    GROUP BY user_id
),
MAU_CTE AS (
    SELECT user_id, COUNT(DISTINCT DATE_TRUNC('month', activity_date)) AS mau
    FROM user_activity
    WHERE activity_date >= CURRENT_DATE - INTERVAL '1 month'
    GROUP BY user_id
)
SELECT dau.user_id,
       COALESCE(dau.dau, 0) AS daily_active_users,
       COALESCE(mau.mau, 0) AS monthly_active_users,
       CASE WHEN COALESCE(mau.mau, 0) = 0 THEN 0 ELSE COALESCE(dau.dau, 0) * 1.0 / mau.mau END AS dau_mau_ratio
FROM DAU_CTE dau
FULL OUTER JOIN MAU_CTE mau ON dau.user_id = mau.user_id;
```

To optimize this query for large datasets, consider the following:
1. **Indexes**: Ensure that there are indexes on `user_id` and `activity_date` columns to speed up the `GROUP BY` and `WHERE` clause operations.
2. **Partitioning**: If the dataset is exceptionally large, partitioning the `user_activity` table by `activity_date` can enhance performance by reducing the amount of scanned data.
3. **Materialized Views**: For recurring queries, consider using a materialized view to pre-compute DAU and MAU values, refreshing the view at defined intervals to reduce query execution time.

1. **NULL Values**: If any `user_id` or `activity_date` are NULL, they should be excluded from the calculations. The `WHERE` clauses in both CTEs inherently handle this.
2. **Duplicates**: If a user has multiple records for the same day, the use of `COUNT(DISTINCT activity_date)` ensures that each user is only counted once per day, mitigating the impact of duplicates.
3. **No Activity**: Users who have zero activity in the considered timeframe should return a ratio of 0, which is handled by the `COALESCE` function in the final selection.

Calculate DAU/MAU ratio per user

Rippling

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

Rippling 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