SQL Development
Write efficient SQL queries and design schemas.
When to Use
-
Writing complex queries
-
Query optimization
-
Schema design
-
Index strategy
-
Migration planning
Query Patterns
Window Functions
-- Running totals SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d FROM transactions;
-- Ranking SELECT name, score, RANK() OVER (ORDER BY score DESC) as rank, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank, ROW_NUMBER() OVER (ORDER BY score DESC) as row_num FROM players;
-- Partition by category SELECT category, product, sales, sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as pct_of_category FROM products;
CTEs (Common Table Expressions)
WITH monthly_sales AS ( SELECT DATE_TRUNC('month', order_date) as month, SUM(amount) as total FROM orders GROUP BY 1 ), growth AS ( SELECT month, total, LAG(total) OVER (ORDER BY month) as prev_month, (total - LAG(total) OVER (ORDER BY month)) / NULLIF(LAG(total) OVER (ORDER BY month), 0) * 100 as growth_pct FROM monthly_sales ) SELECT * FROM growth WHERE growth_pct < 0;
Recursive CTEs
-- Hierarchical data (org chart, categories) WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
) SELECT * FROM subordinates ORDER BY level, name;
Query Optimization
Index Strategy
-- Composite index for common queries CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
-- Partial index for filtered queries CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- Check query plan EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
Common Issues
Problem Symptom Solution
Missing index Seq Scan Add appropriate index
N+1 queries Many small hits Use JOIN or batch
SELECT * Slow + memory Select only needed columns
No LIMIT Large result Add pagination
Function on col Index not used Rewrite condition
Schema Design
-- Normalized schema CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT NOW() );
CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), total DECIMAL(10,2) NOT NULL, status VARCHAR(20) DEFAULT 'pending', created_at TIMESTAMP DEFAULT NOW() );
CREATE INDEX idx_orders_user ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';
Examples
Input: "Optimize this slow query" Action: Run EXPLAIN, identify bottlenecks, add indexes or rewrite query
Input: "Get top 10 customers by revenue" Action: Write aggregation with proper joins, ordering, and limit