Blog

Introduction

• In this article we will solve Tweets’ Rolling Averages SQL question which is asked by Twitter as per DataLemur website.

If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.

Question

• We have been given tweets that were made over given period of time.
• We need to calculate 3-day rolling average of tweets published by each user for each date that a tweet was posted.

• As we can see in our output for user_id, 2 tweets were made so avg is 2, then on next day 1 tweet was made, so total 3 tweets made in 2 days hence avg is 3/2 = 1.5, then on third day 1 more tweet made so total 4 tweets in 3 days makes it 1.33 as avg.

Solution

• We will first count number of tweets made by each user on each date using group by and count operation as shown below.

• Once we have our subquery ready, we can use windowing operation on user_id partition. We will use avg aggregation over total_tweets .

• In Window operation while defining window logic we can choose frame to consider while performing aggregation.
• For example here our frame is 2 preceding rows and current row , that makes 3 rolling days.
• So once we define window we can perform average on it and get the result as rolling average, but we also have to round it up to 2 decimal places, since it was asked in the question.

Final Solution

• Here is the final solution

select user_id, tweet_date ,
round(avg(total_tweets) over(partition by user_id order by tweet_date asc 
rows between 2 preceding and current row),2) as rolling_avg_3d
from (
select user_id, tweet_date, count(*) total_tweets 
from tweets group by user_id, tweet_date order by tweet_date asc
) x

Submission

• Our solution is accepted by the platform.

Conclusion

• In this article we solved SQL question asked by Twitter. This sql question require us the knowledge of windowing, along with using frame to compute various metrics and subquery to solve it.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course