Gross revenue

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

curl --location --request GET "$LAGO_URL/api/v1/analytics/gross_revenue?external_customer_id=hooli_1234&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 value for all issued invoices
    issued_invoices AS (
        SELECT
            i.id,
            i.issuing_date,
            i.total_amount_cents::float AS amount_cents,
            i.currency,
            COALESCE(SUM(refund_amount_cents::float),0) AS total_refund_amount_cents
        FROM invoices i
        LEFT JOIN customers c ON i.customer_id = c.id
        LEFT JOIN credit_notes cn ON cn.invoice_id = i.id
        WHERE i.organization_id = '__YOUR_ORGANIZATION_ID__'
            AND i.status = 1
            ---AND c.external_id = 'hooli_1234' --- FILTER BY CUSTOMER
        GROUP BY i.id, i.issuing_date, i.total_amount_cents, i.currency
        ORDER BY i.issuing_date ASC
    ),

    --- Get value for all instant charges (paid in advance but not invoiceable)
    instant_charges AS (
        SELECT
            f.id,
            f.created_at AS issuing_date,
            f.amount_cents AS amount_cents,
            f.amount_currency AS currency,
            0 AS total_refund_amount_cents
        FROM fees f
        LEFT JOIN subscriptions s ON f.subscription_id = s.id
        LEFT JOIN customers c ON c.id = s.customer_id
        WHERE c.organization_id = '__YOUR_ORGANIZATION_ID__'
            AND f.invoice_id IS NULL
            AND f.pay_in_advance IS TRUE
            ---AND c.external_id = 'hooli_1234' --- FILTER BY CUSTOMER
    ),

    --- Combine data to get total of gross revenue
    combined_data AS (
        SELECT
            DATE_TRUNC('month', issuing_date) AS month,
            currency,
            COALESCE(SUM(amount_cents), 0) AS amount_cents,
            COALESCE(SUM(total_refund_amount_cents), 0) AS total_refund_amount_cents
        FROM (
            SELECT * FROM issued_invoices
            UNION ALL
            SELECT * FROM instant_charges
        ) AS gross_revenue
        GROUP BY month, currency, total_refund_amount_cents
    )

    --- Get gross revenue month over month
    SELECT
        am.month,
        cd.currency,
        SUM(cd.amount_cents - cd.total_refund_amount_cents) AS amount_cents
    FROM all_months am
    LEFT JOIN combined_data cd ON am.month = cd.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.amount_cents IS NOT NULL
        --- AND cd.currency = 'EUR'
    GROUP BY am.month, cd.currency
    ORDER BY am.month