Join messages and events to find rolling 7-day average

Question

Write a query joining messages and orders to produce the combined rolling 7-day average.

Codemia · Accepted Answer

In this problem, we are working with two main data tables: **messages** and **orders**. The **messages** table contains records of messages sent, including a timestamp and a message ID. The **orders** table includes information about orders placed, with each order also having a timestamp and an associated order ID. Our goal is to join these two tables to calculate a rolling 7-day average of the number of messages and orders per day. This requires careful date manipulation and aggregation to ensure we are accurately reflecting the average over the specified time period.

To tackle this problem, we will follow a series of steps:
1. **Identify the Relevant Columns**: We need the timestamp and counts from both the messages and orders tables.
2. **Join the Tables**: Use a LEFT JOIN to combine the messages and orders based on a common date dimension.
3. **Calculate Daily Counts**: Aggregate the counts of messages and orders by day using GROUP BY.
4. **Calculate the Rolling Average**: Utilize a window function to compute the rolling 7-day average for both messages and orders.
5. **Handle NULL Values**: Ensure that we handle any NULLs that may arise from the LEFT JOIN appropriately using COALESCE.

Here is the SQL query that implements the above approach:
```sql
WITH daily_counts AS (
    SELECT
        DATE(m.timestamp) AS message_date,
        COUNT(m.message_id) AS total_messages,
        COUNT(o.order_id) AS total_orders
    FROM messages m
    LEFT JOIN orders o ON DATE(m.timestamp) = DATE(o.timestamp)
    GROUP BY message_date
),
rolling_average AS (
    SELECT
        message_date,
        total_messages,
        total_orders,
        AVG(total_messages) OVER (ORDER BY message_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_messages,
        AVG(total_orders) OVER (ORDER BY message_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_orders
    FROM daily_counts
)
SELECT * FROM rolling_average;
```

For optimizing this query for large datasets, we can consider the following strategies:
1. **Indexing**: Ensure that there are indexes on the timestamp columns of both the messages and orders tables. This will speed up the JOIN operation significantly.
2. **Partitioning**: If possible, partition the data based on date ranges, which can help in quickly accessing relevant data for the rolling average calculations.
3. **Materialized Views**: If this query is run frequently, creating a materialized view for the daily_counts can drastically improve performance since it pre-computes and stores the daily aggregates.

It's essential to consider several edge cases in this query:
1. **NULL Values**: Messages or orders may not exist for certain dates. The use of COALESCE ensures that our rolling averages do not return NULL, and instead provide meaningful data.
2. **Duplicates**: If there are duplicate entries in either the messages or orders tables, we need to ensure that our COUNT functions accurately reflect unique entries. Using COUNT(DISTINCT ...) may be necessary based on data integrity.
3. **Gaps in Data**: If there are days without messages or orders, the rolling average might be skewed. Our approach should handle these gaps gracefully by maintaining consistent output even when data is sparse.

Join messages and events to find rolling 7-day average

SentinelOne

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

SentinelOne 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