PLG RevOps lives with a CRM that records the state of the pipeline and the customer list as of right now. How each deal or account got where it is, and what customers are actually doing in the product, usually lives in a warehouse or a billing system that isn't the CRM.

-- one row per account where the billed seats and last-30d
-- active users disagree. Assumes hubspot_companies and
-- product_events are landed in the warehouse nightly by a
-- Hightouch reverse-ETL sync (or similar).
WITH active_users AS (
  SELECT
    account_id,
    COUNT(DISTINCT user_id) AS active_user_count_30d
  FROM product_events
  WHERE event_ts >= current_date - interval '30 days'
  GROUP BY 1
)
SELECT
  h.account_id,
  h.account_name,
  h.billed_seats                               AS hubspot_seats,
  COALESCE(a.active_user_count_30d, 0)         AS warehouse_users,
  GREATEST(COALESCE(a.active_user_count_30d, 0) - h.billed_seats, 0) AS overage_users,
  (GREATEST(COALESCE(a.active_user_count_30d, 0) - h.billed_seats, 0)
     * h.contract_per_seat_rate)               AS dollar_impact
FROM hubspot_companies h
LEFT JOIN active_users a USING (account_id)
WHERE COALESCE(a.active_user_count_30d, 0) > h.billed_seats
ORDER BY dollar_impact DESC;