3 Days Rolling Avg

[Visweswara Rao]

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:


Tables sample Data:

Users Table:


ID Name User_tpe
1 Matt user
2 John user
3 Louis admin

Traffic Tabke:

user_id visited_on time_spent
1 2019-05-01 15
2 2019-05-02 20
2 2019-05-03 10




My output Expected Output
2015-05-01 30.0000 2015-05-01 30.0000
2015-05-02 70.0000 2015-05-02 50.0000
2015-05-03 40.0000 2015-05-03 46.6667
2015-05-04 80.0000 2015-05-04 63.3333
2015-05-05 110.0000 2015-05-05 76.6667
2015-05-06 60.0000 2015-05-06 83.3333
2015-05-07 19.0000 2015-05-07 63.0000
2015-05-08 30.0000 2015-05-08 36.3333
2015-05-09 67.0000 2015-05-09 38.6667
2015-05-10 11.0000 2015-05-10 36.0000
2015-05-11 7.0000 2015-05-11 28.3333
2015-05-12 16.0000 2015-05-12 11.3333
2015-05-13 32.0000 2015-05-13 18.3333
2015-05-14 100.0000 2015-05-14 49.3333
2015-05-15 40.0000 2015-05-15 57.3333