Text-to-SQL on a Real Database: Two Questions, Five-Table Joins

The question your manager asks

Which film categories make us the most money, and how much per rental?

Reasonable question. Now write it:

• the money is in payment;
• what was rented is in rental;
• which film that was — only through inventory;
• which category it belongs to — only through film_category, a junction table, and then category.

Five tables, four joins — and two of those hops you would only find by reading the schema first.

I typed the question into DBConvert Streams instead.

SELECT c.name AS category,
       COUNT(p.rental_id) AS num_rentals,
       SUM(p.amount) AS total_revenue,
       ROUND(SUM(p.amount) / COUNT(p.rental_id), 2) AS revenue_per_rental
FROM payment p
JOIN rental r         ON p.rental_id = r.rental_id
JOIN inventory i      ON r.inventory_id = i.inventory_id
JOIN film_category fc ON i.film_id = fc.film_id
JOIN category c       ON fc.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY total_revenue DESC;

Sixteen rows, 53 milliseconds. And an actual finding sitting in them: Sports brings in the most money overall — $5,314 — but Comedy earns more per rental, $4.66 against Sports' $4.51. Nobody asked for the per-rental column in SQL terms; it came from the words "how much per rental".

Worth noticing: not that a machine produced SQL, but that it produced the reporting query — derived column, grouping and ordering already right.

A harder one: a date range nobody defined

Second question, deliberately vaguer:

Which customers spent the most last summer, and what did they rent?

"Last summer" is not a column. Neither is "what did they rent" — that is a list, and lists do not fall out of a GROUP BY on their own.

SELECT c.first_name, c.last_name,
       SUM(p.amount) AS total_spent,
       COUNT(DISTINCT p.rental_id) AS rentals,
       GROUP_CONCAT(DISTINCT f.title ORDER BY f.title SEPARATOR ', ') AS films_rented
FROM payment p
JOIN customer c  ON c.customer_id = p.customer_id
JOIN rental r    ON r.rental_id = p.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f      ON f.film_id = i.film_id
WHERE p.payment_date >= '2005-06-01 00:00:00'
  AND p.payment_date <  '2005-09-01 00:00:00'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 10;

Two decisions in there that were not specified anywhere in the question:

• "Last summer" became a date window — June to September, anchored to 2005: the summer this data actually contains, not the one on the wall calendar.
• "What did they rent" became a list — GROUP_CONCAT over film titles, one readable line per customer.

Ten rows, 73 ms. Eleanor Hunt, $211.55 across 45 rentals, with the titles beside her name.

Why it can do this

Because it is not guessing from your sentence. Press ⌘K in the DBConvert Streams SQL console and the request goes to an agent that holds tools against the connected database:

Sakila is a well-known sample database, so the model may already know its schema. DBConvert Streams does not rely on that: it inspects the tables in the connected database before generating SQL. That is what makes the same thing work on your schema, which no model has memorised.

The loop behind those two answers is short, and worth knowing because every step of it is visible to you:

1. You type the question in the editor.
2. It goes to an agent that holds tools against the connected database, not a copy of your DDL in a prompt.
3. The agent reads the parts of the schema it needs — that is the describe_table call above; in chat it will also list tables or run a read-only SELECT to check itself.
4. The SQL lands in your editor, highlighted, with Keep and Revert. Your previous query is not overwritten.
5. You run it. The generated query is ordinary SQL from that point on — editable, re-runnable, yours.

Nothing is hidden at the end of that: the query you are about to trust is on screen in full, which is the whole reason the next section is possible.

The bar above the editor keeps the accounting in view too — tokens sent and returned, how much of the context window the request used, and what it cost. Schema-aware generation is not free of context; on a wide schema it helps to know how close you are to filling it.

If you want the mechanics of how an agent gets that access in the first place, that is a separate piece: what a good database MCP server gets right.

Read the WHERE clause

Go back to the second query for a second. Nobody said June. Nobody said 2005.

"Last summer" was my phrase, and it got turned into payment_date >= '2005-06-01' AND payment_date < '2005-09-01' — a decision about what I meant, made silently, and a defensible one. But it is still a decision, and the ten rows underneath it do not carry a footnote saying so.

That is the honest catch with text-to-SQL, and it has nothing to do with the model being weak. Vague questions are the normal case. Whenever your words leave room — a date range, "active" customers, "top" anything — the model has to resolve them before you see the result.

The SQL is sitting right there on screen. Read the WHERE clause before the number goes into a slide.

The same habit pays off when the SQL is not new but ported: translating a query between MySQL and PostgreSQL fails in exactly this quiet way — it runs, and the numbers move.