Напредни извештаи од базата (SQL и складирани процедури)

Прикажување на најдискутираните филмови за секоја година

select year,title, (num_discussions + num_replies) as total from  (
							
					select godini.year,m.title , count(d1.discussion_id) as num_discussions, count(r.reply_id)  as num_replies
								from movies m
								left join discussions d1 on d1.movie_id  = m.movie_id 
								left join replies r on r.discussion_id  = d1.discussion_id
								  	 join(

 
								select distinct extract(year from d.date) as year
								from discussions d
								) as godini on godini.year = extract(year from d1.date) or
								extract(year from r.date) = godini.year 
								
								group by m.title, godini.year
								
  							) as sum_by_year		
where (num_discussions + num_replies) = (
 	
 			select  max(num_discussions + num_replies) from (
 							select godini.year1,m.title as title1 , count(d1.discussion_id) as num_discussions, count(r.reply_id)  as                                                                                                                 
                                                                                                                                                   num_replies

								from movies m
								left join discussions d1 on d1.movie_id  = m.movie_id 
								left join replies r on r.discussion_id  = d1.discussion_id
								  	 join(

 
								select distinct extract(year from d.date) as year1
								from discussions d
								) as godini on godini.year1 = extract(year from d1.date) or
								extract(year from r.date) = godini.year1
								
								group by m.title, godini.year1
								
  							) as diskusii_repliki 
						where diskusii_repliki.year1 = year
  							group by diskusii_repliki.year1
 	);
 	

Предложување на филмови на корисник врз база на корисниците со кои стапил во интеракција

 select distinct m.title 
 from replies r 
 join discussions d on r.discussion_id = d.discussion_id
 join replies r2 on r2.discussion_id  = d.discussion_id  and r2.user_id  != 1
 join users u  on u.user_id  = r2.user_id 
 join movie_likes ml on ml.user_id = u.user_id 
 join movies m  on m.movie_id = ml.movie_id
 where  r.user_id = 1;

• при реализација на прашалникот, наместо 1, ќе биде искористено соодветниот идентификатор на најавениот корисник

Прикажување на бројот на филмови за секој жанр во секоја година, сортирани според бројот на филмови.

 select g.genre_type, extract(year from m.airing_date), count(m.movie_id) as movie_count from genres as g
 join movie_genres as mg on mg.genre_id = g.genre_id
 join movies as m on m.movie_id = mg.movie_id
 group by g.genre_type, extract(year from m.airing_date)
 order by movie_count desc

Прикажување на бројот на дискусии и реплики за сите корисници кои се во базата, сортирани според бројот на реплики и дискусии.

 select u.user_id, u.name, u.surname, count(d.discussion_id) as discussion_count, count (r.reply_id) as reply_count from users u
 left join discussions as d on d.user_id = u.user_id
 left join replies as r on r.user_id = u.user_id
 group by u.user_id, u.name, u.surname
 order by count(d.discussion_id) +  count (r.reply_id) desc