Write a query to find DAU/MAU ratio from transactions

Question

Write a SQL query to calculate DAU/MAU ratio from the transactions table, considering nulls and duplicates.

Codemia · Accepted Answer

To calculate the Daily Active Users (DAU) and Monthly Active Users (MAU) ratio from the transactions table, we need to understand the data structure of the transactions table. Typically, the table should have at least the following columns: 
1. `user_id`: Unique identifier for each user. 
2. `transaction_date`: The date when the transaction occurred.

DAU is defined as the number of unique users who made transactions on a given day, while MAU is the number of unique users who made transactions within a given month. The goal is to compute the ratio of DAU to MAU for a specified period.

1. **Extract Unique Daily Users (DAU)**: Use a CTE (Common Table Expression) to count unique users per day. 
2. **Extract Unique Monthly Users (MAU)**: Create another CTE to count unique users per month. 
3. **Calculate the Ratio**: Join the two CTEs on the date, ensuring to handle cases where DAU or MAU might be zero to avoid division by zero errors. 
4. **Handle NULLs and Duplicates**: Ensure that the query accounts for NULL values and duplicate transactions by using the `DISTINCT` keyword.

```sql
WITH daily_active_users AS (
    SELECT 
        transaction_date,
        COUNT(DISTINCT user_id) AS dau
    FROM transactions
    GROUP BY transaction_date
),
monthly_active_users AS (
    SELECT 
        DATE_TRUNC('month', transaction_date) AS month,
        COUNT(DISTINCT user_id) AS mau
    FROM transactions
    GROUP BY month
)
SELECT 
    d.transaction_date,
    d.dau,
    COALESCE(m.mau, 0) AS mau,
    CASE WHEN COALESCE(m.mau, 0) = 0 THEN 0 ELSE CAST(d.dau AS FLOAT) / m.mau END AS dau_mau_ratio
FROM daily_active_users d
LEFT JOIN monthly_active_users m ON DATE_TRUNC('month', d.transaction_date) = m.month
ORDER BY d.transaction_date;```

For large datasets, consider the following optimization strategies: 
1. **Indexing**: Create indexes on `transaction_date` and `user_id` to speed up the filtering and counting operations. 
2. **Partitioning**: If the dataset is extremely large, partition the transactions table by date to optimize query performance. 
3. **Materialized Views**: If this calculation is frequently needed, consider using materialized views for daily and monthly active users to pre-compute results and save on computation time during query execution.

1. **NULL Values**: Ensure that the query accounts for NULL user IDs by checking for them explicitly if there's any chance of their presence in the dataset. 
2. **Duplicates**: The use of `COUNT(DISTINCT user_id)` helps in eliminating duplicates within the same day or month. 
3. **Zero Users**: Handle cases where either DAU or MAU is zero to prevent division by zero errors in the final ratio calculation. 
4. **Date Range**: Consider scenarios where there may be no transactions on certain days or months, which should reflect in the output with appropriate zeroes in the ratio.

Write a query to find DAU/MAU ratio from transactions

Snowflake

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

Snowflake 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