Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

12/26/21 21:53:15 (4 years ago)

Author:

193113

Comment:

--

AdvancedReports

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