Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

02/12/24 15:52:10 (7 months ago)

Author:

216037

Comment:

--

AdvancedReports

@@ -1 @@
-незнам
+== Извештај кој прикажува инструктори со највисок профит по месеци и години.
+
+{{{
+WITH InstructorProfit AS (
+    SELECT 
+        i.ID AS InstructorID,
+        EXTRACT(YEAR FROM p.Date) AS Year,
+        EXTRACT(MONTH FROM p.Date) AS Month,
+        SUM(p.Amount) AS TotalProfit
+    FROM 
+        project.Payment p
+    INNER JOIN 
+        project.Instructor i ON p.InstructorFK = i.ID
+    GROUP BY 
+        i.ID, EXTRACT(YEAR FROM p.Date), EXTRACT(MONTH FROM p.Date)
+)
+SELECT 
+    ip.Year,
+    ip.Month,
+    u.FirstName || ' ' || u.LastName AS InstructorName,
+    ip.TotalProfit
+FROM 
+    InstructorProfit ip
+INNER JOIN 
+    project.Instructor i ON ip.InstructorID = i.ID
+INNER JOIN 
+    project.User u ON i.UserFK = u.ID
+WHERE 
+    (ip.InstructorID, ip.TotalProfit, ip.Year, ip.Month) IN (
+        SELECT 
+            ip2.InstructorID,
+            MAX(ip2.TotalProfit) AS MaxProfit,
+            ip2.Year,
+            ip2.Month
+        FROM 
+            InstructorProfit ip2
+        GROUP BY 
+            ip2.Year, ip2.month, ip2.InstructorID
+    )
+    ORDER BY
+    ip.TotalProfit DESC;
+
+
+}}}
+
+== Извештај кој ни прикажува топ три најпопуларни типови на тренинзи според бројот на инструктори кои ги преферираат, каде што членовите имаат доделен инструктор.
+
+{{{
+SELECT
+    w.Type AS WorkoutType,
+    COUNT(*) AS InstructorPreferenceCount
+FROM
+    project.Workout w
+WHERE
+    EXISTS (
+        SELECT 1
+        FROM project.Member m
+        WHERE w.MemberFK = m.Id
+        AND m.InstructorFK IS NOT NULL
+    )
+GROUP BY
+    w.Type
+ORDER BY
+    COUNT(*) DESC
+LIMIT 3;
+
+}}}
+