how to get 3 days moving average in my sql

• Krish319

  Old Hand

  Points: 352

  More actions

  October 21, 2022 at 5:39 pm

  #4102638

  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:

  

   

   

• homebrew01

  SSC Guru

  Points: 55549

  More actions

  October 21, 2022 at 5:44 pm

  #4102640

  1. If you're using MySQL the syntax and functionality are probably a bit different than MSSQL-2019 (the forum you posted in).
  2. 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.
  3. Putting CODE tags around your code makes it more readable.

  • This reply was modified 2 years, 7 months ago by homebrew01.
  • This reply was modified 2 years, 7 months ago by homebrew01.