--- Get start date of the organization
WITH organization_creation_date AS (
SELECT
DATE_TRUNC('month', o.created_at) AS start_month
FROM organizations o
WHERE o.id = '__YOUR_ORGANIZATION_ID__'
)
--- Generate a series of months
, all_months AS (
SELECT
generate_series(
(SELECT start_month FROM organization_creation_date),
DATE_TRUNC('month', CURRENT_DATE + INTERVAL '10 years'),
interval '1 month'
) AS month
)
--- Get invoice number and value per status
,invoices_per_status AS (
SELECT
DATE_TRUNC('month', i.issuing_date) AS month,
i.currency,
CASE
WHEN i.payment_status = 0 THEN 'pending'
WHEN i.payment_status = 1 THEN 'paid'
WHEN i.payment_status = 2 THEN 'failed'
END AS payment_status,
COALESCE(COUNT(*), 0) AS number_invoices,
COALESCE(SUM(i.total_amount_cents::float), 0) AS amount_cents
FROM invoices i
WHERE i.organization_id = '__YOUR_ORGANIZATION_ID__'
AND i.status = 1 --- ONLY FINALIZED INVOICES
GROUP BY payment_status, month, currency
)
--- Get invoice total and amount per status per month till the current month
SELECT
am.month,
payment_status,
currency,
COALESCE(number_invoices, 0) AS number_invoices,
COALESCE(amount_cents, 0) AS amount_cents
FROM all_months am
LEFT JOIN invoices_per_status ips ON ips.month = am.month AND ips.payment_status IS NOT NULL
WHERE am.month <= DATE_TRUNC('month', CURRENT_DATE)
AND payment_status IS NOT NULL
---AND currency = '' --- Filter by currency if needed
ORDER BY am.month, payment_status, currency;
