October 21, 2022 at 5:39 pm
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:
October 21, 2022 at 5:44 pm
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy