> ## Documentation Index
> Fetch the complete documentation index at: https://docs.supaboard.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Write SQL Queries

> Habits that keep query results accurate: aggregate before filtering, sum before dividing, keep your zeros, and watch the join grain.

Whether you're writing SQL by hand in the [Query Bench](/query-bench/overview) 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`.

```sql theme={null}
-- ✔ customers whose total revenue exceeds 1000
SELECT customer_id, SUM(revenue) AS total_revenue
FROM orders
GROUP BY customer_id
HAVING SUM(revenue) > 1000;
```

```sql theme={null}
-- ❌ 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.

```text theme={null}
✔ 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](/dashboards/filters/ignore-filtering-for-a-given-table).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.