Changes between Version 5 and Version 6 of AdvancedReports

Timestamp:

01/31/22 19:51:29 (4 years ago)

Author:

191151

Comment:

--

AdvancedReports

@@ -58 +58 @@
 }}}
 
+== Популарни артисти - рангирано според број на рецензии, тотална заработка и број на добиени понуди
+
+{{{#!sql
+select 
+        u.full_name,
+        u.email,
+        a.city,
+        a.country,
+        a.price_per_hour,
+        at2.name,
+        coalesce(sum(t.stripe_price),0) as earnings,
+        (
+                select 
+                        count(o2.id)
+                from offers o2 
+                where o2.artist_id = a.user_id 
+        ) as num_offers,
+        (
+                (5 * (select 
+                        count(re.artist_id)
+                        from reviews re
+                        where 
+                                re.rating = 5 and
+                                re.artist_id = a.user_id) +
+                4 * (select 
+                        count(re.artist_id)
+                        from reviews re
+                        where 
+                                re.rating = 4 and
+                                re.artist_id = a.user_id) +
+                3 * (select 
+                        count(re.artist_id)
+                        from reviews re
+                        where 
+                                re.rating = 3 and
+                                re.artist_id = a.user_id) +
+                2 * (select 
+                        count(re.artist_id)
+                        from reviews re
+                        where 
+                                re.rating = 2 and
+                                re.artist_id = a.user_id) +
+                1 * (select 
+                        count(re.artist_id)
+                        from reviews re
+                        where 
+                                re.rating = 1 and
+                                re.artist_id = a.user_id)) /
+                ((select 
+                        count(re.artist_id)
+                        from reviews re
+                        where 
+                                re.artist_id = a.user_id))
+        )
+        as average_rating
+from users u
+join artists a on
+        a.user_id = u.id
+join artist_types at2 on
+        at2.id = a.artist_type_id 
+left join reviews r on
+        r.artist_id = a.user_id 
+left join offers o on
+        o.artist_id = a.user_id 
+left join transactions t on
+        t.offer_id = o.id 
+group by a.user_id, u.id, at2.id 
+order by 
+        average_rating desc,
+        earnings desc,
+        num_offers asc;
+}}}
+
 == Број на барања испратени кон одреден артист за одреден настан.