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

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

 
 select mov.title, 
        table2.gnr as genre,
        mov.description, 
        extract(year from mov.airing_date) as released_year, 
        mov.imdb_rating 
		from (
		select  u.user_id, 
				g.genre_id, 
				count(m), 
				m2.movie_id as m_id, 
				g.genre_type as gnr 
			from users u
				join movie_likes ml on ml.user_id = u.user_id 
				join movies m on m.movie_id = ml.movie_id 
				join movie_genres mg on mg.movie_id = m.movie_id
				join genres g on g.genre_id = mg.genre_id 
				join movie_genres mg2 on mg2.genre_id = mg.genre_id 
				join movies m2 on m2.movie_id = mg2.movie_id 

								where u.user_id = 1 ***

			group by u.user_id, g.genre_id, m2.movie_id , g.genre_type 
			order by count(m) desc
			limit 15
		) as table2	
	join movies mov on mov.movie_id = table2.m_id
	order by RANDOM()
	limit 5

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