Join impressions and messages to find DAU/MAU ratio

Question

Write a query joining impressions and transactions to produce the combined DAU/MAU ratio.

Codemia · Accepted Answer

The problem involves two primary datasets: impressions and transactions. The goal is to calculate the Daily Active Users (DAU) and Monthly Active Users (MAU) ratio based on these datasets.

- **Impressions Table**: Contains records of user impressions, with columns such as `user_id`, `impression_date`, and possibly other metadata. 
- **Transactions Table**: Contains records of user transactions, with similar columns including `user_id`, `transaction_date`, and transaction details.

The query needs to join these tables on `user_id`, aggregate the data to get unique user counts for each day (DAU) and for each month (MAU), and finally produce the ratio of DAU to MAU.

1. **Identify Key Dates**: Determine the date range for which DAU and MAU should be calculated, typically the last 30 days. 
2. **CTEs for User Counts**: Create Common Table Expressions (CTEs) to calculate DAU and MAU. 
   - First CTE (`daily_active_users`): Count distinct `user_id` grouped by date from the impressions table. 
   - Second CTE (`monthly_active_users`): Count distinct `user_id` grouped by month from the impressions table. 
3. **Join the CTEs**: Join the two CTEs on the respective date fields to calculate the ratio. 
4. **Final Selection**: Select the ratio of DAU to MAU, ensuring proper handling of cases where MAU might be zero to avoid division errors.

```sql
WITH daily_active_users AS (
    SELECT
        impression_date,
        COUNT(DISTINCT user_id) AS dau
    FROM
        impressions
    WHERE
        impression_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY
        impression_date
),
monthly_active_users AS (
    SELECT
        DATE_TRUNC('month', impression_date) AS month,
        COUNT(DISTINCT user_id) AS mau
    FROM
        impressions
    WHERE
        impression_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY
        month
)
SELECT
    d.impression_date,
    d.dau,
    m.mau,
    CASE WHEN m.mau = 0 THEN 0 ELSE d.dau::float / m.mau END AS dau_mau_ratio
FROM
    daily_active_users d
JOIN
    monthly_active_users m ON DATE_TRUNC('month', d.impression_date) = m.month;
```

To optimize this query for large datasets, consider the following strategies: 
- **Indexing**: Ensure that the `impression_date` and `user_id` columns in the impressions table are indexed to speed up the filtering and aggregation. 
- **Date Filtering**: Filter the impressions as early as possible to minimize the data processed in the CTEs. 
- **Materialized Views**: If this query is run frequently, consider using materialized views to pre-aggregate DAU and MAU data, refreshing them at regular intervals to improve performance.

Specific edge cases to consider include: 
- **NULL Values**: Ensure that `user_id` is never NULL in both tables, as this would lead to incorrect counts. Add conditions to filter out NULLs if necessary. 
- **Duplicates**: Check for duplicates in the impressions and transactions tables. If duplicates exist, they should either be deduplicated or handled carefully in the aggregation logic to ensure accurate counts. 
- **Zero Activity**: Handle scenarios where there are days with no impressions or no transactions, which would affect the DAU and MAU calculations. The division should handle cases where MAU is zero to avoid division by zero errors.

Join impressions and messages to find DAU/MAU ratio

Salesforce

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

Salesforce 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