Net annual recurring revenue

toStartOfMonth(posting_date)

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