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