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

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

 select g.genre_type, 
 	count(distinct d) + count(distinct r) as discussions_replies_count
 from genres g
 	join movie_genres mg on mg.genre_id = g.genre_id
 	join movies m on m.movie_id = mg.movie_id 
 	join discussions d on d.movie_id = m.movie_id and d.date between current_date - interval '14 days' and current_date
 	left join replies r on r.discussion_id = d.discussion_id and r.date between current_date - interval '14 days' and current_date
 group by g.genre_id
 order by count(distinct d) + count(distinct r) desc
 limit 1

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

 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