SELECT
toStartOfMonth(source.posting_date) AS posting_date,
12 * SUM(source.rolling_revenue) AS Net_ARR
FROM (
-- Revenue entries per journal line
WITH revenue_entries AS (
SELECT
posted_at AS posting_date,
jl.customer_id,
jl.revenue_contract_item_id,
rci.name AS product_name,
SUM(jl.credits - jl.debits) / 100 AS revenue
FROM journal_lines jl
JOIN accounts_revamped acr ON jl.account_id = acr.id
JOIN journal_entries je ON je.id = jl.journal_entry_id
JOIN revenue_contract_items rci ON jl.revenue_contract_item_id = rci.id
WHERE
acr.account_category = 'INCOME' AND
jl.invoice_id IS NULL AND
je.status_type = 'posted' AND
je.deleted_at IS NULL
GROUP BY posted_at, jl.customer_id, jl.revenue_contract_item_id, rci.name
),
-- Compute rolling average revenue
monthly_revenue AS (
SELECT *,
AVG(revenue) OVER (
PARTITION BY customer_id, revenue_contract_item_id, product_name
ORDER BY posting_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_revenue
FROM revenue_entries
),
-- Detect previous revenue and new customers
revenue_movement AS (
SELECT mrr.*,
ANY(rolling_revenue) OVER (
PARTITION BY customer_id, revenue_contract_item_id, product_name
ORDER BY posting_date
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS prev_rolling_revenue,
CASE
WHEN customer_info.customer_id IS NOT NULL THEN 1 ELSE 0
END AS is_new_customer
FROM monthly_revenue mrr
LEFT JOIN (
SELECT MIN(posting_date) AS start_date, customer_id
FROM monthly_revenue
GROUP BY customer_id
) customer_info
ON mrr.posting_date = customer_info.start_date AND mrr.customer_id = customer_info.customer_id
),
-- Classify new, expansion, and contraction revenue (not used in chart)
revenue_breakdown AS (
SELECT *,
CASE WHEN is_new_customer = 1 THEN rolling_revenue ELSE 0 END AS new_revenue,
CASE WHEN is_new_customer != 1 AND prev_rolling_revenue < rolling_revenue THEN rolling_revenue - prev_rolling_revenue ELSE 0 END AS expansion_revenue,
CASE WHEN is_new_customer != 1 AND prev_rolling_revenue > rolling_revenue THEN rolling_revenue - prev_rolling_revenue ELSE 0 END AS contraction_revenue
FROM revenue_movement
)
SELECT * FROM revenue_breakdown
) AS source
GROUP BY toStartOfMonth(source.posting_date)
ORDER BY toStartOfMonth(source.posting_date) ASC
