← Back to App

Window Functions and Nested Joins, in Plain English

Rank rows within a group, run a total down a column, or join three files that were never told about each other — each of these is normally the point where a spreadsheet gives up and a text-to-SQL box starts guessing. Here it is a sentence, and the SQL it produces is on screen before anything runs.

Two Different Kinds of Hard SQL

A window function answers a question about a row's place relative to others — its rank, a running total up to that point, what the previous row held — without collapsing the rows into groups the way GROUP BY does. A nested join is what happens when the table you need is not the table you loaded: it links through one or two others to get there, the way suppliers reaches orders only via products. Both are usually the SQL people look up rather than remember. Here, both are typed in English.

Rankings and Running Totals

Run against a 12-row employees.csv, loaded as E, with department, salary and hire_date columns:

What you type	What you get
rank employees by salary within each department	SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM E
dense rank employees by salary within each department	SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_dense_rank FROM E
show employees with row number ordered by salary	SELECT *, ROW_NUMBER() OVER (ORDER BY salary ASC) AS row_num FROM E
running total of salary ordered by salary	SELECT *, SUM(salary) OVER (ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_salary FROM E
moving average of salary ordered by hire_date	SELECT *, AVG(salary) OVER (ORDER BY hire_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_salary FROM E
lag of salary ordered by hire_date for employees	SELECT *, LAG(salary) OVER (ORDER BY hire_date ASC) AS previous_salary FROM E
lead of salary ordered by hire_date for employees	SELECT *, LEAD(salary) OVER (ORDER BY hire_date ASC) AS next_salary FROM E

RANK and DENSE_RANK default to highest-first; plain ROW_NUMBER with no ranking word defaults to the order you named, ascending. Within each department or per department becomes PARTITION BY — leave it out and the window covers the whole table.

Top N and “Nth” Within Each Group

A rank you filter on compiles to DuckDB's QUALIFY, which runs after the window function instead of before it — there is no subquery to nest:

What you type	What you get
top 2 employees per department by salary	SELECT * FROM E QUALIFY ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) <= 2 ORDER BY department ASC, salary DESC
second highest salary per department	SELECT * FROM E QUALIFY DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) = 2 ORDER BY department ASC
second highest salary	SELECT DISTINCT salary FROM E ORDER BY salary DESC LIMIT 1 OFFSET 1
second highest salary in Sales	SELECT DISTINCT salary FROM E WHERE department = 'Sales' ORDER BY salary DESC LIMIT 1 OFFSET 1
third highest salary	SELECT DISTINCT salary FROM E ORDER BY salary DESC LIMIT 1 OFFSET 2
second highest paid employee	SELECT * FROM E ORDER BY salary DESC LIMIT 1 OFFSET 1
top 3 salaries	SELECT salary FROM E ORDER BY salary DESC LIMIT 3

Notice the difference between the last two rows of the first table and these: per department or within each department needs the window function, because the answer has one row per group. Without a group, a place in a ranking is plain ORDER BY … LIMIT 1 OFFSET n — simpler SQL for the same English pattern, and top 3 salaries is the same sort with no offset. Ask for the salary and you get DISTINCT values, so two people tied for first do not push the real second place out of view; ask for the employee and you get the row itself.

Nested Joins — Found, Not Written

Load customers.csv, orders.csv, products.csv and suppliers.csv as C, O, P and S. Nothing links suppliers to orders directly — the path runs through products — and the question does not have to say so:

What you type	What you get
total amount by city	SELECT C.city, SUM(O.amount) AS total_amount FROM C JOIN O ON C.customer_id = O.customer_id GROUP BY C.city
number of products per supplier	SELECT S.supplier_name, COUNT(*) AS count_products FROM P JOIN S ON P.supplier_id = S.supplier_id GROUP BY S.supplier_name
total amount by supplier name	SELECT S.supplier_name, SUM(O.amount) AS total_amount FROM S JOIN P ON S.supplier_id = P.supplier_id JOIN O ON P.product_id = O.product_id GROUP BY S.supplier_name

That last query is a nested join proper: three tables, two ON clauses, and suppliers never mentioned alongside orders in the sentence at all. Add a fourth file — tickets.csv, linked to customers — and how many tickets by product category reaches across all four:

SELECT P.category, COUNT(*) AS count_tickets
FROM T
JOIN C ON T.customer_id = C.customer_id
JOIN O ON C.customer_id = O.customer_id
JOIN P ON O.product_id = P.product_id
GROUP BY P.category

The path is found the same way a two-file join is: a column name that lines up, checked against whether the values in it actually occur in the other table. Both signals have to agree at every step of the chain, which is what keeps a long join from being guessed. This works across up to 26 loaded files — more on that limit here.

Rows That Do Not Match — Anti-Joins

“Which of these have nothing on the other side” is a LEFT JOIN filtered down to the gap, and several phrasings reach it:

What you type	What you get
customers that have not placed any orders	SELECT * FROM C LEFT JOIN O ON C.customer_id = O.customer_id WHERE O.customer_id IS NULL
orders with no matching customer	SELECT * FROM O LEFT JOIN C ON O.customer_id = C.customer_id WHERE C.customer_id IS NULL
list customers including those without orders	SELECT * FROM C LEFT JOIN O ON C.customer_id = O.customer_id

