Tweets Rolling Average
ASKED IN INTERVIEWTwitter
Problem Statement
Twitter wants to monitor user engagement by looking at their tweet frequency. Specifically, you need to calculate the 3-day rolling average of tweets for each user.
Write an SQL query to calculate the 3-day rolling average of tweets for each user_id and tweet_date.
Rules:
- The rolling average for a given day is the average of tweets on that day and the previous two days (3 days total).
- If a user has only one or two days of history, the average should be calculated based on the available data.
- Output the user_id, tweet_date, and the rolling_avg rounded to 2 decimal places.
- Results must be sorted by user_id in ascending order, then by tweet_date in ascending order.
Tests your understanding of
Window Functions, Rolling Average, Time Series and Analytics
Input Tables
tweets
| user_id(INTEGER) | tweet_date(DATE) | tweet_count(INTEGER) |
|---|---|---|
| 1 | 2022-06-01 | 2 |
| 1 | 2022-06-02 | 1 |
| 1 | 2022-06-03 | 3 |
| 1 | 2022-06-04 | 4 |
| 1 | 2022-06-05 | 5 |
| 2 | 2022-06-01 | 10 |
| 2 | 2022-06-02 | 5 |
| 2 | 2022-06-03 | 0 |
| 2 | 2022-06-04 | 10 |
| 2 | 2022-06-05 | 2 |
| 3 | 2022-06-01 | 1 |
| 3 | 2022-06-02 | 1 |
| 3 | 2022-06-03 | 7 |
| 4 | 2022-06-01 | 2 |
| 4 | 2022-06-02 | 4 |
Expected Output
| user_id(INTEGER) | tweet_date(DATE) | rolling_avg(DECIMAL) |
|---|---|---|
| 1 | 2022-06-01 | 2 |
| 1 | 2022-06-02 | 1.5 |
| 1 | 2022-06-03 | 2 |
| 1 | 2022-06-04 | 2.67 |
| 1 | 2022-06-05 | 4 |
| 2 | 2022-06-01 | 10 |
| 2 | 2022-06-02 | 7.5 |
| 2 | 2022-06-03 | 5 |
| 2 | 2022-06-04 | 5 |
| 2 | 2022-06-05 | 4 |
| 3 | 2022-06-01 | 1 |
| 3 | 2022-06-02 | 1 |
| 3 | 2022-06-03 | 3 |
| 4 | 2022-06-01 | 2 |
| 4 | 2022-06-02 | 3 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRolling AverageTime SeriesAnalytics
15-20 min
52%