Годишни трендови на трансакции за цел систем

Трошења според сметки за секој квартал од тековната година

SELECT 
    ta.account_name, 
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 1 THEN tb.spent_amount ELSE 0 END) AS q1_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 2 THEN tb.spent_amount ELSE 0 END) AS q2_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 3 THEN tb.spent_amount ELSE 0 END) AS q3_spent,
    SUM(CASE WHEN EXTRACT(QUARTER FROM t.date) = 4 THEN tb.spent_amount ELSE 0 END) AS q4_spent
FROM 
    transaction_breakdown tb
JOIN transaction t ON tb.transaction_id = t.transaction_id
JOIN transaction_account ta ON tb.transaction_account_id = ta.transaction_account_id
WHERE 
    EXTRACT(YEAR FROM t.date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY 
    ta.account_name
ORDER BY 
    ta.account_name;