Analytics query cookbook

1
SELECT count() AS signals
2
FROM fact_signal
3
WHERE created_at > now() - INTERVAL 30 DAY

1
SELECT signal_name, count() AS signals
2
FROM fact_signal
3
WHERE created_at > now() - INTERVAL 30 DAY
4
GROUP BY signal_name
5
ORDER BY signals DESC

1
SELECT toDate(created_at) AS day, count() AS signals
2
FROM fact_signal
3
WHERE created_at > now() - INTERVAL 30 DAY
4
GROUP BY day
5
ORDER BY day

1
SELECT c.name AS customer, count() AS signals
2
FROM fact_signal s
3
JOIN dim_customer c ON s.customer_id = c.id
4
WHERE s.created_at > now() - INTERVAL 30 DAY
5
GROUP BY customer
6
ORDER BY signals DESC
7
LIMIT 20

1
SELECT data.opportunity_stage::String AS stage, count() AS signals
2
FROM fact_signal
3
WHERE created_at > now() - INTERVAL 30 DAY
4
GROUP BY stage
5
ORDER BY signals DESC

1
SELECT count() AS completed_calls
2
FROM fact_signal
3
WHERE signal_name = 'call'
4
  AND data.call_status::String = 'completed'
5
  AND created_at > now() - INTERVAL 7 DAY

1
SELECT
2
  data.account_tier::String AS tier,
3
  avg(data.call_length_sec::Float64) AS avg_call_seconds
4
FROM fact_signal
5
WHERE signal_name = 'call'
6
  AND created_at > now() - INTERVAL 30 DAY
7
GROUP BY tier

1
SELECT vendor, count() AS calls, sum(cost_amount) / 100 AS dollars
2
FROM fact_cost
3
WHERE start_time > now() - INTERVAL 30 DAY
4
GROUP BY vendor
5
ORDER BY dollars DESC

1
SELECT
2
  model,
3
  count() AS calls,
4
  sum(input_tokens) AS input_tokens,
5
  sum(output_tokens) AS output_tokens,
6
  sum(cost_amount) / 100 AS dollars
7
FROM fact_cost
8
WHERE start_time > now() - INTERVAL 30 DAY
9
GROUP BY model
10
ORDER BY dollars DESC

1
SELECT toDate(start_time) AS day, sum(cost_amount) / 100 AS dollars
2
FROM fact_cost
3
WHERE start_time > now() - INTERVAL 30 DAY
4
GROUP BY day
5
ORDER BY day

1
SELECT c.name AS customer, sum(co.cost_amount) / 100 AS dollars
2
FROM fact_cost co
3
JOIN dim_customer c ON co.customer_id = c.id
4
WHERE co.start_time > now() - INTERVAL 30 DAY
5
GROUP BY customer
6
ORDER BY dollars DESC
7
LIMIT 20

1
SELECT
2
  s.signal_name,
3
  count(DISTINCT s.signal_id) AS signals,
4
  sum(co.cost_amount) / 100 AS dollars,
5
  sum(co.cost_amount) / 100 / count(DISTINCT s.signal_id) AS dollars_per_signal
6
FROM fact_signal s
7
JOIN fact_cost co ON s.trace_id = co.trace_id
8
WHERE s.created_at > now() - INTERVAL 30 DAY
9
GROUP BY s.signal_name
10
ORDER BY dollars DESC

1
SELECT
2
  toStartOfMonth(issue_date) AS month,
3
  sum(total_incl_tax) / 100 AS revenue
4
FROM fact_invoice
5
WHERE status = 'posted'
6
  AND issue_date > now() - INTERVAL 12 MONTH
7
GROUP BY month
8
ORDER BY month

1
SELECT c.name AS customer, sum(i.total_incl_tax) / 100 AS revenue
2
FROM fact_invoice i
3
JOIN dim_customer c ON i.customer_id = c.id
4
WHERE i.status = 'posted'
5
GROUP BY customer
6
ORDER BY revenue DESC
7
LIMIT 20

