Overdue invoices

LAGO_URL="https://api.getlago.com"
API_KEY="__YOUR_API_KEY__"

curl --location --request GET "$LAGO_URL/api/v1/analytics/overdue_balance?currency=USD" \
--header "Authorization: Bearer $API_KEY" \
--header 'Content-Type: application/json' \

--- 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;