Changes between Version 14 and Version 15 of AdvancedReports

Timestamp:

12/20/22 14:26:03 (2 years ago)

Author:

191285

Comment:

--

AdvancedReports

@@ -65 +65 @@
 {{{#!div style="font-size: 90%"
   {{{#!sql 
-
+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;
   }}}
 }}}