SQL Cookbook Showcase

Practical PostgreSQL Learning Journey with Measurable Progress

Executive Summary

This page presents my SQL Cookbook project as a structured learning artifact rather than only a code repository. The project uses the Chinook dataset and focuses on practical PostgreSQL patterns, including joins, aggregation, window functions, CTEs, and performance-oriented query design. Each module is mapped to runnable SQL files and validated with query tests.

Project Metrics

• Query Modules27 implemented modules in queries/
• Validation Results27/56 tasks completed (roadmap-aligned verification artifacts)
• Progress CoverageSections A–G complete; H partially complete; I–R pending
• Data ContextChinook schema with customers, invoices, tracks, and playlists

Understanding the Data and Scope

Source and Environment

The exercises are built around the Chinook PostgreSQL dataset and executed in a Supabase/PostgreSQL workflow. SQL modules are versioned as .pgsql files and supported by test specifications and generated reports.

Schema Context

Core relationships used in the exercises include: customer to invoice to invoice_line and track to album to artist, with many-to-many patterns through playlist_track.

Learning Scope

• A. Fundamentals: filters, distinct counts, pagination, and type formatting.
• B. Joins: inner/left joins, many-to-many traversal, anti-join and semi-join patterns.
• C. Aggregation: GROUP BY/HAVING, FILTER, and grouping combinations.
• D. Window Analytics: ranking, lag/lead, running totals, rolling windows, and percentiles.
• E. Advanced Logic: CTEs and recursive CTEs implemented; pivoting complete.
• F. Transformation: Deduplication, composer normalization, and NULL-handling completed.
• G. Indexing & DBA: Targeted, expression, partial, and functional indexes implemented and benchmarked.
• H. EXPLAIN & Profiling: one before/after plan completed (H27); two analysis tasks remain.

Approach

1. Modular Query Design

Each concept is implemented in a dedicated SQL file, making the learning path explicit and easy to review incrementally.

2. Verification by Test Spec

Query behavior is checked against tests/query_test_specs.json, with a generated report that tracks pass/fail outcomes and highlights execution constraints.

3. Proof of Rigor

The latest report shows one failed benchmark script due to VACUUM execution inside a transaction block. This is retained as a useful environment and execution constraint note rather than silently removed.

4. Roadmap Tracking

Planned and completed work is tracked in a dedicated roadmap with task-level checkboxes and deliverables. This keeps the learning process transparent and provides a clear next-step backlog.

Test Automation Framework

The project includes a lightweight but structured SQL test automation framework that validates each module consistently.

• Spec generation: scripts/generate_query_test_specs.py parses query headers and builds tests/query_test_specs.json.
• Execution engine: scripts/run_query_tests.py executes statements per file, evaluates assertions, and rolls back transactions for safe isolation.
• PowerShell entrypoint: scripts/run_query_tests.ps1 runs spec generation and tests in one command.
• Artifacts: markdown and JSON reports are generated in artifacts/query_test_report.md and artifacts/query_test_report.json.
• Driver: Python psycopg[binary] is used for PostgreSQL/Supabase connectivity.

Full Technology Stack

The stack spans data, database, automation, tooling, and reporting components across the full project lifecycle.

• Data and schema: Chinook sample dataset (relational music-commerce schema).
• Database engine: PostgreSQL (Supabase-hosted or compatible local Postgres).
• Query language: SQL in modular .pgsql files.
• Automation language: Python 3 for test-spec generation and query execution.
• Database driver: psycopg with binary package.
• Shell tooling: PowerShell wrapper script for one-command execution.
• Test configuration: JSON spec model with baseline checks and custom assertions.
• Reporting outputs: Markdown and JSON artifacts for human review and tooling integration.
• Documentation: Markdown-based roadmap and testing documentation.
• Development tooling: VS Code workflow and GitHub repository version control.

Deep-Dive Highlight

ROW_NUMBER(), RANK(), and DENSE_RANK() for Top-Per-Genre Analysis

This pattern computes track sales first, then applies a ranking function per genre. It demonstrates how tie behavior changes depending on the selected window function.

WITH sales AS (
  SELECT
    genre.name AS genre,
    track.name AS track_name,
    artist.name AS artist,
    SUM(il.quantity) AS times_sold
  FROM invoice_line il
  JOIN track ON il.track_id = track.track_id
  LEFT JOIN genre ON track.genre_id = genre.genre_id
  LEFT JOIN album ON track.album_id = album.album_id
  LEFT JOIN artist ON album.artist_id = artist.artist_id
  GROUP BY genre.name, track.name, artist.name
), ranked AS (
  SELECT
    genre,
    track_name,
    artist,
    times_sold,
    RANK() OVER (PARTITION BY genre ORDER BY times_sold DESC) AS genre_rank
  FROM sales
)
SELECT genre, track_name, artist, times_sold
FROM ranked
WHERE genre_rank = 1
ORDER BY genre;

Why this matters: this avoids accidental tie-breaking when the analytical requirement is to keep all top performers for a category.

Learning Outcomes

• I can choose ranking functions based on tie semantics and reporting intent.
• I can build customer- and time-based analytics with partitioned window logic.
• I can validate SQL modules with repeatable test specifications and generated reports.
• I can document performance considerations and execution caveats as part of the learning process.

Repository and Artifacts

Main repository: https://github.com/kypnicholas/sql_cookbook

Roadmap: SQL_COOKBOOK_ROADMAP.md

Latest report artifact: query_test_report.md