Invoiced usage

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

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

    -- Get creation 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 future 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 revenue for all usage-based fees (charge type)
    usage_fees AS (
        SELECT
            f.charge_id,
            f.amount_cents::float,
            f.amount_currency AS currency,
            f.created_at AS fee_created_at
        FROM fees f
        LEFT JOIN subscriptions s ON s.id = f.subscription_id
        LEFT JOIN customers c ON c.id = s.customer_id
        WHERE f.invoiceable_type = 'Charge'
            AND f.fee_type = 0
            AND c.organization_id = '__YOUR_ORGANIZATION_ID__'
    ),

    -- Get total revenue for all billable metrics
    total_revenue_per_bm AS (
        SELECT
            DATE_TRUNC('month', uf.fee_created_at) AS month,
            bm.code,
            uf.currency,
            COALESCE(SUM(amount_cents), 0) AS amount_cents
        FROM usage_fees uf
        LEFT JOIN charges c ON c.id = uf.charge_id
        LEFT JOIN billable_metrics bm ON bm.id = c.billable_metric_id
        GROUP BY month, code, currency
        ORDER BY month
    )

    -- Select the desired results
    SELECT
        am.month,
        trpmb.code,
        trpmb.currency,
        trpmb.amount_cents
    FROM all_months AS am
    LEFT JOIN total_revenue_per_bm trpmb ON trpmb.month = am.month
    WHERE am.month <= DATE_TRUNC('month', CURRENT_DATE)
        AND trpmb.currency IS NOT NULL
        AND trpmb.amount_cents IS NOT NULL
    ORDER BY am.month DESC, trpmb.amount_cents DESC;