Извештај кој прикажува инструктори со највисок профит по месеци и години.

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;