Skip to content
v0.1.0latest release21 skills2 categories3 sub-categories22 tested scripts68 reference docsContact us
Vector Agentic Skills
Submit a skill

SQL Query Explainer Examples

Reference for sql-query-explainer · 521 lines

Programming / SQLv1.0.0Updated OCT 20 20261 reference1 script

Complex query explanations with detailed breakdowns.

Example 1: Customer Segmentation Query

Query

sql
WITH customer_metrics AS (
    SELECT 
        c.id AS customer_id,
        c.name,
        c.email,
        COUNT(DISTINCT o.id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
        MAX(o.order_date) AS last_order_date,
        MIN(o.order_date) AS first_order_date
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id 
        AND o.status = 'completed'
    WHERE c.created_at >= '2023-01-01'
    GROUP BY c.id, c.name, c.email
),
segmented_customers AS (
    SELECT 
        *,
        CASE 
            WHEN lifetime_value >= 10000 THEN 'VIP'
            WHEN lifetime_value >= 5000 THEN 'Gold'
            WHEN lifetime_value >= 1000 THEN 'Silver'
            WHEN order_count > 0 THEN 'Bronze'
            ELSE 'Prospect'
        END AS segment,
        CURRENT_DATE - last_order_date AS days_since_last_order
    FROM customer_metrics
)
SELECT 
    segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(lifetime_value), 2) AS avg_lifetime_value,
    ROUND(AVG(order_count), 1) AS avg_orders,
    ROUND(AVG(days_since_last_order), 0) AS avg_days_inactive
FROM segmented_customers
GROUP BY segment
ORDER BY 
    CASE segment 
        WHEN 'VIP' THEN 1 
        WHEN 'Gold' THEN 2 
        WHEN 'Silver' THEN 3 
        WHEN 'Bronze' THEN 4 
        ELSE 5 
    END;

Purpose

Segments customers into tiers (VIP, Gold, Silver, Bronze, Prospect) based on their lifetime value and provides aggregate statistics for each segment.

Step-by-Step Breakdown

CTE 1: customer_metrics

  1. FROM customers c LEFT JOIN orders o - Start with all customers, attach their completed orders (LEFT JOIN keeps customers even without orders)
  2. AND o.status = 'completed' - Only count completed orders (in JOIN condition to preserve LEFT JOIN behavior)
  3. WHERE c.created_at >= '2023-01-01' - Only customers created in 2023 or later
  4. GROUP BY c.id, c.name, c.email - One row per customer
  5. Aggregations:
    • COUNT(DISTINCT o.id) - Number of orders
    • COALESCE(SUM(o.total_amount), 0) - Total spent (0 if no orders)
    • MAX(o.order_date) - Most recent order
    • MIN(o.order_date) - First order

CTE 2: segmented_customers

  1. Takes all columns from customer_metrics
  2. CASE WHEN... - Assigns segment based on lifetime value thresholds
  3. CURRENT_DATE - last_order_date - Calculates days since last purchase

Final Query

  1. GROUP BY segment - Aggregate by customer segment
  2. COUNT(*) - Customers in each segment
  3. AVG(...) - Average metrics per segment
  4. ORDER BY CASE... - Custom sort order (VIP first)

Data Flow

text
customers (all) ──┬──> LEFT JOIN with completed orders
                  │
                  ▼
          customer_metrics
          (one row per customer with aggregates)
                  │
                  ▼
          segmented_customers
          (add segment label + days inactive)
                  │
                  ▼
          Final aggregation by segment

Key Concepts

  • CTEs for multi-step logic
  • LEFT JOIN to keep customers without orders
  • COALESCE for NULL handling
  • CASE expressions for categorization
  • Custom ORDER BY with CASE

Example 2: Running Balance Query

Query

sql
SELECT 
    t.id,
    t.transaction_date,
    t.description,
    t.type,
    t.amount,
    SUM(
        CASE 
            WHEN t.type = 'credit' THEN t.amount 
            ELSE -t.amount 
        END
    ) OVER (
        PARTITION BY t.account_id 
        ORDER BY t.transaction_date, t.id
        ROWS UNBOUNDED PRECEDING
    ) AS running_balance
FROM transactions t
WHERE t.account_id = 12345
  AND t.status = 'posted'
ORDER BY t.transaction_date, t.id;

Purpose

Shows all transactions for an account with a running balance that accumulates from the first transaction to each row.

Step-by-Step Breakdown

  1. FROM transactions t WHERE account_id = 12345 AND status = 'posted'

    • Get all posted transactions for account 12345

  2. SELECT t.id, t.transaction_date, ...

    • Basic transaction details

  3. CASE WHEN t.type = 'credit' THEN t.amount ELSE -t.amount END

    • Convert debits to negative values for balance calculation

  4. SUM(...) OVER (...)

    • Window function to calculate cumulative sum

  5. PARTITION BY t.account_id

    • Calculate separately for each account (redundant here with WHERE, but useful in general)

  6. ORDER BY t.transaction_date, t.id

    • Process transactions in chronological order, use ID as tiebreaker

  7. ROWS UNBOUNDED PRECEDING

    • Sum all rows from the first row up to the current row

Data Flow

text
Transaction   Date        Type    Amount    Running Balance
─────────────────────────────────────────────────────────
1001          2024-01-01  credit  1000.00   1000.00
1002          2024-01-05  debit   250.00    750.00
1003          2024-01-10  credit  500.00    1250.00
1004          2024-01-15  debit   100.00    1150.00

Key Concepts

  • Window functions with OVER clause
  • Running/cumulative aggregations
  • Frame specification (ROWS UNBOUNDED PRECEDING)
  • CASE for conditional calculations
  • Multiple ORDER BY columns

Example 3: Hierarchical Organization Query

Query

sql
WITH RECURSIVE org_hierarchy AS (
    SELECT 
        id,
        name,
        title,
        manager_id,
        1 AS level,
        name AS path,
        ARRAY[id] AS ancestors
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.title,
        e.manager_id,
        oh.level + 1,
        oh.path || ' > ' || e.name,
        oh.ancestors || e.id
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT 
    REPEAT('  ', level - 1) || name AS name_indented,
    title,
    level,
    path
FROM org_hierarchy
ORDER BY ancestors;

Purpose

Displays the entire organizational hierarchy as a tree, showing each employee's level and reporting path.

Step-by-Step Breakdown

Base Case (Anchor)

sql
SELECT ... FROM employees WHERE manager_id IS NULL
  • Find all top-level employees (no manager = CEO/executives)
  • Initialize level at 1
  • Start path with just their name
  • Start ancestors array with their ID

Recursive Case

sql
SELECT ... FROM employees e JOIN org_hierarchy oh ON e.manager_id = oh.id
  • Find employees whose manager is already in the result
  • Increment level by 1
  • Append current name to path
  • Append current ID to ancestors array

Termination

  • Recursion stops when no more employees have a manager in the current result set

Final SELECT

  • REPEAT(' ', level - 1) - Indentation based on level
  • ORDER BY ancestors - Orders by path from root, maintaining tree structure

Data Flow

text
Iteration 0 (Base):
┌────┬───────────┬───────┬──────────────┐
│ id │ name      │ level │ ancestors    │
├────┼───────────┼───────┼──────────────┤
│ 1  │ CEO       │ 1     │ [1]          │
└────┴───────────┴───────┴──────────────┘

Iteration 1:
┌────┬───────────┬───────┬──────────────┐
│ id │ name      │ level │ ancestors    │
├────┼───────────┼───────┼──────────────┤
│ 1  │ CEO       │ 1     │ [1]          │
│ 2  │ VP Sales  │ 2     │ [1,2]        │
│ 3  │ VP Eng    │ 2     │ [1,3]        │
└────┴───────────┴───────┴──────────────┘

Final Output:
CEO
  VP Sales
    Sales Manager
      Sales Rep 1
  VP Engineering
    Dev Manager
      Developer 1

Key Concepts

  • Recursive CTEs for hierarchical data
  • UNION ALL to combine base and recursive cases
  • Array building for path tracking
  • String concatenation for readable paths
  • Ordered output preserving tree structure

Example 4: Gap Analysis Query

Query

sql
WITH daily_sales AS (
    SELECT 
        DATE(order_date) AS sale_date,
        SUM(total_amount) AS daily_total
    FROM orders
    WHERE order_date >= '2024-01-01' 
      AND order_date < '2024-02-01'
      AND status = 'completed'
    GROUP BY DATE(order_date)
),
date_series AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-01-31'::date,
        '1 day'::interval
    )::date AS date
),
complete_data AS (
    SELECT 
        ds.date,
        COALESCE(s.daily_total, 0) AS daily_total,
        EXTRACT(DOW FROM ds.date) AS day_of_week,
        CASE 
            WHEN EXTRACT(DOW FROM ds.date) IN (0, 6) THEN 'Weekend'
            ELSE 'Weekday'
        END AS day_type
    FROM date_series ds
    LEFT JOIN daily_sales s ON ds.date = s.sale_date
)
SELECT 
    date,
    daily_total,
    day_type,
    AVG(daily_total) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg,
    daily_total - LAG(daily_total) OVER (ORDER BY date) AS day_over_day_change
