how to get 3 days moving average in sql

Question

I have 2 tables: users, traffic.

The first table consists of the users information (id, name, user_type) .

The second table consists of the time of each visit to the website:(user_id, visited_on, time_spent).

Trying to write a query to show the 3 day moving average of time spent on the website for users.user_type='user'. Also, avg_time_spent must have 4 decimal digits and rounded off.

I am stuck here and trying to figure out:

Select t.visited_on, avg(time_spent)over(

partition by user_id order by visited_on

range between interval 2 day PRECEDING AND CURRENT ROW

) as avg_time_spent

from traffic t join users u on u.id=t.user_id

where u.user_type='user';

Could any one please help what I am missing here

My expected and actual out put is like below:

Answer 1

Hi @Learner
How about using Rows between ..., like this:

Select t.visited_on  
      ,avg(time_spent)over(partition by user_id order by visited_on Rows Between 2 PRECEDING AND CURRENT ROW) as avg_time_spent  
from traffic t join users u on u.id=t.user_id  
where u.user_type='user';  

For more details, refer to this doc: SELECT - OVER Clause (ROWS or RANGE)
Also, see this blog for some more examples: What a Moving Average Is and How to Compute it in SQL

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.