Напредни извештаи од базата (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, ќе биде искористено соодветниот идентификатор на најавениот корисник