Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

12/29/22 01:27:44 (17 months ago)

Author:

201070

Comment:

--

AdvancedReports

@@ -113 +113 @@
     }}}
 
-=== Ранг на цени и просечна цена на продукти од секоја продавница
-
-{{{
-   select
-         s.storename,
-         
-         min(p.productinstanceprice) as min_price,
-         
-         max(p.productinstanceprice) as max_price,
-         
-         round(avg(p.productinstanceprice)) as avg_price
-   from
-        project.stores s
-        inner join project.productinstances p on (p.storeid = s.storeid)
-        
-   group by
-        s.storename
-   order by
-        avg(p.productinstanceprice)
-}}}
-
-=== Ранг на цени и просечна цена на продукти од секој производител
-
-{{{
-   select 
-        m.manufacturername,
-        
-        p.productname,
-        
-        min(p2.productinstanceprice) as min_price,
-        
-        max(p2.productinstanceprice) as max_price,
-        
-        round(avg(p2.productinstanceprice)) as avg_price
-        
-   from
-        project.manufacturers m 
-        inner join project.products p on (p.manufacturerid = m.manufacturerid)
-        inner join project.productinstances p2 on (p2.productid = p.productid)
-
-   group by m.manufacturername, p.productname
-   order by m.manufacturername, p.productname
-
-}}}
-
-=== Продукт кој се јавил најмногу во листа на омилени продукти
-
-{{{
-   select
-        q1.productid, p.productname, q1.num_users_favourite
-   from
-         (
-                select uf.productid as productid, count(*) as num_users_favourite
-                from
-                        project.userfavourites as uf
-                        group by
-                                  uf.productid
-                        order by
-                                  num_users_favourite desc
-                        fetch first rows with ties
-         
-         ) as q1
-        inner join project.products as p on (p.productid = q1.productid)
-        order by
-              q1.productid desc
-}}}
-
 === Продукти со најголем просечен рејтинг на ниво на категорија, додадени во последната година (продуктите се групирани по категорија)
 
@@ -215 +148 @@
         
         ) as q2
-where
+   where
         q2.rank_num = 1
 }}}
@@ -287 +220 @@
 }}}
 
+=== Продукт кој се јавил најмногу во листа на омилени продукти
+
+{{{
+   select
+        q1.productid, p.productname, q1.num_users_favourite
+   from
+         (
+                select uf.productid as productid, count(*) as num_users_favourite
+                from
+                        project.userfavourites as uf
+                        group by
+                                  uf.productid
+                        order by
+                                  num_users_favourite desc
+                        fetch first rows with ties
+         
+         ) as q1
+        inner join project.products as p on (p.productid = q1.productid)
+        order by
+              q1.productid desc
+}}}
+
+=== Ранг на цени и просечна цена на продукти од секоја продавница
+
+{{{
+   select
+         s.storename,
+         
+         min(p.productinstanceprice) as min_price,
+         
+         max(p.productinstanceprice) as max_price,
+         
+         round(avg(p.productinstanceprice)) as avg_price
+   from
+        project.stores s
+        inner join project.productinstances p on (p.storeid = s.storeid)
+        
+   group by
+        s.storename
+   order by
+        avg(p.productinstanceprice)
+}}}
+
+=== Ранг на цени и просечна цена на продукти од секој производител
+
+{{{
+   select 
+        m.manufacturername,
+        
+        p.productname,
+        
+        min(p2.productinstanceprice) as min_price,
+        
+        max(p2.productinstanceprice) as max_price,
+        
+        round(avg(p2.productinstanceprice)) as avg_price
+        
+   from
+        project.manufacturers m 
+        inner join project.products p on (p.manufacturerid = m.manufacturerid)
+        inner join project.productinstances p2 on (p2.productid = p.productid)
+
+   group by m.manufacturername, p.productname
+   order by m.manufacturername, p.productname
+
+}}}
+
+
 === Број на продукти од секоја категорија