How to use the SQL Window Functions Playground
- 1. Pick an exampleChoose a window function example from the list.
- 2. Edit the queryChange the PARTITION BY, ORDER BY or frame and see what moves.
- 3. Run itPress Run query to see the full result table.
How a window function works
A window function calculates across a set of rows related to the current row, without collapsing them like GROUP BY does. The pattern is FUNCTION() OVER (PARTITION BY group ORDER BY sort [frame]). PARTITION BY restarts the calculation for each group, ORDER BY sets the sequence, and the frame (ROWS BETWEEN …) picks which nearby rows to include.
Example from the first preset: Aman sold 42000 in January, 38000 in February and 45000 in March. SUM(amount) OVER (PARTITION BY rep ORDER BY month) gives a running total of 42000, then 80000, then 125000 in March. Bela's total starts again from her own January figure because of PARTITION BY rep.
RANK vs DENSE_RANK vs ROW_NUMBER
In the North region, Aman and Bela both sold 45000 in March. ROW_NUMBER gives them 1 and 2 (a tie is broken arbitrarily), RANK gives both 1 and then skips to 3, and DENSE_RANK gives both 1 and then continues with 2. Interviewers love this question, so run the second example and look at the three columns side by side.
- LAG / LEAD read a value from the previous or next row, perfect for month-on-month change.
- NTILE(n) splits rows into n near-equal buckets.
- FIRST_VALUE returns the first value in the window, such as each rep's best month.
You cannot filter on a window function in WHERE. Wrap it in a subquery, as the last example shows. The engine is SQLite via sql.js 1.10.3 from cdnjs, which supports window functions. It runs on your device only. Practise more with the SQL practice questions.
Frequently asked questions
Do these queries work in MySQL and PostgreSQL?
Yes. MySQL 8+, PostgreSQL, SQL Server and Oracle all support the same OVER() syntax used here. Older MySQL 5.7 does not support window functions.
Can I create my own table?
Yes. Add a CREATE TABLE and INSERT before your SELECT in the editor. It exists only for that run.
Is the SQL Window Functions Playground free?
Yes, the SQL Window Functions Playground is free. The first time you run your first query, you are asked for your name and WhatsApp number once. After that, this and every other Uncodemy tool opens straight away without the form.