SQL Queries Skill

Write correct, performant SQL across major data warehouse dialects. Handle everything from simple selects to complex analytical queries with CTEs, window functions, and advanced aggregations.

Workflow

Step 1: Clarify Requirements

Before writing SQL, establish:

1. What question does this answer? Restate the business question.
2. What dialect? Snowflake, BigQuery, Databricks SQL, PostgreSQL, MySQL, Redshift.
3. What tables are involved? Get table names, schemas, key columns.
4. What is the grain? One row per user? Per day? Per transaction?
5. What filters apply? Date range, segments, exclusions.
6. How large are the tables? This affects optimization strategy.

Step 2: Structure with CTEs

Always use CTEs for readability. Structure queries in logical layers:

WITH
-- Layer 1: Source data with filters
filtered_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        amount
    FROM orders
    WHERE order_date >= '2024-01-01'
      AND status = 'completed'
      AND is_test = FALSE
),

-- Layer 2: Intermediate aggregation
daily_revenue AS (
    SELECT
        order_date,
        COUNT(DISTINCT customer_id) AS customers,
        COUNT(order_id) AS orders,
        SUM(amount) AS revenue
    FROM filtered_orders
    GROUP BY order_date
),

-- Layer 3: Final calculation
final AS (
    SELECT
        order_date,
        customers,
        orders,
        revenue,
        revenue / NULLIF(orders, 0) AS avg_order_value,
        SUM(revenue) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS cumulative_revenue
    FROM daily_revenue
)

SELECT * FROM final
ORDER BY order_date;

Step 3: Apply Dialect-Specific Syntax

Date Functions

String Functions

Array/JSON

Step 4: Window Functions

Master these patterns:

-- Running total
SUM(amount) OVER (PARTITION BY customer_id ORDER BY date ROWS UNBOUNDED PRECEDING)

-- Row number for deduplication
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn

-- Lag/Lead for period-over-period
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue

-- Percent of total
amount / SUM(amount) OVER (PARTITION BY category) AS pct_of_category

-- Moving average
AVG(daily_value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d

-- Rank within groups
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank

-- Cumulative distinct count (approximate)
-- Snowflake: HLL
-- BigQuery: APPROX_COUNT_DISTINCT (not windowed, use a different approach)

Step 5: Optimize for Performance

General optimization rules:

1. Filter early. Push WHERE clauses into CTEs as close to the source as possible.
2. Avoid SELECT *. Name specific columns to reduce I/O.
3. Use approximate functions for large datasets. APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT) when exact counts are not needed.
4. Partition pruning. Filter on partition columns (usually date) to avoid full table scans.
5. Avoid correlated subqueries. Rewrite as JOINs or window functions.
6. Use QUALIFY instead of subqueries for filtering window results:

   -- Instead of wrapping in a subquery to filter by rn = 1:
   SELECT *
   FROM events
   QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) = 1

7. Minimize joins on large tables. Pre-aggregate before joining when possible.
8. Use semi-joins (EXISTS/IN) instead of full joins when you only need to filter.

Snowflake-specific:

• Use clustering keys for frequently filtered columns
• Avoid ORDER BY in CTEs (it is ignored)
• Use RESULT_SCAN(LAST_QUERY_ID()) for iterative exploration

BigQuery-specific:

• Filter on _PARTITIONDATE or _PARTITIONTIME for partitioned tables
• Use QUALIFY (supported since 2021)
• Prefer SAFE_DIVIDE(a, b) over a / NULLIF(b, 0)

Step 6: Common Patterns

Deduplication

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM raw_table
) WHERE rn = 1

Pivoting

SELECT
    date,
    SUM(CASE WHEN channel = 'organic' THEN revenue END) AS organic_revenue,
    SUM(CASE WHEN channel = 'paid' THEN revenue END) AS paid_revenue,
    SUM(CASE WHEN channel = 'referral' THEN revenue END) AS referral_revenue
FROM orders
GROUP BY date

Gap Detection

WITH dates AS (
    SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', CURRENT_DATE())) AS date
)
SELECT d.date
FROM dates d
LEFT JOIN daily_data dd ON d.date = dd.date
WHERE dd.date IS NULL

Retention Cohorts

WITH first_purchase AS (
    SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
    FROM orders GROUP BY 1
)
SELECT
    fp.cohort_month,
    DATEDIFF('month', fp.cohort_month, DATE_TRUNC('month', o.order_date)) AS month_number,
    COUNT(DISTINCT o.customer_id) AS retained_customers
FROM orders o
JOIN first_purchase fp ON o.customer_id = fp.customer_id
GROUP BY 1, 2

Output Format

-- [Brief description of what this query does]
-- Dialect: [Snowflake/BigQuery/PostgreSQL/etc.]
-- Expected output: [Description of result set]

[SQL query with comments explaining non-obvious logic]

Best Practices

1. Format consistently. Keywords uppercase, table/column names lowercase, one clause per line.
2. Comment the why, not the what. -- Exclude refunds processed after cutoff not -- filter by status.
3. Use meaningful CTE names. daily_active_users not tmp1.
4. Handle division by zero. Always wrap denominators with NULLIF(x, 0).
5. Use COALESCE for null handling. Be explicit about how nulls should be treated.
6. Test with LIMIT first. Run with LIMIT 100 before executing the full query on large tables.
7. Include row counts. Add COUNT(*) as a sanity check when developing.
8. Avoid implicit type coercion. Cast explicitly: CAST(id AS STRING).
9. Use date literals consistently. Prefer '2024-01-01' over DATE('2024-01-01') unless dialect requires it.
10. Version your queries. Include a comment header with date and purpose when saving queries.