Use this skill when writing SQL queries for data retrieval, analysis, or manipulation. Trigger when the user asks to "write a SQL query", "query a database", "get data from a table", "write a join", "create a report query", or any task involving SQL for relational databases (PostgreSQL, MySQL, SQLite, etc.).
Write correct, performant, and readable SQL queries.
Use this skill when:
Do NOT use this skill when:
Always write queries in this order for readability:
WITH cte_name AS (
-- Common Table Expressions first
SELECT ...
)
SELECT
column1,
column2,
aggregate_function(column3) AS alias
FROM main_table t
JOIN other_table o ON t.id = o.table_id
LEFT JOIN optional_table opt ON t.id = opt.table_id
WHERE t.status = 'active'
AND t.created_at >= '2025-01-01'
GROUP BY column1, column2
HAVING aggregate_function(column3) > threshold
ORDER BY column1 ASC
LIMIT 100;
Break complex queries into readable CTEs rather than nested subqueries:
-- GOOD: CTEs are readable and debuggable
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)
),
monthly_growth AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
) AS growth_pct
FROM monthly_revenue
)
SELECT * FROM monthly_growth
ORDER BY month DESC;
-- BAD: Nested subqueries are hard to read
SELECT *, (revenue - prev_rev) / NULLIF(prev_rev, 0) * 100 AS growth
FROM (
SELECT *, LAG(revenue) OVER (ORDER BY month) AS prev_rev
FROM (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders WHERE status = 'completed'
GROUP BY 1
) sub1
) sub2;
-- Ranking
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees;
-- Running totals
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM orders;
-- Percent of total
SELECT
category,
revenue,
ROUND(revenue * 100.0 / SUM(revenue) OVER (), 1) AS pct_of_total
FROM category_revenue;
-- Use COALESCE for defaults
SELECT
name,
COALESCE(phone, email, 'No contact') AS primary_contact,
COALESCE(discount, 0) AS discount
FROM customers;
-- Use NULLIF to prevent division by zero
SELECT
name,
total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM customers;
-- NULL-safe comparisons
WHERE column IS NULL -- not: WHERE column = NULL
WHERE column IS NOT NULL -- not: WHERE column != NULL
WHERE column IS DISTINCT FROM other_column -- PostgreSQL NULL-safe !=
-- ALWAYS preview before modifying
-- Step 1: SELECT to verify
SELECT id, status, updated_at
FROM orders
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '30 days';
-- Step 2: UPDATE with the same WHERE clause
UPDATE orders
SET status = 'cancelled', updated_at = NOW()
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '30 days';
-- For critical operations, use transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Verify before committing
SELECT id, balance FROM accounts WHERE id IN (1, 2);
COMMIT; -- or ROLLBACK if something looks wrong
| Mistake | Why It's Wrong | Do This Instead |
|---|---|---|
SELECT * in production queries | Wasteful, fragile if schema changes | List specific columns |
WHERE column = NULL | NULL comparisons always return NULL, not TRUE | Use IS NULL / IS NOT NULL |
Implicit joins (FROM a, b WHERE a.id = b.a_id) | Hard to read, easy to accidentally cross-join | Use explicit JOIN ... ON syntax |
GROUP BY 1, 2 (positional) | Fragile — breaks if SELECT order changes | Use column names in GROUP BY |
Not using LIMIT during development | Returns millions of rows, crashes client | Always LIMIT while iterating |
| String concatenation for filtering | SQL injection risk | Use parameterized queries ($1, %s, ?) |
EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to check query plansWHERE, JOIN ON, and ORDER BY clausesEXISTS over IN for subqueries checking existenceDATE_TRUNC() (PostgreSQL) or DATE_FORMAT() (MySQL) for date groupingOFFSET/FETCH or cursor-based pagination-- Calculate 7-day moving average excluding weekends