-3

What is the genre-wise running total and moving average of the average movie duration? -- (Note: You need to show the output table in the question.)

/* Output format: -

genre avg_duration running_avg_duration moving_avg_duration
comdy 145 102.44 134
. . . .
. . . .

Genre table: - movie_id, genre

Movie table: - id, title, year, date_published, duration, country, worldwide_gross_income, languages, production_company

Vinod Prajapati
  • 1
  • 2
  • 1
    can you share your coding attempt at this problem and the content of the input tables? – lemon May 18 '22 at 15:53
  • I prefer an "exponential moving average". With that one needs to remember only one value from one row to the next. Also, any kind of moving average is clumsy to compute in SQL; I recommend doing it in your app code. – Rick James May 23 '22 at 05:31

1 Answers1

0
SELECT genre,
    ROUND(AVG(duration),2) AS avg_duration,
    SUM(ROUND(AVG(duration),2)) OVER(ORDER BY genre ROWS UNBOUNDED 
    PRECEDING) AS running_total_duration,
    AVG(ROUND(AVG(duration),2)) OVER(ORDER BY genre ROWS 10 PRECEDING) 
    AS moving_avg_duration
FROM movie AS m 
INNER JOIN genre AS g 
ON m.id= g.movie_id
GROUP BY genre
ORDER BY genre;
Vinod Prajapati
  • 1
  • 2