Changes between Version 22 and Version 23 of AdvancedReports

Timestamp:

03/10/22 19:05:32 (3 years ago)

Author:

191215

Comment:

--

AdvancedReports

@@ -35 +35 @@
 }}}
 
+== Листа на организатори кои организирале настани на кои биле поканети 2 или повеќе артисти
+{{{#!sql
+select 
+        u.id,
+        u.username,
+        u.email,
+        u.name,
+        o.city,
+        o.country
+from events as e
+join (
+        select 
+                e.id,
+                e.title,
+                count(distinct a.user_id) as num_artists
+        from events as e 
+        join offers as o2 on
+                                                o2.event_id = e.id
+        join artists as a on
+                                                a.user_id = o2.artist_id
+        group by e.id 
+) as tt on
+                                                tt.id = e.id and 
+                                                tt.num_artists > 1
+join organizers as o on 
+                                                o.user_id = e.organizer_id
+join users as u on 
+                                        u.id = o.user_id 
+group by o.user_id, u.id 
+}}}
+
 == Да се најде просечно време (во денови) кое било потрошено од страна на Организаторите и Артистите за да склучат договор (да платат).
 
@@ -40 +71 @@
 select avg(extract(day from o.completed_at-o.created_at)) from offers o 
 where completed_at notnull 
+}}}
+
+== Број на трансакции реализирани од страна на организатори кои оставиле позитивни рецензии (оценка над 3) кон артисти
+{{{#!sql
+select count (*) from (
+with tempTable as (select 
+        o.* 
+from organizers o 
+join reviews as r on 
+                                        r.organizer_id = o.user_id and 
+                                        r.rating > 3
+group by o.user_id)
+select 
+        t.*
+from events as e
+join tempTable as tt on 
+                                                tt.user_id = e.organizer_id 
+join offers as o2 on 
+                                        o2.event_id = e.id 
+join transactions as t on 
+                                        t.offer_id = o2.id 
+) as temp
 }}}