how to get 3 days moving average in my sql
-
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:
-
- If you're using MySQL the syntax and functionality are probably a bit different than MSSQL-2019 (the forum you posted in).
- When posting this type of question, others are more likely to help if you provide table create scripts and sample data that can be Copy-Pasted into their work screens.
- Putting CODE tags around your code makes it more readable.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply