Changes between Version 21 and Version 22 of AdvancedReports

Timestamp:

12/23/22 20:23:42 (3 years ago)

Author:

191285

Comment:

--

AdvancedReports

@@ -124 +124 @@
 {{{#!div style="font-size: 90%"
   {{{#!sql 
-select p.ime as predmet, count(zz.id_ucenik) as broj_na_zainteresirani_ucenici
-from predmeti p 
-join zainteresiran_za zz 
-on zz.id_predmet = p.id_predmet 
-join ucenici u 
-on u.id_ucenik = zz.id_ucenik 
-where zz.datum between current_date - interval '100 days' and current_date
-group by p.ime
-order by count(zz.id_ucenik) desc 
-limit 1;
+select e2."name", q4.max_broj_zainteresirani
+from
+((select e.id_event, count(u.id_user) as broj_na_zainteresirani_kupuvaci
+from ticket t  
+join events e 
+on t.id_event  = e.id_event 
+join users u
+on t.id_user_customer =u.id_user 
+join cart c on c.id_user_customer =t.id_user_customer 
+where e."date" between current_date - interval '100 days' and current_date
+group by e.id_event ) q1
+
+join 
+
+(
+select max(q2.broj_na_zainteresirani_kupuvaci) as max_broj_zainteresirani
+from (
+select e.id_event, count(u.id_user) as broj_na_zainteresirani_kupuvaci
+from ticket t  
+join events e 
+on t.id_event  = e.id_event 
+join users u
+on t.id_user_customer =u.id_user 
+join cart c on c.id_user_customer =t.id_user_customer 
+where e."date" between current_date - interval '100 days' and current_date
+group by e.id_event 
+) q2 
+) q3 on q1.broj_na_zainteresirani_kupuvaci=q3.max_broj_zainteresirani)
+as q4
+join 
+events e2 
+on q4.id_event=e2.id_event 
+
   }}}
 }}}