Advanced SQL expertise including window functions, CTEs, recursive queries, query optimization with EXPLAIN plans, indexing strategies, pivot/unpivot operations, JSON operations, full-text search, stored procedures, and identification of performance anti-patterns across PostgreSQL, MySQL, and SQL Server.
Use when the user asks about sql master, sql master best practices, or needs guidance on sql master implementation.
Do NOT use when the user needs a different specialized skill or is asking about an unrelated technology domain.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
name
sql-master
description
Advanced SQL expertise including window functions, CTEs, recursive queries, query optimization with EXPLAIN plans, indexing strategies, pivot/unpivot operations, JSON operations, full-text search, stored procedures, and identification of performance anti-patterns across PostgreSQL, MySQL, and SQL Server.
Use when the user asks about sql master, sql master best practices, or needs guidance on sql master implementation.
Do NOT use when the user needs a different specialized skill or is asking about an unrelated technology domain.
This skill provides deep expertise in advanced SQL techniques that separate production-grade database work from basic querying. It covers the full spectrum from analytical window functions through query optimization, enabling you to write SQL that is both correct and performant at scale.
Window Functions
Window functions operate over a set of rows related to the current row without collapsing the result set. They are essential for ranking, running totals, moving averages, and gap-and-island analysis.
Ranking Functions
-- ROW_NUMBER: unique sequential integer, no ties-- RANK: same rank for ties, gaps after ties-- DENSE_RANK: same rank for ties, no gaps
employee_id,
department,
salary,
() ( department salary ) row_num,
() ( department salary ) rank_val,
() ( department salary ) dense_rank_val,
() ( department salary ) quartile
employees;
SELECT
ROW_NUMBER
OVER
PARTITION
BY
ORDER
BY
DESC
AS
RANK
OVER
PARTITION
BY
ORDER
BY
DESC
AS
DENSE_RANK
OVER
PARTITION
BY
ORDER
BY
DESC
AS
NTILE
4
OVER
PARTITION
BY
ORDER
BY
DESC
AS
FROM
Offset Functions
-- LAG/LEAD: access previous/next rows without self-joinSELECT
order_date,
revenue,
LAG(revenue, 1) OVER (ORDERBY order_date) AS prev_day_revenue,
LEAD(revenue, 1) OVER (ORDERBY order_date) AS next_day_revenue,
revenue -LAG(revenue, 1) OVER (ORDERBY order_date) AS day_over_day_change,
FIRST_VALUE(revenue) OVER (
PARTITIONBY DATE_TRUNC('month', order_date)
ORDERBY order_date
ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_day_of_month_revenue
FROM daily_revenue;
Frame Specifications
-- Moving averages with precise frame controlSELECT
trade_date,
close_price,
-- 7-day moving averageAVG(close_price) OVER (
ORDERBY trade_date
ROWSBETWEEN6 PRECEDING ANDCURRENTROW
) AS ma_7,
-- 30-day moving averageAVG(close_price) OVER (
ORDERBY trade_date
ROWSBETWEEN29 PRECEDING ANDCURRENTROW
) AS ma_30,
# ... (condensed) ...
WHERE ticker ='AAPL';
-- RANGE vs ROWS: RANGE groups identical ORDER BY values-- ROWS treats each row independently-- GROUPS (PostgreSQL 11+) counts distinct groups
Common Table Expressions (CTEs)
Standard CTEs
-- Named subqueries for readability and reuseWITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) ASmonth,
SUM(amount) AS total_sales
FROM orders
GROUPBY1
),
monthly_growth AS (
SELECTmonth,
total_sales,
LAG(total_sales) OVER (ORDERBYmonth) AS prev_month_sales,
ROUND(
(total_sales -LAG(total_sales) OVER (ORDERBYmonth))
/LAG(total_sales) OVER (ORDERBYmonth) *100, 2
) AS growth_pct
FROM monthly_sales
)
SELECT*FROM monthly_growth WHERE growth_pct <0;
Recursive CTEs
-- Organizational hierarchy traversalWITHRECURSIVE org_tree AS (
-- Base case: top-level managersSELECT
employee_id,
name,
manager_id,
1AS depth,
ARRAY[employee_id] AS path,
name::TEXT AS hierarchy
FROM employees
WHERE manager_id ISNULLUNIONALL
# ... (condensed) ...
SELECT dt +INTERVAL'1 day'FROM date_series
WHERE dt <DATE'2024-12-31'
)
SELECT dt FROM date_series;
Gap and Island Analysis
-- Find consecutive sequences (islands) and gapsWITH numbered AS (
SELECT
event_date,
event_date - (ROW_NUMBER() OVER (ORDERBY event_date))::INT*INTERVAL'1 day'AS grp
FROM events
),
islands AS (
SELECTMIN(event_date) AS island_start,
MAX(event_date) AS island_end,
COUNT(*) AS island_length
FROM numbered
GROUPBY grp
)
SELECT*FROM islands ORDERBY island_start;
Query Optimization
Reading EXPLAIN Plans
-- PostgreSQL: always use ANALYZE for actual execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT*FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >'2024-01-01';
-- Key metrics to examine:-- 1. Seq Scan vs Index Scan (sequential = full table scan)-- 2. Actual rows vs Estimated rows (>10x difference = stale statistics)-- 3. Sort method: external merge = not enough work_mem-- 4. Nested Loop vs Hash Join vs Merge Join-- 5. Buffers: shared hit (cache) vs shared read (disk)
Pattern matching (LIKE 'prefix%') -> B-tree with text_pattern_ops
Pattern matching (LIKE '%middle%') -> GIN with pg_trgm
Composite Index Design
-- Column order matters: most selective first for equality,-- range conditions last-- Rule: equality columns first, then range columns, then sort columns-- For query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY nameCREATE INDEX idx_orders_status_created_name
ON orders (status, created_at, name);
-- Covering index: includes all columns needed, avoiding table lookupCREATE INDEX idx_orders_covering
ON orders (customer_id)
INCLUDE (order_date, total_amount);
-- Partial index: index only relevant rowsCREATE INDEX idx_orders_active
ON orders (customer_id, order_date)
WHERE status ='active';
-- Expression indexCREATE INDEX idx_users_lower_email
ON users (LOWER(email));
Index Maintenance
-- Find unused indexes (PostgreSQL)SELECT
schemaname, tablename, indexname,
idx_scan AS times_used,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan =0AND indexrelid NOTIN (SELECT conindid FROM pg_constraint)
ORDERBY pg_relation_size(indexrelid) DESC;
-- Find missing indexes (tables with high sequential scans)SELECT
schemaname, relname,
seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch,
seq_tup_read / GREATEST(seq_scan, 1) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan >100ORDERBY seq_tup_read DESC;
Pivot and Unpivot Operations
-- PostgreSQL pivot using FILTERSELECT
department,
COUNT(*) FILTER (WHERE status ='active') AS active_count,
COUNT(*) FILTER (WHERE status ='inactive') AS inactive_count,
COUNT(*) FILTER (WHERE status ='pending') AS pending_count
FROM employees
GROUPBY department;
-- Generic pivot using CASESELECT
product_category,
SUM(CASEWHEN quarter ='Q1'THEN revenue END) AS q1_revenue,
SUM(CASEWHEN quarter ='Q2'THEN revenue END) AS q2_revenue,
# ... (condensed) ...
SELECT p.product_id, v.quarter, v.revenue
FROM quarterly_products p
CROSSJOINLATERAL (
VALUES ('Q1', p.q1_rev), ('Q2', p.q2_rev), ('Q3', p.q3_rev), ('Q4', p.q4_rev)
) AS v(quarter, revenue);
JSON Operations
-- PostgreSQL JSONB operations-- Extract valuesSELECT
data->>'name'AS name_text,
data->'address'->>'city'AS city,
data#>>'{address,zip}'AS zip_alt_syntax,
jsonb_array_length(data->'tags') AS tag_count
FROM users;
-- Query inside JSONSELECT*FROM events
WHERE payload @>'{"type": "purchase"}'::jsonb;
-- Aggregate to JSON
# ... (condensed) ...
CROSSJOINLATERAL jsonb_array_elements(o.items) AS item;
-- JSON path queries (PostgreSQL 12+)SELECT*FROM events
WHERE payload @? '$.items[*] ? (@.price > 100)';
Full-Text Search
-- PostgreSQL full-text search setupALTER TABLE articles ADDCOLUMN search_vector tsvector;
UPDATE articles SET search_vector =
setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(abstract, '')), 'B') ||
setweight(to_tsvector('english', COALESCE(body, '')), 'C');
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);
-- Trigger for automatic updatesCREATETRIGGER articles_search_update
BEFORE INSERTORUPDATEON articles
FOREACHROWEXECUTEFUNCTION
# ... (condensed) ...
FROM articles,
to_tsquery('english', 'machine & learning & !supervised') AS query
WHERE search_vector @@ query
ORDERBY rank DESC
LIMIT 20;
Stored Procedures and Functions
-- PostgreSQL: function with proper error handlingCREATEOR REPLACE FUNCTION transfer_funds(
p_from_account BIGINT,
p_to_account BIGINT,
p_amount NUMERIC(15,2)
) RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
v_from_balance NUMERIC(15,2);
v_result JSONB;
BEGIN-- Lock rows in consistent order to prevent deadlocksSELECT balance INTO v_from_balance
# ... (condensed) ...
'message', SQLERRM,
'code', SQLSTATE
);
END;
$$;
N+1 queries: Loop issuing one query per row instead of a single JOIN or IN clause
Functions in WHERE on indexed columns: WHERE YEAR(created_at) = 2024 cannot use index; use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
Implicit type conversions: WHERE varchar_col = 12345 forces full scan; match types explicitly
OR on different columns: WHERE col_a = 1 OR col_b = 2 often forces sequential scan; rewrite as UNION ALL
Correlated subqueries that could be joins: Executes subquery once per outer row
Missing LIMIT on existence checks: Use EXISTS(SELECT 1 ...) not COUNT(*) > 0
Query Rewriting Patterns
-- Anti-pattern: correlated subquerySELECT*FROM orders o
WHERE (SELECTMAX(order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id) = o.order_date;
-- Optimized: window functionSELECT*FROM (
SELECT*, ROW_NUMBER() OVER (PARTITIONBY customer_id ORDERBY order_date DESC) AS rn
FROM orders
) sub WHERE rn =1;
-- Anti-pattern: count for existenceSELECT*FROM customers c
WHERE (SELECTCOUNT(*) FROM orders o WHERE o.customer_id = c.id) >0;
# ... (condensed) ...
JOIN orders o ON c.id = o.customer_id;
-- Optimized: semi-joinSELECT c.*FROM customers c
WHEREEXISTS (SELECT1FROM orders o WHERE o.customer_id = c.id);
Advanced Techniques
GROUPING SETS, CUBE, and ROLLUP
-- Multiple aggregation levels in one passSELECTCOALESCE(region, '(All Regions)') AS region,
COALESCE(product, '(All Products)') AS product,
SUM(revenue) AS total_revenue,
GROUPING(region) AS is_region_total,
GROUPING(product) AS is_product_total
FROM sales
GROUPBYGROUPING SETS (
(region, product), -- detail
(region), -- subtotal by region
(product), -- subtotal by product
() -- grand total
)
ORDERBYGROUPING(region), GROUPING(product), region, product;
Materialized Views
-- Create materialized view for expensive aggregationsCREATE MATERIALIZED VIEW mv_daily_metrics ASSELECT
DATE_TRUNC('day', event_time) ASday,
event_type,
COUNT(*) AS event_count,
COUNT(DISTINCT user_id) AS unique_users,
AVG(duration_ms) AS avg_duration
FROM events
GROUPBY1, 2WITH DATA;
CREATEUNIQUE INDEX ON mv_daily_metrics (day, event_type);
-- Refresh concurrently (requires unique index, no lock on reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_metrics;
Lateral Joins
-- Top-N per group without window functionsSELECT c.customer_name, recent_orders.*FROM customers c
CROSSJOINLATERAL (
SELECT order_id, order_date, total_amount
FROM orders o
WHERE o.customer_id = c.id
ORDERBY order_date DESC
LIMIT 3
) AS recent_orders;
Decision Framework
When approaching a SQL problem:
Correctness first: Write the logically correct query, then optimize
Check the plan: Always run EXPLAIN ANALYZE before and after optimization
Measure, do not guess: Use pg_stat_statements or query store to find actual slow queries
Index with purpose: Every index slows writes; ensure it serves real query patterns
Denormalize deliberately: Only when read patterns demand it, and document why
Test at scale: Queries that are fast on 1000 rows may be catastrophic on 10 million
When to Use
Use this skill when:
Designing or implementing sql master solutions
Reviewing or improving existing sql master approaches
Making architectural or implementation decisions about sql master
Learning sql master patterns and best practices
Troubleshooting sql master-related issues
Do NOT use this skill when:
The question is about a fundamentally different technology domain
A more specific sibling skill covers the exact topic needed
The user needs a complete hands-on tutorial rather than expert guidance
Output Format
# Sql Master Analysis## Context Assessment
[Situation summary and constraints]
## Recommended Approach
[Primary recommendation with rationale]
## Implementation Steps1. [Step with specific details]
2. [Step with specific details]
3. [Step with specific details]
## Trade-offs and Considerations- [Key trade-off 1]
- [Key trade-off 2]
## Next Steps- [Immediate action item]
- [Follow-up action item]
Example
Input: "Help me implement sql master for a medium-scale production application"
Output: A structured analysis covering current state assessment, recommended sql master approach with specific patterns, implementation roadmap with milestones, and risk mitigation strategies tailored to the application scale and constraints.
Edge Cases
Legacy system integration: When sql master must coexist with legacy approaches, provide a gradual migration path rather than a complete rewrite
Scale mismatch: When the solution complexity exceeds the project scale, recommend a simpler approach and note when to revisit
Team skill gaps: When the team lacks experience with the recommended approach, include learning resources and simpler alternatives
Conflicting requirements: When constraints conflict (e.g., performance vs. maintainability), explicitly state the trade-off and recommend based on stated priorities
