Create intermediate Works with: Any AI

Window Functions Cookbook for My Schema

Few shot Has example

Learn analytic SQL with examples on your tables.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Teach me window functions using my own tables. Database and version: [DATABASE_AND_VERSION]. Table definitions: [PASTE_SCHEMA]. Business questions I care about: [BUSINESS_QUESTIONS]. My SQL level: [SQL_LEVEL].

Follow this teaching pattern, shown on a made-up orders table:
Goal: rank customers by spend within each country.
Query: SELECT country, customer_id, SUM(total) AS spend, RANK() OVER (PARTITION BY country ORDER BY SUM(total) DESC) AS rank_in_country FROM orders GROUP BY country, customer_id;
How it works: PARTITION BY restarts the ranking for each country; ORDER BY inside OVER sets the order; RANK leaves gaps after ties, DENSE_RANK does not.
Result sketch: Spain, c12, 900, 1; Spain, c7, 900, 1; Spain, c3, 400, 3.
Common mistake: using WHERE on the rank; you must wrap the query and filter outside.

Then give seven recipes using my tables, one each for: ranking and top N per group, running totals, moving averages, comparing a row with the previous row (LAG), first and last value per group, percent of total, and de-duplicating by keeping the latest row. For each: the question, the query, a plain explanation, a tiny result sketch, a pitfall, and a performance note. End with a cheat table of the frame clauses (ROWS vs RANGE) and exercises with answers hidden at the end. Note dialect differences.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Recipe 1: Top 3 products per category
SELECT * FROM (SELECT category, product, revenue, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn FROM product_sales) t WHERE rn <= 3;
How it works: PARTITION BY restarts numbering for each category; ORDER BY sets the ranking; the filter must happen outside.
Result sketch: Shoes, Runner X, 91k, 1; Shoes, Trail Y, 70k, 2.
Pitfall: use RANK instead if ties should share a position. Performance: an index on (category, revenue) can help.
Recipe 2: Running total
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total.

• window-functions
• learning