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 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
  value_type_name,
3
  sum(delivered_value_cents) / 100 AS value_dollars,
4
  sum(unit_value) AS total_units
5
FROM fact_delivered_value
6
WHERE created_at > now() - INTERVAL 30 DAY
7
GROUP BY value_type_name
8
ORDER BY value_dollars DESC

1
SELECT
2
  c.name AS customer,
3
  sum(dv.delivered_value_cents) / 100 AS value_dollars
4
FROM fact_delivered_value dv
5
JOIN dim_customer c ON dv.customer_id = c.id
6
WHERE dv.created_at > now() - INTERVAL 30 DAY
7
GROUP BY customer
8
ORDER BY value_dollars DESC
9
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