Sql Pro

SQL Pro is a community skill for advanced SQL query writing and database optimization, covering complex queries, performance tuning, schema design, window functions, and query analysis for relational database systems.

What Is This?

Overview

SQL Pro provides guidance on writing efficient and maintainable SQL for relational databases. It covers complex queries that combine joins, subqueries, and CTEs for multi-step data retrieval, performance tuning that analyzes execution plans and adds indexes for query optimization, schema design that normalizes tables and defines constraints for data integrity, window functions that compute rankings, running totals, and moving averages over partitioned result sets, and query analysis that identifies slow queries and recommends structural improvements. The skill helps developers write correct and performant SQL.

Who Should Use This

This skill serves backend developers writing database queries, data analysts building complex reports, and database administrators optimizing query performance and schema design.

Why Use It?

Problems It Solves

Poorly written queries scan entire tables instead of using indexes, causing slow response times. Complex business logic expressed as nested subqueries becomes unreadable and hard to maintain. Missing indexes on frequently queried columns degrade performance as data grows. Schema designs without proper normalization lead to data duplication and update anomalies.

Core Highlights

Query builder constructs efficient multi-join queries with proper filtering. Index advisor recommends indexes based on query patterns. Schema designer normalizes tables with appropriate constraints. Window function helper applies analytic computations over result partitions.

How to Use It?

Basic Usage

-- CTE with window functions
WITH monthly_sales AS (
  SELECT
    customer_id,
    DATE_TRUNC(
      'month',
      order_date
    ) AS month,
    SUM(amount)
      AS total
  FROM orders
  WHERE order_date >=
    '2024-01-01'
  GROUP BY
    customer_id,
    DATE_TRUNC(
      'month',
      order_date)
),
ranked AS (
  SELECT
    customer_id,
    month,
    total,
    ROW_NUMBER() OVER (
      PARTITION BY month
      ORDER BY total DESC
    ) AS rank,
    LAG(total) OVER (
      PARTITION BY
        customer_id
      ORDER BY month
    ) AS prev_total
  FROM monthly_sales
)
SELECT
  customer_id,
  month,
  total,
  rank,
  ROUND(
    (total - prev_total)
    / NULLIF(
        prev_total, 0)
    * 100, 1
  ) AS growth_pct
FROM ranked
WHERE rank <= 10
ORDER BY
  month, rank;

Real-World Examples

-- Index analysis query
WITH table_stats AS (
  SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup AS rows
  FROM
    pg_stat_user_tables
),
index_usage AS (
  SELECT
    schemaname,
    relname AS table_name,
    indexrelname
      AS index_name,
    idx_scan AS scans,
    pg_relation_size(
      indexrelid
    ) AS size_bytes
  FROM
    pg_stat_user_indexes
)
SELECT
  t.table_name,
  t.rows,
  t.seq_scan,
  t.idx_scan,
  ROUND(
    t.idx_scan::numeric
    / NULLIF(
      t.seq_scan
      + t.idx_scan, 0)
    * 100, 1
  ) AS idx_hit_pct,
  i.index_name,
  i.scans AS idx_scans,
  pg_size_pretty(
    i.size_bytes
  ) AS idx_size
FROM table_stats t
LEFT JOIN index_usage i
  ON t.table_name =
     i.table_name
WHERE t.rows > 1000
ORDER BY
  t.seq_scan DESC,
  i.scans ASC;

Advanced Tips

Use CTEs to break complex queries into readable named steps that the optimizer can evaluate independently. Analyze EXPLAIN output to identify sequential scans on large tables that need index coverage. Prefer EXISTS over IN for correlated subqueries since the optimizer can short-circuit on the first matching row.

When to Use It?

Use Cases

Write a reporting query that ranks customers by monthly revenue with growth percentages. Identify unused indexes consuming storage and slow queries missing index coverage. Design a normalized schema for a multi-tenant application with proper foreign key constraints.

Related Topics

SQL, PostgreSQL, MySQL, query optimization, database indexing, schema design, window functions, and execution plans.

Important Notes

Requirements

A relational database system such as PostgreSQL, MySQL, or SQL Server. Access to query execution plan tools like EXPLAIN or EXPLAIN ANALYZE for performance analysis. Database permissions for schema inspection and index management operations.

Usage Recommendations

Do: use parameterized queries to prevent SQL injection in application code. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses for frequently executed queries. Write CTEs for complex logic to improve readability and maintainability.

Don't: use SELECT * in production queries since it returns unnecessary columns and prevents covering index usage. Add indexes on every column since each index adds write overhead during inserts and updates. Rely on implicit type conversions in WHERE clauses since they can prevent index usage.

Limitations

Query optimization strategies vary across database engines since each has different optimizer behavior and index types. Performance tuning requires understanding the specific data distribution and access patterns of the application. Window functions and CTEs may have different performance characteristics depending on the database version and configuration.