search

cop5725 / Movie-Predictor-9000

0 stars 3 forks source link

Create Genre Monthly Average Page #15

Open ltcarbonell opened 5 years ago

ltcarbonell commented 5 years ago

This ticket encompass the creation of the genre monthly average performance page according to specification. This includes a search for a particular genre(s) and a period of time. The search will result in a graph of the Revenue in millions per month for these genres

Keep in mind that this does not include backend api changes as those will be handled in the linked ticket (#16 ), and only includes the front end changes to support the database calls

image
davidallen1 commented 5 years ago

This query will return the average revenue for movies of a certain GENRE over a specific number of years. To test the query, replace the <FROM YEAR, <TO YEAR, and <GENRE text with proper years and genre names. Please note that the provided 

SELECT CAST(AVG(Movie.REVENUE) AS NUMBER (12,0) ) AS Revenue, to_char(ReleaseDate, 'MONTH') AS Month FROM 
((LTCARBON.MOVIE JOIN LTCARBON.MOVIEGENRE 
    ON LTCARBON.MOVIE.MOVIEID=LTCARBON.MOVIEGENRE.MOVIEID) 
    JOIN LTCARBON.GENRE ON LTCARBON.GENRE.GENREID=LTCARBON.MOVIEGENRE.GENREID)
WHERE RELEASEDATE IS NOT NULL 
   AND to_char(ReleaseDate, 'yyyy')>= <FROM YEAR>
   AND to_char(ReleaseDate, 'yyyy') <= <TO YEAR>
   AND LTCARBON.GENRE.Name = '<GENRE>'
   AND Movie.REVENUE <> 0
GROUP BY to_char(ReleaseDate, 'MONTH'),to_char(ReleaseDate, 'mm') ORDER BY to_char(ReleaseDate, 'mm');