MySQL Forums
Forum List  »  Newbie

3 Days Rolling Avg
Posted by: Visweswara Rao
Date: October 25, 2022 04:46AM

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

Options: ReplyQuote


Subject
Written By
Posted
3 Days Rolling Avg
October 25, 2022 04:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.