sql-query-writer

SQL Query Writer Skill

Overview

Write correct, readable, and performant SQL queries. Covers SELECT fundamentals through advanced window functions, CTEs, and performance optimization. Dialect-aware: PostgreSQL, MySQL, SQLite, BigQuery.

Step-by-Step Process

Step 1: Understand the Data Model

Before writing a query, confirm:

• Tables involved and their primary keys
• Join relationships (1:1, 1:many, many:many via junction table)
• Data types of filter and join columns
• Approximate row counts (affects optimization strategy)
• Target dialect (PostgreSQL, MySQL, SQLite, BigQuery, DuckDB)

Step 2: Start with the Simplest Correct Query

-- Start simple, then optimize
SELECT
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 100;

Step 3: Common Query Patterns

Top N per group (window function)

SELECT *
FROM (
    SELECT
        product_id,
        category,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
    FROM product_sales
) ranked
WHERE rank <= 5;

Running totals and moving averages

SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS revenue_7d_avg
FROM daily_sales
ORDER BY date;

Year-over-year comparison

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue,
    LAG(SUM(total), 12) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS revenue_prev_year,
    ROUND(
        (SUM(total) - LAG(SUM(total), 12) OVER (ORDER BY DATE_TRUNC('month', order_date)))
        / NULLIF(LAG(SUM(total), 12) OVER (ORDER BY DATE_TRUNC('month', order_date)), 0) * 100,
        1
    ) AS yoy_pct_change
FROM orders
GROUP BY 1
ORDER BY 1;