Changes between Version 52 and Version 53 of AdvancedReports

Timestamp:

12/24/22 22:18:45 (3 years ago)

Author:

181006

Comment:

--

AdvancedReports

@@ -59 +59 @@
         ) as q1 
         on u.id_user = q1.id_user_customer
-  }}}
-}}}
-
-== Предложување на 10те најдобро рангирани настани на даден корисник, врз база на корисниците со кои стапил во интеракција во последните 2 месеци и корисници кои имаат оставено број на реплики поголем или еднаков од вкупниот просечен на број на реплики по сите корисници 
-
-{{{#!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;
-  }}}
-}}}
-
-== Настан со најмногу заинтересирани купувачи во последните 100 дена 
-
-{{{#!div style="font-size: 90%"
-  {{{#!sql 
-select e2."name", e2."date", e2.city, e2.price, 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
-  }}}
-}}}
-
-== За секоја година, град со најголем број одржани настани 
-
-{{{#!div style="font-size: 90%"
-  {{{#!sql 
-select q4."location", q4.max_broj_nastani
-from (
-(
-        select extract (year from t.date) as godina,
-                count(distinct e.id_event) broj_nastani, t."location"
-        from ticket t
-        join events e on t.id_event = e.id_event
-        group by godina, t."location" 
-) q1
-
-join 
-
-(
-        select max(q2.broj_nastani) as max_broj_nastani
-        from
-                (
-                select extract (year from t.date) as godina, 
-                        count(distinct e.id_event) broj_nastani,
-                        t."location"
-                from ticket t
-                join events e on t.id_event = e.id_event
-                group by godina, t."location"
-) q2 ) q3
-on
-        q1.broj_nastani = q3.max_broj_nastani) 
-as q4
-  }}}
-}}}
-
-OR
-
-{{{#!div style="font-size: 90%"
-  {{{#!sql 
-select q4.godina, q4.city, q4.max_broj_nastani
-from (
-(
-        select extract (year from e."date") as godina,
-                count(distinct e.id_event) broj_nastani, e.city
-        from events e
-        group by godina, e.city 
-) q1
-
-join 
-
-(
-        select max(q2.broj_nastani) as max_broj_nastani
-        from
-                (
-                select extract (year from e."date") as godina,
-                        count(distinct e.id_event) broj_nastani, e.city
-                from events e
-                group by godina, e.city 
-) q2 ) q3
-on
-        q1.broj_nastani = q3.max_broj_nastani) 
-as q4
-  }}}
-}}}
-
-== Артист со максимален број на одржани настани 
-
-{{{#!div style="font-size: 90%"
-  {{{#!sql 
-select q4."name", q4.surname, q4.max_broj_nastani
-from (
-(
-        select u."name", u.surname, count(e.id_event) as broj_nastani
-        from artist a
-        join users u on a.id_user = u.id_user
-        join events e on a.id_user = e.id_user_artist
-        group by u."name", u.surname) q1
-        
-join
-
-(
-        select max(q2.broj_nastani) as max_broj_nastani
-        from
-                (
-                select u."name", u.surname, count(e.id_event) as broj_nastani
-                from artist a
-                join users u on a.id_user = u.id_user
-                join events e on a.id_user = e.id_user_artist
-                group by u."name", u.surname
-) q2 ) q3
-on
-        q1.broj_nastani = q3.max_broj_nastani) 
-as q4
   }}}
 }}}
@@ -271 +107 @@
 }}}
 
+== Настан со најмногу заинтересирани купувачи во последните 100 дена 
+
+{{{#!div style="font-size: 90%"
+  {{{#!sql 
+select e2."name", e2."date", e2.city, e2.price, 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
+  }}}
+}}}
+
+== За секоја година, град со најголем број одржани настани 
+
+{{{#!div style="font-size: 90%"
+  {{{#!sql 
+select q4."location", q4.max_broj_nastani
+from (
+(
+        select extract (year from t.date) as godina,
+                count(distinct e.id_event) broj_nastani, t."location"
+        from ticket t
+        join events e on t.id_event = e.id_event
+        group by godina, t."location" 
+) q1
+
+join 
+
+(
+        select max(q2.broj_nastani) as max_broj_nastani
+        from
+                (
+                select extract (year from t.date) as godina, 
+                        count(distinct e.id_event) broj_nastani,
+                        t."location"
+                from ticket t
+                join events e on t.id_event = e.id_event
+                group by godina, t."location"
+) q2 ) q3
+on
+        q1.broj_nastani = q3.max_broj_nastani) 
+as q4
+  }}}
+}}}
+
+OR
+
+{{{#!div style="font-size: 90%"
+  {{{#!sql 
+select q4.godina, q4.city, q4.max_broj_nastani
+from (
+(
+        select extract (year from e."date") as godina,
+                count(distinct e.id_event) broj_nastani, e.city
+        from events e
+        group by godina, e.city 
+) q1
+
+join 
+
+(
+        select max(q2.broj_nastani) as max_broj_nastani
+        from
+                (
+                select extract (year from e."date") as godina,
+                        count(distinct e.id_event) broj_nastani, e.city
+                from events e
+                group by godina, e.city 
+) q2 ) q3
+on
+        q1.broj_nastani = q3.max_broj_nastani) 
+as q4
+  }}}
+}}}
+
+== Артист со максимален број на одржани настани 
+
+{{{#!div style="font-size: 90%"
+  {{{#!sql 
+select q4."name", q4.surname, q4.max_broj_nastani
+from (
+(
+        select u."name", u.surname, count(e.id_event) as broj_nastani
+        from artist a
+        join users u on a.id_user = u.id_user
+        join events e on a.id_user = e.id_user_artist
+        group by u."name", u.surname) q1
+        
+join
+
+(
+        select max(q2.broj_nastani) as max_broj_nastani
+        from
+                (
+                select u."name", u.surname, count(e.id_event) as broj_nastani
+                from artist a
+                join users u on a.id_user = u.id_user
+                join events e on a.id_user = e.id_user_artist
+                group by u."name", u.surname
+) q2 ) q3
+on
+        q1.broj_nastani = q3.max_broj_nastani) 
+as q4
+  }}}
+}}}
+
 == Вкупно продадени карти за секој настан