FROM complete_data
ORDER BY date;

Purpose

Analyzes daily sales for January 2024, filling in gaps for days with no sales, and calculating rolling averages and day-over-day changes.

Step-by-Step Breakdown

CTE 1: daily_sales

  • Aggregates orders by date for completed orders in January 2024

CTE 2: date_series

  • generate_series() creates a row for every date in January
  • This ensures no gaps in the output

CTE 3: complete_data

  • LEFT JOIN ensures every date appears, even without sales
  • COALESCE(..., 0) replaces NULL with 0 for missing days
  • Extracts day of week and categorizes as weekend/weekday

Final Query

  • AVG(...) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) - 7-day rolling average
  • LAG(daily_total) OVER (ORDER BY date) - Previous day's total for comparison

Data Flow

text
date_series:          daily_sales:           complete_data:
2024-01-01            2024-01-01: $1000      2024-01-01: $1000
2024-01-02            2024-01-03: $1500      2024-01-02: $0 (gap filled)
2024-01-03            ...                    2024-01-03: $1500
...                                          ...

Key Concepts

  • generate_series for date ranges
  • Gap filling with LEFT JOIN
  • COALESCE for NULL replacement
  • Window function frames for rolling calculations
  • LAG for row comparisons

Example 5: Deduplication with Ranking

Query

sql
WITH ranked_duplicates AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY 
                CASE WHEN email_verified THEN 0 ELSE 1 END,
                last_login_at DESC NULLS LAST,
                created_at ASC
        ) AS dup_rank
    FROM users
    WHERE status != 'deleted'
)
SELECT 
    id,
    email,
    name,
    email_verified,
    last_login_at,
    created_at,
    CASE 
        WHEN dup_rank = 1 THEN 'Keep'
        ELSE 'Duplicate'
    END AS action
FROM ranked_duplicates
ORDER BY email, dup_rank;

Purpose

Identifies duplicate user accounts by email and marks which one to keep based on verification status, login recency, and creation date.

Step-by-Step Breakdown

CTE: ranked_duplicates

  1. PARTITION BY email - Group duplicates by email
  2. ORDER BY prioritization:
    • CASE WHEN email_verified... - Verified accounts first (0 before 1)
    • last_login_at DESC NULLS LAST - Most recent login, NULLs at end
    • created_at ASC - Older account preferred (first created)
  3. ROW_NUMBER() - Assigns 1 to the "best" record, 2+ to duplicates

Final Query

  • dup_rank = 1 means "Keep" (the primary record)
  • dup_rank > 1 means "Duplicate" (can be merged/deleted)

Data Flow

