Back to questions

Tweets' Rolling Averages [Twitter SQL Interview Question]

Medium

This is the same question as problem #10 in the SQL Chapter of Ace the Data Science Interview!

Given a table of tweet data over a specified time period, calculate the 3-day rolling average of tweets for each user. Output the user ID, tweet date, and rolling averages rounded to 2 decimal places.

Notes:

• A rolling average, also known as a moving average or running mean is a time-series technique that examines trends in data over a specified period of time.
• In this case, we want to determine how the tweet count for each user changes over a 3-day period.

Effective April 7th, 2023, the problem statement, solution and hints for this question have been revised.

Table:

Column NameType
user_idinteger
tweet_datetimestamp
tweet_countinteger

Example Input:

user_idtweet_datetweet_count
11106/01/2022 00:00:002
11106/02/2022 00:00:001
11106/03/2022 00:00:003
11106/04/2022 00:00:004
11106/05/2022 00:00:005

Example Output:

user_idtweet_daterolling_avg_3d
11106/01/2022 00:00:002.00
11106/02/2022 00:00:001.50
11106/03/2022 00:00:002.00
11106/04/2022 00:00:002.67
11106/05/2022 00:00:004.00

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14