Calculate DAU/MAU ratio per product

Question

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

Codemia · Accepted Answer

The data involved consists of user activity logs that detail user engagement with various products over time. Each log entry includes a user ID, product ID, and a timestamp indicating when the user interacted with the product. The goal is to compute the Daily Active Users (DAU) and Monthly Active Users (MAU) ratios for each product.

- DAU is defined as the number of distinct users who interacted with a product on a given day. 
- MAU is defined as the number of distinct users who interacted with a product in a given month.

The query must handle edge cases such as duplicate entries (where a user may have multiple logs for the same product on the same day) and NULL values in the user or product IDs.

To solve the problem, we will follow these steps:
1. **Extract distinct active users for each product**: Use a Common Table Expression (CTE) to derive distinct user counts for each day and month for each product.
2. **Calculate DAU and MAU**: In another CTE, compute DAU and MAU for each product by aggregating the distinct user counts.
3. **Calculate DAU/MAU Ratio**: Finally, compute the DAU/MAU ratio for each product while handling potential division by zero. 
4. **Handle duplicates and NULLs**: Ensure that we only consider non-null user and product IDs and that duplicates are removed in the initial extraction.

Here is the SQL query that implements the above approach:
```sql
WITH DistinctUsers AS (
    SELECT
        product_id,
        DATE(timestamp) AS activity_date,
        user_id
    FROM
        user_activity_logs
    WHERE
        user_id IS NOT NULL AND product_id IS NOT NULL
    GROUP BY
        product_id, activity_date, user_id
),
DailyActiveUsers AS (
    SELECT
        product_id,
        activity_date,
        COUNT(DISTINCT user_id) AS dau
    FROM
        DistinctUsers
    GROUP BY
        product_id, activity_date
),
MonthlyActiveUsers AS (
    SELECT
        product_id,
        DATE_TRUNC('month', activity_date) AS activity_month,
        COUNT(DISTINCT user_id) AS mau
    FROM
        DistinctUsers
    GROUP BY
        product_id, activity_month
)
SELECT
    d.product_id,
    SUM(d.dau) AS total_dau,
    COALESCE(MAX(m.mau), 1) AS total_mau,  -- Avoid division by zero
    SUM(d.dau) / COALESCE(MAX(m.mau), 1) AS dau_mau_ratio
FROM
    DailyActiveUsers d
LEFT JOIN
    MonthlyActiveUsers m ON d.product_id = m.product_id
GROUP BY
    d.product_id;
```

To optimize this query for large datasets, consider the following strategies:
- **Indexing**: Create indexes on `user_id`, `product_id`, and `timestamp` in the `user_activity_logs` table to speed up the GROUP BY and WHERE clause operations.
- **Partitioning**: If the dataset is particularly large, consider partitioning the table by date or product ID to improve query performance.
- **Materialized Views**: If this computation needs to be performed frequently, consider creating a materialized view that pre-aggregates DAU and MAU data, which can be refreshed periodically.

Specific edge cases to consider include:
- **NULL values**: Ensure that any NULL `user_id` or `product_id` entries are filtered out before aggregation to avoid inaccurate counts.
- **Duplicates**: The initial extraction step removes duplicates where a user interacts with the same product on the same day. Without this step, DAU and MAU calculations could be inflated.
- **Zero Users**: If there are no active users for a product in a month, ensure that the MAU value defaults to 1 in the final calculation to prevent division by zero errors.

Calculate DAU/MAU ratio per product

xAI

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

xAI 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