text
email: john@example.com
┌────┬─────────────────┬──────────┬─────────────┬──────────┐
│ id │ email           │ verified │ last_login  │ dup_rank │
├────┼─────────────────┼──────────┼─────────────┼──────────┤
│ 42 │ john@example.com│ true     │ 2024-01-15  │ 1 (Keep) │
│ 15 │ john@example.com│ false    │ 2024-01-10  │ 2 (Dup)  │
│ 78 │ john@example.com│ false    │ NULL        │ 3 (Dup)  │
└────┴─────────────────┴──────────┴─────────────┴──────────┘

Key Concepts

  • ROW_NUMBER for deduplication
  • Complex ORDER BY with CASE
  • NULLS LAST handling
  • PARTITION BY for grouping without aggregation

Example 6: Pivot/Cross-Tab Query

Query

sql
SELECT 
    product_category,
    SUM(CASE WHEN sale_month = 1 THEN revenue ELSE 0 END) AS jan_revenue,
    SUM(CASE WHEN sale_month = 2 THEN revenue ELSE 0 END) AS feb_revenue,
    SUM(CASE WHEN sale_month = 3 THEN revenue ELSE 0 END) AS mar_revenue,
    SUM(CASE WHEN sale_month = 4 THEN revenue ELSE 0 END) AS apr_revenue,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT order_id) AS total_orders
FROM (
    SELECT 
        p.category AS product_category,
        EXTRACT(MONTH FROM o.order_date) AS sale_month,
        oi.quantity * oi.unit_price AS revenue,
        o.id AS order_id
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    JOIN products p ON oi.product_id = p.id
    WHERE o.order_date >= '2024-01-01' 
      AND o.order_date < '2024-05-01'
      AND o.status = 'completed'
) sales_data
GROUP BY product_category
ORDER BY total_revenue DESC;

Purpose

Creates a pivot table showing revenue by product category for each month, transforming rows into columns.

Step-by-Step Breakdown

Subquery: sales_data

  • Joins orders, order_items, and products
  • Calculates revenue per line item
  • Extracts month number for pivoting

Outer Query

  • GROUP BY product_category - One row per category
  • SUM(CASE WHEN sale_month = N...) - Conditional aggregation creates columns
  • Each month becomes a separate column

Data Flow

text
Raw data (rows):
┌───────────┬───────┬─────────┐
│ category  │ month │ revenue │
├───────────┼───────┼─────────┤
│ Electronics│ 1    │ 500     │
│ Electronics│ 1    │ 300     │
│ Electronics│ 2    │ 450     │
│ Clothing  │ 1     │ 200     │
└───────────┴───────┴─────────┘

Pivoted (columns):
┌───────────┬─────┬─────┬───────┐
│ category  │ Jan │ Feb │ Total │
├───────────┼─────┼─────┼───────┤
│ Electronics│ 800│ 450 │ 1250  │
│ Clothing  │ 200 │ 0   │ 200   │
└───────────┴─────┴─────┴───────┘

Key Concepts

  • Conditional aggregation for pivoting
  • CASE expressions inside aggregate functions
  • Subquery for data preparation
  • Manual pivot (vs. PIVOT operator in some dialects)
More in SQL

sql-analytics

Analytical and aggregation SQL — GROUP BY, ROLLUP, CUBE, GROUPING SETS, pivots, cohorts, funnels, retention, percentiles and statistical functions.

sql-data-quality

Profile and validate data with SQL — NULL analysis, duplicate detection, referential integrity, range and format checks, outliers, constraints and cleansing queries.

sql-debugger

Debug failing SQL or queries returning wrong results — interpret error messages, find causes of duplicates, missing rows, NULL surprises, fan-out joins and type mismatches, and produce a fixed query.

sql-dialect-translator

Translate SQL between dialects — PostgreSQL, MySQL, SQL Server (T-SQL), Oracle, SQLite — covering data types, functions, pagination, date handling, upserts and identity columns.

sql-optimizer

Rewrite slow SQL queries for performance — read execution plans (EXPLAIN / EXPLAIN ANALYZE), remove anti-patterns, make predicates sargable, choose JOIN/EXISTS strategies and suggest indexes.

sql-performance-tuning

Database-level SQL performance tuning — index design and maintenance, query profiling, statistics, partitioning, schema design, locking and configuration bottlenecks.