sql

Core Style

Lowercase Everything

All SQL keywords, functions, and syntax in lowercase:

-- good
select id, name from users where status = 'active'

-- bad
SELECT id, name FROM users WHERE status = 'active'

snake_case Identifiers

Tables, columns, aliases, CTEs — all snake_case:

select user_id, created_at from order_items

Meaningful Aliases

-- good
select ord.id, cust.name
from orders as ord
join customers as cust on cust.id = ord.customer_id

-- bad
select a.id, b.name
from orders a join customers b on b.id = a.customer_id

Dialect Routing

This skill owns universal SQL style; dialect skills carry deltas only:

• PostgreSQL syntax or OLTP (jsonb, on conflict, returning, partitioning, pgbouncer) → load postgres-skill
• OLAP / columnar / MergeTree / time-series → load clickhouse-skill
• Streaming SQL, Flink DDL, windowed aggregations over streams → load flink-sql-skill

Lateral joins (below) are standard SQL:1999 — SQL Server spells it cross apply; ClickHouse lacks it.

Design Philosophy

Queries should be readable stories. A well-written query reads top-to-bottom, telling you what it does without mental gymnastics.

Query Design

• CTEs as named paragraphs — each CTE does ONE thing
• Filter early, aggregate late — reduce data before joining
• Explicit over implicit — spell out join conditions, column lists
• Set-based thinking — think in sets, not loops

Performance Philosophy

• Correct first, then optimize
• Measure with explain analyze — intuition is often wrong
• Index for actual query patterns, not hypothetical ones
• Simple queries are often fast queries (optimizer understands them better)

When to Break Rules

• Denormalization for read-heavy reporting (measured)
• Complex single query vs multiple simple queries (benchmark both)

Schema Principles

Dialect-neutral defaults (dialect skills carry their specifics):

• not null by default — nullable only when absence is a meaningful state
• check constraints are cheap asserts at the data layer: check (price >= 0), check (status in ('active', 'disabled'))
• Declare foreign keys explicitly, even when the application "guarantees" integrity
• Surrogate primary key id; foreign keys named <referenced_table_singular>_id (orders.customer_id → customers.id)

Query Structure & Formatting

Each major clause on its own line. Indent continuation logically:

select
  u.id,
  u.email,
  u.created_at,
  count(o.id) as order_count
from users as u
left join orders as o
  on o.user_id = u.id
  and o.status = 'completed'
where u.active = true
  and u.created_at >= '2024-01-01'
group by
  u.id,
  u.email,
  u.created_at
having count(o.id) > 5
order by order_count desc
limit 100

Trailing Commas

Column lists use trailing commas. and/or in where clauses are leading:

select
  id,
  email,
  first_name,
  last_name
from users

Explicit join Syntax

Never use implicit comma joins:

-- good
from orders as o
inner join customers as c on c.id = o.customer_id

-- bad: never
from orders o, customers c
where c.id = o.customer_id

Avoid select *

Always specify columns in production code. Critical for columnar databases, good discipline everywhere.

CTEs as Named Paragraphs

CTEs over nested subqueries — more readable, easier to debug. Modern engines inline single-reference, side-effect-free CTEs (PostgreSQL 12+), so this costs nothing; materialization control is dialect-specific — see postgres-skill.

with monthly_revenue as (
  select
    date_trunc('month', order_date) as month,
    sum(amount) as revenue
  from orders
  where status = 'completed'
  group by date_trunc('month', order_date)
),
revenue_with_growth as (
  select
    month,
    revenue,
    lag(revenue) over (order by month) as prev_month_revenue,
    revenue - lag(revenue) over (order by month) as growth
  from monthly_revenue
)
select
  month,
  revenue,
  growth,
  round(100.0 * growth / nullif(prev_month_revenue, 0), 2) as growth_pct
from revenue_with_growth
order by month

Window Functions

The standard catalog (rank/dense_rank/row_number, lag/lead, percent-of-total via sum(x) over (), moving averages via rows between n preceding and current row) needs no examples. Two idioms earn rules:

