write-analytical-sql

name	write-analytical-sql
description	Writes accurate, readable, dialect-correct analytical SQL — CTEs, window functions, aggregations, and joins — across Postgres, BigQuery, Snowflake, and Databricks.
when_to_use	When the user needs an analytical SQL query written or translated — build a report query with CTEs/window functions/aggregations, express business logic in SQL, or translate a query between warehouse dialects (Postgres/BigQuery/Snowflake/Databricks).

When to Use

Use when the task is to write or translate analytical SQL — reports, metrics, cohort/funnel logic, dedup, running totals, rankings — in Postgres, BigQuery, Snowflake, or Databricks SQL.

• Use for: report queries with CTEs/window functions, expressing business logic in SQL, dialect translation preserving semantics.
• Do NOT use for: query performance tuning (use optimize-sql-query) or schema migrations (out of scope).
• Hard rule: never invent column or table names. If schema is unknown, inspect or ask before writing — a wrong column name is worse than a question.

Steps

1. Lock the dialect first. If unstated, ask or infer from connection/context. Dialect changes date functions, dedup syntax (QUALIFY), array/struct access, and string concat — get it wrong and the query won't parse.
2. Get the real schema. Inspect via information_schema.columns / \d table / DESCRIBE table, or ask. Confirm for every joined table: the grain (one row per what?), the join keys, and which columns are nullable. Grain ambiguity is the #1 source of wrong numbers.
3. Decompose into named CTEs, one logical step per CTE (filter → join → aggregate → rank → final select). Name them for intent (active_users, daily_revenue), not cte1. Keep the final SELECT thin.
4. Pick the right join type and verify grain. Before any join, confirm the right side is unique on the join key. If it can have multiple rows, you have fan-out — pre-aggregate the right side in a CTE first, or the join multiplies your fact rows and inflates every SUM/COUNT.
5. Use window functions instead of self-joins for ranking, running totals, dedup, period-over-period:
   • Dedup / latest-per-group: ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) then keep = 1.
   • Running total: SUM(x) OVER (PARTITION BY ... ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
   • Period-over-period: LAG(metric) OVER (ORDER BY period).
   • Be explicit about the frame — default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which silently sums ties together; use ROWS when you mean row-by-row.
6. Guard aggregates against NULL and the empty set.
   • COUNT(col) skips NULLs; COUNT(*) doesn't — pick deliberately. Distinct users = COUNT(DISTINCT user_id).
   • SUM over zero rows returns NULL, not 0 — wrap with COALESCE(SUM(x), 0) when a numeric default is expected.
   • AVG ignores NULLs in the denominator; if NULL means zero, COALESCE before averaging.
   • Filtered aggregates: COUNT(*) FILTER (WHERE cond) (Postgres/Databricks) or COUNT(CASE WHEN cond THEN 1 END) (portable, works everywhere).
7. Apply dialect specifics (see table below) for dates, dedup, arrays, and row limiting.
8. For translation: map semantics, not text. Re-express date math, dedup, array/struct access, and QUALIFY/LIMIT/TOP per the target dialect. Re-check that NULL and casting behavior still match the source intent.
9. Format for review: keywords uppercase, leading commas or trailing — pick one, one join per line, CTEs separated by blank lines. Add a one-line comment stating what the query answers and its output grain.
10. Validate on a sample if a DB is reachable (see Verify).

Dialect cheat-sheet

Need	Postgres	BigQuery	Snowflake	Databricks (Spark SQL)
Current date	CURRENT_DATE	CURRENT_DATE()	CURRENT_DATE	current_date()
Truncate to month	DATE_TRUNC('month', d)	DATE_TRUNC(d, MONTH)	DATE_TRUNC('month', d)	date_trunc('month', d)
Date diff (days)	d2 - d1	DATE_DIFF(d2, d1, DAY)	DATEDIFF(day, d1, d2)	datediff(d2, d1)
Add interval	d + INTERVAL '7 day'	DATE_ADD(d, INTERVAL 7 DAY)	DATEADD(day, 7, d)	date_add(d, 7)
Dedup top-1 per group	ROW_NUMBER() in CTE, filter outer	QUALIFY ROW_NUMBER()...=1	QUALIFY ROW_NUMBER()...=1	QUALIFY ROW_NUMBER()...=1
Row limit	LIMIT n	LIMIT n	LIMIT n (not TOP)	LIMIT n
String concat	|| or CONCAT	CONCAT (|| ok)	|| or CONCAT	|| or concat
Safe divide	x / NULLIF(y,0)	SAFE_DIVIDE(x,y)	x / NULLIF(y,0)	x / NULLIF(y,0)
Array element	arr[1] (1-based)	arr[OFFSET(0)] (0-based)	arr[0] (0-based)

Common Errors

• Postgres has no QUALIFY. Wrap the window function in a CTE/subquery and filter WHERE rn = 1 in the outer query. BigQuery/Snowflake/Databricks support QUALIFY directly.
• GROUP BY ordinals. Postgres/Snowflake/Databricks accept GROUP BY 1, 2; prefer explicit column names in CTEs so a column reorder doesn't silently regroup.
• Fan-out from a one-to-many join silently inflates SUM/COUNT. Always pre-aggregate the many-side to the fact's grain before joining. Symptom: totals are too high and don't reconcile.
• COUNT(DISTINCT) is the fix when fan-out already happened, but it's a band-aid — fixing the grain is correct. Don't reach for DISTINCT to hide a join bug.
• WHERE on a LEFT JOINed table turns it into an INNER JOIN. Filters on the right table belong in the ON clause, or the unmatched (NULL) rows get dropped.
• NULL never equals NULL. x = NULL is always false — use IS NULL / IS DISTINCT FROM. NOT IN (subquery with a NULL) returns zero rows; use NOT EXISTS.
• BigQuery arrays are 0-based with OFFSET; Postgres is 1-based. Off-by-one when translating array access is a silent wrong-row bug.
• Integer division truncates in Postgres/Snowflake/Databricks (5/2 = 2). Cast one side: x::numeric / y or CAST(x AS FLOAT64)/y (BigQuery).
• Window frame defaults to RANGE, which lumps tied ORDER BY rows together in running totals. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for true row-by-row cumulation.
• HAVING filters after aggregation, WHERE before. Putting an aggregate condition in WHERE errors; putting a row filter in HAVING works but scans more than needed.
• Timezone drift: BigQuery TIMESTAMP is UTC; casting to without a timezone shifts day boundaries. Be explicit: .

Verify

• Parse/compile: if a DB is reachable, run the query (or EXPLAIN it) — confirm it executes and column names resolve.
• Grain check: run SELECT key, COUNT(*) FROM result GROUP BY key HAVING COUNT(*) > 1 on the claimed unique key; expect zero rows. If not, you have fan-out or a dedup miss.
• Reconcile a total: compute one headline metric two independent ways (e.g. SUM of the detail vs. a separate aggregate) and confirm they match — catches join inflation.
• NULL/empty edge: confirm the query returns a sensible value (0 vs NULL) when a group has no rows, per the COALESCE decision in Step 6.
• Translation parity: if translating, run source and target against the same sample and diff the result sets — they must be identical, not just "look right."
• State the output grain and what the query answers in one line so the caller can sanity-check intent against numbers.