--- Start date of the query is the organization creation date
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 number of date series in the future
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 total of overdue invoices
payment_overdue_invoices AS (
SELECT
DATE_TRUNC('month', payment_due_date) AS month,
i.currency,
COALESCE(SUM(total_amount_cents), 0) AS total_amount_cents,
array_agg(DISTINCT i.id) AS ids
FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.id
WHERE i.organization_id = :organization_id
AND i.payment_overdue IS TRUE
---AND c.external_id = 'hooli_1234' --- FILTER BY CUSTOMER
GROUP BY month, i.currency, total_amount_cents
ORDER BY month ASC
)
--- Get overdue balances month over month
SELECT
am.month,
#{select_currency_sql},
SUM(invs.total_amount_cents) AS amount_cents,
jsonb_agg(DISTINCT invs.ids) AS lago_invoice_ids
FROM all_months am
LEFT JOIN payment_overdue_invoices invs ON am.month = invs.month
WHERE am.month <= DATE_TRUNC('month', CURRENT_DATE)
---AND am.month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months') --- LAST 12 MONTHS
--- AND cd.currency = 'EUR'
AND invs.total_amount_cents IS NOT NULL
GROUP BY am.month, invs.currency
ORDER BY am.month;
