Create intermediate Works with: Any AI

SQL Window Functions Explained With Examples From Your Data

Table output Has example

• 🗄️ SQL

Learn SQL window functions using your own tables, with ROW_NUMBER, RANK, running totals and LAG explained step by step with sample output.

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

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Explain SQL window functions to me using my own kind of data. Database: [DATABASE_ENGINE] ([VERIFY] syntax for this engine and version). My tables and columns: [TABLE_SCHEMA]. Business questions I want to answer: [BUSINESS_QUESTIONS]. My SQL level: [SQL_LEVEL].

Teach in this order, one short section each, always with a query on my tables and a small invented sample result:

1. What a window function is compared with GROUP BY (the key difference: rows are kept).

2. PARTITION BY and ORDER BY inside OVER, explained with an analogy.

3. ROW_NUMBER, RANK and DENSE_RANK, with a tie in the data to show the difference.

4. Running totals and moving averages using a frame clause (ROWS BETWEEN), with the arithmetic shown for three rows.

5. LAG and LEAD for comparing to the previous or next row, such as month-on-month change.

6. Top N per group using a subquery or CTE.

7. NTILE and percentage of total.

End with a table of Business question, Function to use, Query pattern, Common mistake, and an exercise set of 5 questions on my schema with an answer key at the end.

Use only my columns and clearly label any invented sample data. Do not claim queries were executed. Mark engine-specific features [VERIFY]. Keep it friendly and avoid jargon.

Add a worked example with invented figures: monthly sales of 100, 120, 90 and a running total plus the change from the previous month.

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.

Invented data: monthly sales for one store
Jan 100 | Feb 120 | Mar 90

Query pattern
SELECT month, sales,
  SUM(sales) OVER (ORDER BY month) AS running_total,
  sales - LAG(sales) OVER (ORDER BY month) AS change_vs_prev
FROM monthly_sales;

Result
Jan: 100, running 100, change NULL (no previous row)
Feb: 120, running 220 (100 + 120), change +20
Mar: 90, running 310 (220 + 90), change -30

Mistake to avoid: forgetting ORDER BY inside OVER makes the running total the same on every row in some engines. [VERIFY] your engine.

• worksheet