PostgreSQL Advanced Queries Skill

Atomic skill for complex query patterns

Overview

Production-ready patterns for CTEs, window functions, recursive queries, and advanced joins.

Prerequisites

• PostgreSQL 16+
• Intermediate SQL knowledge

Parameters

parameters:
  query_type:
    type: string
    required: true
    enum: [cte, window, recursive, lateral, aggregate]
  tables:
    type: array
    items: { type: string }

Quick Reference

CTE Pattern

WITH step1 AS (SELECT ...), step2 AS (SELECT ... FROM step1)
SELECT * FROM step2;

Window Functions

ROW_NUMBER() OVER (PARTITION BY cat ORDER BY date DESC)
SUM(amount) OVER (ORDER BY date)  -- Running total
LAG(value, 1) OVER (ORDER BY date)  -- Previous row

Recursive Query

WITH RECURSIVE tree AS (
    SELECT id, parent_id, 1 as level FROM items WHERE parent_id IS NULL
    UNION ALL
    SELECT i.id, i.parent_id, t.level + 1 FROM items i JOIN tree t ON i.parent_id = t.id
)
SELECT * FROM tree;

LATERAL Join

SELECT u.*, r.* FROM users u
CROSS JOIN LATERAL (SELECT * FROM orders WHERE user_id = u.id LIMIT 3) r;

Test Template

DO $$ DECLARE result NUMERIC; BEGIN
    CREATE TEMP TABLE test_sales (id INT, amount NUMERIC);
    INSERT INTO test_sales VALUES (1, 100), (2, 200);
    SELECT SUM(amount) OVER (ORDER BY id) INTO result FROM test_sales WHERE id = 2;
    ASSERT result = 300, 'Running total should be 300';
    DROP TABLE test_sales;
END $$;

Troubleshooting

Usage

Skill("postgresql-advanced-queries")