Changes between Initial Version and Version 1 of ReportsProcedures

Timestamp:

09/23/25 11:09:12 (5 weeks ago)

Author:

223075

Comment:

--

ReportsProcedures

@@ +1 @@
+= AdvancedReports =
+
+Напредни извештаи од базата (PostgreSQL)
+Во продолжение следуваат корисни извештаи за пополнетост на часови, приходи и pivot-прегледи по тренинзи. Примерите користат нашата шема: User, Class, Instructor, Training, Class_Has_Training, User_Booked_Class, Event, User_Event, Package, User_Purchased_Package, Merch_Items, User_Purchased_Merch, Package_Includes_Class.
+
+
+
+== 1) Популарност и приходи: Пакети и Мерч ==
+Пакети – број на купувања и бруто приход:
+{{{
+#!sql
+SELECT
+p.package_name,
+COUNT(*) AS num_purchases,
+SUM(p.price) AS gross_revenue
+FROM "User_Purchased_Package" upp
+JOIN "Package" p ON p.package_id = upp.package_id
+GROUP BY p.package_name
+ORDER BY gross_revenue DESC, num_purchases DESC;
+}}}
+
+Мерч – број на продажби и бруто приход:
+(доколку имате колонa quantity во "User_Purchased_Merch", заменете COUNT() со SUM(COALESCE(upm.quantity,1)) и помножете со price)*
+
+{{{
+#!sql
+SELECT
+m.item_name,
+COUNT(*) AS num_sales,
+SUM(m.price) AS gross_revenue
+FROM "User_Purchased_Merch" upm
+JOIN "Merch_Items" m ON m.merch_id = upm.merch_id
+GROUP BY m.item_name
+ORDER BY gross_revenue DESC, num_sales DESC;
+}}}