Engineering SQLWindow FunctionsQuery Optimization

SQL Pro

Advanced SQL: window functions, CTEs, recursive queries, query optimization with EXPLAIN, indexing strategies, partitioning, and dialect differences.

An advanced SQL practitioner who writes complex analytics queries using window functions, recursive CTEs, and lateral joins — and then optimizes them. Reads query execution plans from EXPLAIN ANALYZE, designs composite indexes, and knows when to partition a table versus when to materialize a view.

Added Mar 20, 2026

$npx skills add johnefemer/skillfish --skill sql-pro

Capabilities Real Examples Get Started Models & Context Works Great With

What This Skill Can Do

Concrete capabilities you get when you install this skill.

Write complex analytics queries with window functions, PARTITION BY, and ROWS BETWEEN

Design and optimize indexes: composite, partial, covering, and expression indexes

Read EXPLAIN ANALYZE output and identify sequential scans, hash joins, and sorts

Implement recursive CTEs for hierarchical data (org trees, bill of materials)

Design table partitioning strategies by range, list, or hash

Translate queries between PostgreSQL, MySQL, SQLite, and SQL Server dialects

Real Conversations

See how the SQL Pro skill responds to actual requests. These are representative examples of what your AI agent will produce.

You

I have a transactions table with user_id, amount, and created_at. I need to show each user's running balance after each transaction, ordered by time. How do I write this?

Agent

Use a window function with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

SELECT
    user_id,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
FROM transactions
ORDER BY user_id, created_at;

How it works:

PARTITION BY user_id — resets the running total for each user
ORDER BY created_at — defines the order of accumulation
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — sums from the first row up to and including the current row

Handling ties (same timestamp): If two transactions share the same created_at, the window frame includes both before accumulating — which gives them the same running balance. Add a tiebreaker:

ORDER BY created_at, transaction_id -- stable ordering

Performance: Add a composite index on (user_id, created_at). Without it, the window function requires a full table sort, which is O(n log n) and will be slow at 10M+ rows.

You

This query takes 8 seconds on a table with 10 million orders. I have an index on customer_id and another on created_at, but it's still slow. Can you help diagnose it?

Agent

Paste your query and EXPLAIN ANALYZE output — but here's how to read it yourself while you gather that:

Red flags to look for in EXPLAIN ANALYZE:

Seq Scan on orders  (cost=0.00..180000.00 rows=9000000)

Sequential scan on a large table = index not being used. Causes: low cardinality column, OR condition across indexed columns, function wrapping the column.

Hash Join  (cost=... rows=...) (actual time=2100..4800 ms)

Slow hash join = one side of the join is large. Check if you're joining without a WHERE clause filter that could reduce the row count first.

Most common causes of slow queries with existing indexes:

Function on indexed column:

-- ❌ Index on created_at unused — function evaluated per row
WHERE DATE(created_at) = '2026-01-01'

-- ✅ Range scan uses the index
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

OR across different indexed columns:

-- ❌ PostgreSQL may choose Seq Scan over two Bitmap Index Scans
WHERE customer_id = 123 OR status = 'pending'

-- ✅ UNION ALL uses each index independently
SELECT * FROM orders WHERE customer_id = 123
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND customer_id != 123

Missing composite index: If your query filters on customer_id AND orders by created_at:

CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

This single index satisfies both the filter and the sort — no separate sort step.

Get Started

How to install and use this skill in your preferred environment.

Skills are designed for AI coding agents (Claude Code, Cursor, Windsurf) and IDE-based workflows where the agent can read files, run scripts, and act on your codebase.

Models & Context

Which AI models and context windows work best with this skill.

Recommended Models

Claude Sonnet 4 or GPT-4o handle complex SQL well. For dialect-specific questions (SQL Server, BigQuery), verify generated syntax before running in production.

Context Window

SKILL.md ~4KB. Query optimization sessions with large EXPLAIN output benefit from 64K+ context.

Pro tips for best results

1

Be specific

Include numbers — users, budget, RPS — so the skill can size the architecture.

2

Share constraints

Compliance needs, team size, and existing stack all improve the output.

3

Iterate

Start with a high-level design, then ask follow-ups for IaC, cost analysis, or security review.

4

Combine skills

Pair with companion skills below for end-to-end coverage.

Works Great With

These skills complement SQL Pro for end-to-end coverage. Install them together for better results.

Database Designer

Schema analysis, ERD generation, index optimization, and migrations.

DatabaseSchema

Postgres Pro

PostgreSQL mastery: advanced indexing (GIN, GiST, BRIN), JSONB, full-text search, partitioning, replication, pgvector for embeddings, and performance tuning.

PostgreSQLpgvector

Senior Backend

REST APIs, database optimization, authentication, and microservices.

BackendAPIs

Performance Profiler

Node/Python/Go profiling, bundle analysis, and load testing.

PerformanceProfiling

$ skillfish add johnefemer/skillfish --all # install all skills at once

Ready to try SQL Pro?

Install the skill and start getting expert-level guidance in your workflow — any agent, any IDE.