Join sessions and sessions to find DAU/MAU ratio

Question

Write a query joining sessions and rides to produce the combined DAU/MAU ratio.

Codemia · Accepted Answer

The task involves calculating the Daily Active Users (DAU) and Monthly Active Users (MAU) ratios based on data from two tables: `sessions` and `rides`. The `sessions` table contains user session data, including timestamps, while the `rides` table includes information about rides taken by users. Our goal is to join these tables to compute the number of unique users for both DAU and MAU, and then derive the ratio of DAU to MAU.

1. **Identify Unique Users**: First, we will extract unique users from both `sessions` and `rides` tables.  
2. **Calculate DAU**: We will group the `sessions` data by day to count unique users per day.  
3. **Calculate MAU**: Similarly, we will group the `sessions` data by month to count unique users per month.  
4. **Join Results**: We will then join the DAU and MAU results on a common timeframe (day/month) to facilitate the calculation of the DAU/MAU ratio.  
5. **Final Calculation**: Finally, we will compute the ratio of DAU to MAU for the most recent month.

```sql
WITH DailyActiveUsers AS (
    SELECT 
        DATE(session_timestamp) AS session_date, 
        COUNT(DISTINCT user_id) AS dau
    FROM sessions
    GROUP BY session_date
),
MonthlyActiveUsers AS (
    SELECT 
        DATE_TRUNC('month', session_timestamp) AS month, 
        COUNT(DISTINCT user_id) AS mau
    FROM sessions
    GROUP BY month
)
SELECT 
    d.session_date, 
    d.dau, 
    m.mau, 
    COALESCE(d.dau::FLOAT / NULLIF(m.mau, 0), 0) AS dau_mau_ratio
FROM DailyActiveUsers d
JOIN MonthlyActiveUsers m ON DATE_TRUNC('month', d.session_date) = m.month
ORDER BY d.session_date DESC;
```

To optimize this query for large datasets, the following strategies can be employed:  
1. **Indexing**: Ensure that there are indexes on `session_timestamp` and `user_id` in the `sessions` table to speed up the aggregation process.  
2. **Partitioning**: If the dataset is extremely large, consider partitioning the `sessions` table by date to improve performance on temporal queries.  
3. **Materialized Views**: For frequently run queries, consider creating materialized views for DAU and MAU to avoid recalculating these metrics from scratch every time.

Several edge cases must be considered:  
1. **NULL Values**: Handle any NULL values in `user_id` or `session_timestamp` by filtering them out in the initial SELECT statements.  
2. **Duplicates**: Ensure that the counting of unique users uses `COUNT(DISTINCT user_id)` to avoid overcounting.  
3. **Time Zone Issues**: If session timestamps could be in different time zones, ensure they are converted to a consistent timezone before processing.  
4. **Months with No Activity**: If there are months with no activity, the ratio could result in division by zero. This is handled using `NULLIF` in the SQL query.

Join sessions and sessions to find DAU/MAU ratio

Plaid

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

Plaid 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