1
SELECT p.name AS product, sum(il.total_after_tax) / 100 AS revenue
2
FROM fact_invoice_line il
3
JOIN fact_order_line ol ON il.order_line_id = ol.id
4
JOIN dim_product p ON ol.product_id = p.id
5
GROUP BY product
6
ORDER BY revenue DESC

1
SELECT
2
  multiIf(
3
    il.charge_type = 'usage', 'usage',
4
    il.charge_type IN ('oneTime', 'recurring', 'seatBased'), 'fixed',
5
    'unknown'
6
  ) AS billing_basis,
7
  count() AS lines,
8
  sum(il.total_after_tax) / 100 AS revenue
9
FROM fact_invoice_line il
10
JOIN fact_invoice i ON il.invoice_id = i.id
11
WHERE i.status = 'posted'
12
GROUP BY billing_basis
13
ORDER BY revenue DESC

1
SELECT c.name AS customer, sum(i.amount_due) / 100 AS outstanding
2
FROM fact_invoice i
3
JOIN dim_customer c ON i.customer_id = c.id
4
WHERE i.payment_status IN ('pending', 'partiallyPaid', 'overdue')
5
GROUP BY customer
6
ORDER BY outstanding DESC

1
SELECT toDate(payment_date) AS day, sum(amount) / 100 AS collected
2
FROM fact_payment
3
WHERE payment_status = 'posted'
4
  AND payment_date > now() - INTERVAL 30 DAY
5
GROUP BY day
6
ORDER BY day

1
SELECT signal_name, sum(subtotal) / 100 AS billed
2
FROM fact_usage_summary
3
WHERE status = 'billed'
4
GROUP BY signal_name
5
ORDER BY billed DESC

1
SELECT
2
  signal_name,
3
  sum(signal_count) AS used,
4
  sum(included_quantity) AS included,
5
  sum(greatest(signal_count - included_quantity, 0)) AS overage
6
FROM fact_usage_summary
7
WHERE included_quantity IS NOT NULL
8
GROUP BY signal_name

1
SELECT cc.name AS currency, sum(t.amount) AS net_credits
2
FROM fact_credit_transaction t
3
JOIN dim_credits_currency cc ON t.credits_currency_id = cc.id
4
GROUP BY currency

1
SELECT signal_name, -sum(amount) AS credits_spent
2
FROM fact_credit_transaction
3
WHERE type = 'SPEND'
4
  AND created_at > now() - INTERVAL 30 DAY
5
GROUP BY signal_name
6
ORDER BY credits_spent DESC

1
SELECT
2
  s.data.external_user_id::String AS external_user_id,
3
  cc.name AS currency,
4
  -sum(t.amount) AS credits_spent
5
FROM fact_credit_transaction t
6
JOIN fact_signal s ON t.signal_mongo_id = s.signal_id
7
JOIN dim_credits_currency cc ON t.credits_currency_id = cc.id
8
WHERE t.type = 'SPEND'
9
  AND t.created_at > now() - INTERVAL 30 DAY
10
GROUP BY external_user_id, currency
11
ORDER BY credits_spent DESC

1
SELECT
2
  value_type_name,
3
  delivered_value_currency AS currency,
4
  round(sum(delivered_value_cents) / 100, 2) AS value_amount,
5
  sum(unit_value) AS total_units
6
FROM fact_delivered_value
7
WHERE created_at > now() - INTERVAL 30 DAY
8
GROUP BY value_type_name, delivered_value_currency
9
ORDER BY value_amount DESC

1
SELECT
2
  c.name AS customer,
3
  dv.delivered_value_currency AS currency,
4
  round(sum(dv.delivered_value_cents) / 100, 2) AS value_amount
5
FROM fact_delivered_value dv
6
JOIN dim_customer c ON dv.customer_id = c.id
7
WHERE dv.created_at > now() - INTERVAL 30 DAY
8
GROUP BY customer, currency
9
ORDER BY value_amount DESC
10
LIMIT 20

1
SELECT signal_id, signal_name, created_at
2
FROM fact_signal
3
WHERE created_at < '2026-06-01 00:00:00'  -- last value from the previous page
4
ORDER BY created_at DESC
5
LIMIT 1000