-- running total: always state rows explicitly — the default frame with
-- order by is range, which lumps all order-by ties (peers) into the current row
select
  order_date,
  amount,
  sum(amount) over (order by order_date rows unbounded preceding) as running_total
from orders

-- deduplication with row_number
with ranked as (
  select
    id,
    email,
    created_at,
    row_number() over (partition by email order by created_at desc) as rn
  from users
)
select id, email, created_at from ranked where rn = 1

Lateral Joins

Top-N per group, correlated computation without correlated subqueries:

-- top 3 recent orders per customer
select c.id, c.name, recent.order_id, recent.total
from customers as c
cross join lateral (
  select o.id as order_id, o.total
  from orders as o
  where o.customer_id = c.id
  order by o.created_at desc
  limit 3
) as recent

Recursive CTEs

Hierarchical data, graph traversal:

-- org chart: find all reports under a manager
with recursive reports as (
  select id, name, manager_id, 0 as depth
  from employees
  where id = :manager_id

  union all

  select e.id, e.name, e.manager_id, r.depth + 1
  from employees as e
  inner join reports as r on r.id = e.manager_id
  where r.depth < 10  -- safety limit
)
select * from reports order by depth, name

Conditional Aggregation

filter is the house default (SQL:2003; supported in PostgreSQL, SQLite, DuckDB):

select
  date_trunc('month', order_date) as month,
  count(*) as total_orders,
  count(*) filter (where status = 'completed') as completed,
  sum(amount) filter (where status = 'completed') as completed_revenue
from orders
group by date_trunc('month', order_date)

Engines without filter: fall back to count(case when status = 'completed' then 1 end). ClickHouse: -If combinators — see clickhouse-skill.

Grouping Sets

Multi-level aggregations in a single pass:

select
  coalesce(region, 'ALL') as region,
  coalesce(category, 'ALL') as category,
  sum(sales) as total_sales,
  grouping(region) as is_region_total,
  grouping(category) as is_category_total
from sales
group by grouping sets (
  (region, category),
  (region),
  ()
)

Performance Fundamentals

SARGable Predicates

Keep indexed columns naked — avoid wrapping in functions:

-- good: can use index
where order_date >= '2024-01-01' and order_date < '2025-01-01'

-- bad: full table scan
where year(order_date) = 2024

-- good: can use index
where email = 'test@example.com'

-- bad: function wrapping kills index
where lower(email) = 'test@example.com'

Keyset Pagination

-- bad: offset — scans 10,020 rows
select * from products order by id limit 20 offset 10000

-- good: keyset — consistent performance
select * from products where id > :last_seen_id order by id limit 20

exists vs in

exists and in (subquery) plan identically on modern engines (both become semi-joins). The real rule: prefer not exists over not in (subquery) — one null in the subquery makes not in return zero rows, and nullable columns block anti-join optimization.

select * from customers as c
where exists (
  select 1 from orders as o
  where o.customer_id = c.id and o.status = 'completed'
)

-- in is fine for small static literal lists
where category_id in (1, 2, 3)

Indexing Principles

Composite index column ordering — equality first, then range/sort:

-- index: (customer_id, status, created_at)

-- ✓ uses full index
where customer_id = 1 and status = 'active' and created_at > '2024-01-01'

-- ✓ uses first two columns
where customer_id = 1 and status = 'active'

-- ✓ uses first column
where customer_id = 1

-- ✗ cannot use index (missing leading column)
where status = 'active'

Comment Doctrine

Comments are for the person reading and reviewing the SQL: say why a filter, join, or CTE exists, call out anything that is not obvious, and write the reason as a plain sentence (no WHY: label).

Keep the work plan out of comments: no phases, plan steps, ticket numbers, or words like "for now", "temporary", or "later" (migrations attract this rot the most). Those go stale and someone has to find and delete them. Unfinished work belongs in plan.md.

