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