Changes between Version 12 and Version 13 of AdvancedReports

Timestamp:

01/21/22 17:34:54 (4 years ago)

Author:

196021

Comment:

--

AdvancedReports

@@ -46 +46 @@
 }}}
 
-== 
+== Извештај за прикажан интерес за секој филм , колку пати е резервирана проекција за тој филм во последни две недели  и просечна заработка од карти за таа проекција за тој филм
+{{{#!sql 
+   select distinct mp.projection_id,m.movie_name,vkupna_brojka.vkupno,prosek.prosek_zarabotka
+from movieprojection as mp
+join movie as m on mp.movie_id=m.movie_id
+
+join(   select distinct r.reservation_id, count(r.projection_id) as vkupno
+         from reservation as r
+          join movieprojection as mp2 on r.projection_id=mp2.projection_id 
+          join movie as m2 on mp2.movie_id=m2.movie_id
+          where r.reservation_date between now() - interval '40 days' and now()
+          group by 1
+ )as vkupna_brojka on vkupna_brojka.vkupno is not null
+ 
+ join(   select distinct r2.reservation_id,mp3.projection_id, avg(suma.sum1) as prosek_zarabotka
+         from reservation as r2
+          join movieprojection as mp3 on r2.projection_id=mp3.projection_id 
+          
+          join (select mp4.projection_id , sum(mp4.projection_price) as sum1
+                 from movieprojection as mp4
+                 group by 1
+          )as suma on suma.sum1 is not null
+          join movie as m3 on mp3.movie_id=m3.movie_id
+          group by 1,2
+          order by prosek_zarabotka desc
+ )as prosek on prosek.prosek_zarabotka is not null
+}}}