Напредни извештаи од базата (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
 	);
 	

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

select title from(
	 select  m.title, sum(mv.stars_rated) as total
	 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 and 
 	 
 	 		(
 	 			select count(r3.reply_id)
 	 			from replies r3 
 	 			group by u.user_id 
 	 		) >= 
 	 		(
 	 			
 	 			select count(*) / (select count(*) from users)
 	 			from replies 
 	 		)
 	 		
     join movie_rates mv on mv.user_id = u.user_id 
     join movies m on m.movie_id  = mv.movie_id
     where  r.user_id = 1 and 
     r.date between current_date - interval '2 months' and current_date
     group by m.title
     order by sum(mv.stars_rated) desc 
     limit 15
 ) as tabela;

• при реализација на прашалникот, наместо 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