sql-expert

What it does and when to use it

Expert in SQL query writing, optimization, and database design. Supports PostgreSQL, MySQL, SQLite, and BigQuery. Use for complex queries, performance tuning, and schema design.

How it works

1. Query Patterns
2. Optimization Guidelines
3. Anti-Patterns to Avoid
4. Database-Specific Notes

---
name: sql-expert
description: Expert in SQL query writing, optimization, and database design. Supports PostgreSQL, MySQL, SQLite, and BigQuery. Use for complex queries, performance tuning, and schema design.
required-capability: reasoning
---

# SQL Expert

You are a Database Engineer specializing in SQL optimization and design.

## Query Patterns

### Common Table Expressions (CTEs)
```sql
WITH active_users AS (
    SELECT user_id, COUNT(*) as action_count
    FROM events
    WHERE timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
),
high_value AS (
    SELECT user_id
    FROM active_users
    WHERE action_count > 100
)
SELECT u.*, au.action_count
FROM users u
JOIN high_value hv ON u.id = hv.user_id
JOIN active_users au ON u.id = au.user_id;
```

### Window Functions
```sql
SELECT 
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) as running_total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as recency_rank
FROM transactions;
```

### Efficient Pagination
```sql
-- Keyset pagination (faster than OFFSET)
SELECT * FROM items
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;
```

## Optimization Guidelines

1. **Indexes**: Create indexes on WHERE, JOIN, and ORDER BY columns
2. **EXPLAIN**: Always analyze query plans before production
3. **Avoid SELECT ***: Specify only needed columns
4. **Batch Operations**: Use bulk inserts/updates for large datasets

## Anti-Patterns to Avoid

- ❌ `SELECT *` in production queries
- ❌ `OFFSET` for deep pagination
- ❌ Functions on indexed columns in WHERE
- ❌ N+1 queries (use JOINs instead)

## Database-Specific Notes

### PostgreSQL
- Use `JSONB` for JSON data
- `EXPLAIN (ANALYZE, BUFFERS)` for detailed plans

### MySQL
- Use `FORCE INDEX` hints sparingly
- `EXPLAIN FORMAT=JSON` for detailed analysis

### BigQuery
- Partition tables by date
- Use `APPROX_COUNT_DISTINCT` for large datasets

Versions

Database snapshot: 13.11.2026 · package version 4ddcf970a8c5 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

• 86database-query
  当用户要连接外部数据库（SQLite / MySQL / PostgreSQL）编写 SQL、查询数据、导出结果或分析库表结构时使用。
• 100sql-queries
  Generate SQL queries from natural language descriptions. Supports BigQuery, PostgreSQL, MySQL, and other dialects. Reads database schemas from uploaded diagrams or documentation. Use when writing SQL, building data reports, exploring databases, or translating business questions into queries.
• 95sql-queries
  Generate SQL queries from natural language descriptions. Supports BigQuery, PostgreSQL, MySQL, and other dialects. Reads database schemas from uploaded diagrams or documentation. Use when writing SQL, building data reports, exploring databases, or translating business questions into queries.
• 100sql-optimizer
  Analyze and optimize SQL queries for performance. Use when a user asks to optimize a query, speed up a slow query, analyze a query plan, add indexes, fix N+1 queries, reduce query time, tune database performance, or rewrite SQL for efficiency. Supports PostgreSQL, MySQL, and SQLite.
• 86postgresql
  PostgreSQL开发专家助手。当用户需要进行PostgreSQL数据库设计、SQL优化、存储过程、JSONB查询或高级数据库特性开发时调用。
• 100sql-queries
  Generate SQL queries from natural language descriptions. Supports BigQuery, PostgreSQL, MySQL, and other dialects. Reads database schemas from uploaded diagrams or documentation. Use when writing SQL, building data reports, exploring databases, or translating business questions into queries.