Non-trivial queries, views, migrations, and SQL functions get a header comment above them: one line saying what it is for, plus the cost driver. Do not write big-O or O(...) on SQL — the planner picks the plan, so naming O(...) would be a lie. Name the cost driver instead: plan and index choice, expected row cardinality. This header is the SQL analogue of a big-O header in the code skills. The comment is the claim; explain analyze is the verification. Trivial CRUD exempt.

-- active users with their completed-order count, one row per user
-- cost driver: hash join on orders(user_id) index; ~50k active users, orders ~5M rows pre-filter
select
  u.id,
  u.email,
  count(o.id) as completed_orders
from users as u
left join orders as o
  on o.user_id = u.id
  and o.status = 'completed'
where u.active = true
group by u.id, u.email

Editing counts too: when you change a query, migration, or function, keep the comments already there, fix any your change made wrong, and add the missing header, cost note, or reason. Do this only for SQL you add or change, not for nearby SQL you did not touch.

explain analyze

Always validate non-trivial queries. Watch for:

• Sequential/full scans on large tables (missing index?)
• Nested loops with high row counts (consider hash/merge join)
• Sort spilling to disk (increase memory? add index?)
• Estimated vs actual rows far apart (stale statistics?)

NULL Handling

-- coalesce for defaults
select coalesce(nickname, first_name, 'Anonymous') as display_name from users

-- nullif to prevent division by zero
select revenue / nullif(cost, 0) as margin from products

-- null comparisons need is null
where deleted_at is null      -- good
where deleted_at = null       -- bad: always false

Anti-Patterns

Correlated Subqueries in select — Use CTE or join

-- bad: executes subquery per row
select
  o.id,
  (select count(*) from order_items where order_id = o.id) as item_count
from orders as o

-- good: single aggregation
select o.id, coalesce(oi.item_count, 0) as item_count
from orders as o
left join (
  select order_id, count(*) as item_count
  from order_items group by order_id
) as oi on oi.order_id = o.id

select distinct as Bandaid — Fix the join

-- bad: smells like wrong join
select distinct u.id, u.name
from users as u join orders as o on o.user_id = u.id

-- good: use exists
select u.id, u.name from users as u
where exists (select 1 from orders as o where o.user_id = u.id)

Implicit Type Conversions

-- bad: implicit conversion, may skip index
where phone = 5551234567

-- good: explicit type
where phone = '5551234567'

N+1 in Application Code

-- instead of: fetch orders, then loop and fetch items per order
select o.id, o.customer_id, oi.product_id, oi.quantity
from orders as o
left join order_items as oi on oi.order_id = o.id
where o.customer_id = :customer_id

Security

-- always parameterized (safe)
select * from users where id = :user_id
select * from users where id = $1
select * from users where id = ?

-- never concatenate: SQL injection risk

Least privilege: read-only connections for queries, specific grants, row-level security where appropriate.

Before Committing (Self-Review)

Design

• Query reads top-to-bottom as a coherent story
• Each CTE does ONE thing with a descriptive name
• Could explain the query to a colleague in 30 seconds
• Set-based thinking — no row-by-row logic

Correctness

• Tested with realistic data, not just happy path
• Query bugfix? Failing fixture-based regression test lands before the fix (red, green, vacuous check) — harness per host-language skill
• null handling explicit (coalesce, is null, nullif)
• Join conditions complete — no accidental cross joins
• Aggregations have correct group by

Performance

• Predicates are SARGable (no functions on indexed columns)
• Appropriate indexes exist for where/join/order by
• No N+1 patterns in application code
• Validated with explain analyze for non-trivial queries
• Pagination uses keyset, not offset

Security

• Parameterized queries — no string concatenation
• Least privilege — read-only connection where possible
• No sensitive data in logs or error messages

Maintainability

• Comments say why (filters, joins, CTEs) — no plan steps, tickets, or "for now"/"later"
• Non-trivial query, view, migration, or function has a header: what it is for + cost driver (no O(...))
• Edited SQL keeps its comments correct and adds the missing header/cost note
• Consistent style (lowercase, snake_case, trailing commas)
• No select * in production code