Back to Cookbook

Window Wizard

Get the right window function without the headache

Describe what you want in plain English — "running total by region, resetting each quarter" — and get the exact window function syntax with an explanation of every clause. No more guessing at PARTITION BY, ORDER BY, and frame specs.

CommunitySubmitted by CommunityWork1 min

PROMPT

Create a skill called "Window Wizard". When I describe a calculation in plain English, generate the correct SQL window function. Always explain each part: what PARTITION BY is grouping, what ORDER BY is sorting, and what the frame specification (ROWS/RANGE BETWEEN) is including. Show a visual example with sample data — a small table showing the input rows and, for each row, which other rows fall within its window frame and what the calculated result is. Warn about common mistakes: default RANGE vs ROWS behavior, NULL ordering effects, and RANK vs DENSE_RANK edge cases. Adapt syntax to my database dialect if I specify one. If my description is ambiguous, ask clarifying questions rather than guessing.

How It Works

Window functions are the most powerful and the most confusing part of SQL.

PARTITION BY vs GROUP BY, ROW_NUMBER vs RANK vs DENSE_RANK, ROWS vs RANGE,

UNBOUNDED PRECEDING vs CURRENT ROW — the combinations are endless and the

bugs are subtle. Describe what you want in plain language and get correct,

tested SQL.

What You Get

  • Correct window function from a plain-English description
  • Explanation of each clause (PARTITION BY, ORDER BY, frame specification)
  • Visual example showing how the window operates on sample data
  • Warnings about common pitfalls (e.g., RANGE vs ROWS default behavior)
  • Dialect-specific syntax for your database

Setup Steps

  1. Ask your Claw to create a "Window Wizard" skill with the prompt below
  2. Describe what you want in plain English
  3. Specify your database if it matters (Postgres, BigQuery, Snowflake, etc.)
  4. Get back the SQL with a visual walkthrough

Tips

  • Great for learning — the visual examples show exactly what each clause does
  • Use it when you're unsure whether you need ROW_NUMBER, RANK, or DENSE_RANK
  • The pitfall warnings prevent the subtle bugs that produce "correct-looking but wrong" results
  • Pairs well with Query Surgeon for optimizing window function performance
Tags:#sql#window-functions#learning#productivity