customers without orders, that have no orders and with no matching orders all land on the same query as the first row. Drop the negative — including those without orders — and the WHERE disappears too: you get every customer, matched rows and gaps both, which is the ordinary meaning of a left join.

Combining Both: Top N Within a Join

A rank does not care whether its table came from one file or three. top 2 orders per customer by amount, against the same C and O files:

SELECT * FROM O
JOIN C ON O.customer_id = C.customer_id
QUALIFY ROW_NUMBER() OVER (PARTITION BY C.name ORDER BY O.amount DESC) <= 2
ORDER BY C.name ASC, O.amount DESC

The join happens first, then the window function partitions the joined result — one sentence, no subquery written by hand.

Typos, Shorthand and Half-Written SQL

Questions like these are rarely typed neatly. A misspelt keyword, dept for department, a half-remembered OVER clause, or the words in another order all come out as exactly the SQL of the tidy question — on the same files as above:

What you type	Same SQL as
rnak employes by salry per dept	rank employees by salary within each department
salary rank department wise	rank employees by salary within each department
rank() over (partition by department order by salary desc)	rank employees by salary within each department
runing totl of salary by hire_date	running total of salary ordered by hire_date
cumsum salary order by hire date	running total of salary ordered by hire_date
prev salary order by hire_date	lag of salary ordered by hire_date for employees
top2 employees per dept by salary	top 2 employees per department by salary
highest 2 salaries in each department	top 2 employees per department by salary
total amt by supplier nm	total amount by supplier name
citywise total amt	total amount by city
customers w/o orders	customers that have not placed any orders
left join customers orders where order is null	customers that have not placed any orders
customers left join orders	list customers including those without orders
top 2 order per custmer by amt	top 2 orders per customer by amount

Four kinds of slip are put right before anything else is read: a misspelt word (rnak, salry, totl — a letter swapped, dropped or changed), shorthand (dept, amt, nm, qty, prev, cumsum, w/o, and department wise for per department), SQL written half-way (OVER, PARTITION BY, a LEFT JOIN with IS NULL), and word order (salary rank as well as rank by salary). A header's shorthand is only expanded when one of your files has that column: amt becomes amount because orders.csv has one.

Only words it could not otherwise read are touched. A column name, a value in your file or a word it already knows stays exactly as typed, and a capitalised word in the middle of a sentence is taken for a name: employees named Rnak looks for someone called Rnak and ranks nothing. Each repair costs a little certainty, and the status above the editor says so — the tidy question shows understood, while rnak employes by salry per dept, with four words repaired, shows fairly sure · 72%. The SQL is on screen before anything runs, so a glance at it is the check.

Where It Stops

Covered	Not covered
ROW_NUMBER, RANK, DENSE_RANK, running totals, a moving average, LAG and LEAD, each with an optional PARTITION BY	NTILE, PERCENT_RANK, CUME_DIST, FIRST_VALUE/LAST_VALUE — DuckDB has all of these, but a plain-English question does not generate them yet. Write them directly in the editor.
Top N and “Nth place” per group, via QUALIFY	A custom window size: “7-day moving average” or “lag by 2 rows” is understood as a request, but the number is not read — the moving average is always the current row and the two before it, and LAG/LEAD always look one row away. Edit the generated OVER (…) clause for anything else.
Joins chained automatically across up to 26 files, and the “no match” anti-join pattern	RIGHT JOIN, FULL JOIN and self-joins (a table joined to itself, for something like a manager column) — write the ON clause yourself; the editor is ordinary DuckDB SQL underneath.
Misspelt words, common shorthand, SQL written half-way, and most word orders — each read as the tidy question	A ranking that never names what is ranked: top 2 by salary per department and 2 highest paid employees per department are read as the two departments with the highest total salary. Say top 2 employees per department by salary. And where one of your files is called orders, order on its own means that file — write order by in full.

When part of a sentence does not map to any of this, the status above the editor drops from understood to fairly sure, or to not sure · check the SQL, rather than claiming the whole sentence was read; a sentence that cannot become SQL at all says which words it did not recognise.

Reasonable Questions

Is this a real SQL engine, or a lookup table of canned queries?

A real one — DuckDB, compiled to WebAssembly and running in the tab. The plain-English box only decides which SQL to write; the query itself runs the same way whether you typed it or the box did.

Can I edit a generated window function or join?

Yes, and it is often the fastest way to get the last 10%: let the sentence write the OVER (…) clause or the join chain, then adjust the frame, add a RIGHT JOIN, or rename the column it produced.

What if I misspell a word, or type half of it as SQL?

Where it can, it reads the question the same as the tidy version — see the table above — and the status says how sure it is. A word it cannot place at all is left out rather than guessed, and the status drops with it.

What if it joins the wrong two files?

It needs a column name that lines up and values that actually overlap, in both files, before it will use a relationship — a name match alone is not enough. If a join still looks wrong, run SELECT * FROM X LIMIT 5 on each file and check what is actually in the key column; the two-file join guide covers the usual causes.

Does ranking or joining my files send anything anywhere?

No. The files, the columns discovered on them, and the SQL built from your question all stay in the browser tab. Here is how to confirm that yourself.

How many files can one nested join reach?

Up to 26, the same limit as any other query here. What that looks like at the edge.

Related Guides

• Plain English to SQL, with no AI
• Join two Excel workbooks without VLOOKUP
• Querying 26 files in one statement
• Full How To Use guide