Write SQL Queries

Whether you’re writing SQL by hand in the Query Bench or reviewing what the AI wrote for you, the same handful of mistakes cause most wrong numbers. This page is that handful.

Aggregate first, then filter

Group and aggregate first, then filter on the aggregated values with HAVING.

-- ✔ customers whose total revenue exceeds 1000
SELECT customer_id, SUM(revenue) AS total_revenue
FROM orders
GROUP BY customer_id
HAVING SUM(revenue) > 1000;

-- ❌ drops every order under 1000 BEFORE summing —
--   a customer with 20 × 900 orders disappears entirely
SELECT customer_id, SUM(revenue) AS total_revenue
FROM orders
WHERE revenue > 1000
GROUP BY customer_id;

The two queries answer different questions, and the second one is rarely the question you meant to ask.

Compute rates as a ratio of sums

For conversion rate, ROI, engagement rate, and anything else that’s a fraction: divide the total numerator by the total denominator.

✔ conversion_rate = total_conversions / total_sessions
❌ average(daily_conversion_rate)

Averaging per-day ratios weights a 10-session day the same as a 10,000-session day, which skews the result. While you’re dividing: protect the denominator. SUM(conversions) / NULLIF(SUM(visits), 0) never crashes on an empty period; SAFE_DIVIDE does the same on BigQuery.

Keep valid zeros

A day with 0 purchases still belongs in your analysis. Filtering out zero rows creates survivorship bias — the metric looks better because the bad days are gone, not because anything improved.

One timeframe, one attribution rule

A metric should use one date range and one time anchor. “Revenue from January” is a metric; “revenue from January divided by conversions from February” is a bug, unless the offset is deliberate and stated. When timing is ambiguous, pick the anchor explicitly — created date or resolved date, order date or payment date — and stick to it. The same goes for currency: “Revenue Jan 1–31 (USD)” leaves nothing to interpretation.

Watch the grain of your joins

Decide what one row of the result means — one row per date, per customer, per campaign — and check that joins preserve it. A join to a table with multiple matching rows multiplies your data (a fan-out join), and every SUM downstream of it is silently inflated. The safe pattern: aggregate → join → final result. Collapse each side to the grain you need first, then join.

Return names, not just IDs

campaign_id	campaign_name	revenue
101	Spring Sale	25000

reads a lot better than the same table without campaign_name. Include the human-readable label whenever one exists.

Before you save

A thirty-second check that catches most of the above:

Completeness — right scope, right date range, right units and filters
Bias — no dropped zeros, no mixed time windows or attribution rules
Grain — joins didn’t multiply rows
Safety — no PII or sensitive columns in the output unless explicitly required and permitted
One query — a single query (or one structured set of CTEs), not fragments the reader has to assemble

FAQ

Can I skip filtering a particular table in a sub-query? Yes. You can keep parts of your query unaffected by dashboard filters — just add $$ in front of the table reference. See Partial Filters.

Get started

Ask questions

Organize your work

Create and share

Connect and explore data

Configure agents

Manage your workspace

Aggregate first, then filter

Compute rates as a ratio of sums

Keep valid zeros

One timeframe, one attribution rule

Watch the grain of your joins

Return names, not just IDs

Before you save

FAQ

​Aggregate first, then filter

​Compute rates as a ratio of sums

​Keep valid zeros

​One timeframe, one attribution rule

​Watch the grain of your joins

​Return names, not just IDs

​Before you save

​FAQ

Aggregate first, then filter

Compute rates as a ratio of sums

Keep valid zeros

One timeframe, one attribution rule

Watch the grain of your joins

Return names, not just IDs

Before you save

FAQ