From a question to a query: a method

Part of the free LogicWiz SQL & Databases course, Module 6: Subqueries and set operations. The queries and practice run in the app on real Postgres.

Aarav's new dashboard lists every restaurant with its delivered orders. It runs without an error, the numbers look right, and his manager approves it. Then Pizza Corner's owner calls: "Why are we not on the list?" The query had quietly dropped a row, and nothing warned anyone.

You'll learn to

• Turn an English question into a query with the same six steps every time
• Say the grain of a result ("one row per what?") and let it choose your GROUP BY
• Decide when a join must be LEFT, and where its filters go
• Check a result by counting rows and catching double counting

Learn

The mistakes that do not show up

A typo is the easy kind of mistake: Postgres stops you with an error. The dangerous kind runs fine and gives a believable answer.

Researchers who studied about 1.6 lakh query attempts by students found that most wrong answers were logic errors, not typing errors. Most were omissions in queries that needed a join, a subquery or GROUP BY:

• a missing join, or a missing join condition
• a grouping column missing, or one too many
• rows with no match, quietly dropped

You already know every clause. What is missing is a routine for deciding when to group and when to join. Here is one, in six steps, for every question.

Six steps from a question to a query

Steps 1 to 4 are thinking, not typing. Write them as -- comments above your query; it takes one minute and saves twenty. This routine closes the Basic level: every clause you learn from here on fits into one of these steps.

Step 1: the grain decides almost everything

The grain of a result is what one row stands for. Say it out loud before you type anything:

• "Delivered orders per restaurant": one row per restaurant, so GROUP BY r.restaurant_id, r.name, and you expect 8 rows.
• "Dishes sold per restaurant per category": one row per (restaurant, category) pair, so both go in GROUP BY.
• "Cities that have a premium customer": one row per city, so DISTINCT city or GROUP BY city.

Group by the key (restaurant_id), not only the name: two restaurants can share a name. And the grain gives you a number to check your result against at the end.

Worked example 1: zeros included

-- Question: every restaurant with its number of delivered orders,
-- including restaurants with none.
-- 1. Grain: one row per restaurant. Expect as many rows as restaurants.
SELECT COUNT(*) AS expected_rows
FROM restaurants;

-- 2. Tables: restaurants, orders (orders.restaurant_id -> restaurants).
-- 3. Restaurants with no match must stay: LEFT JOIN.
-- 4. "delivered" is about the right-hand table, so it goes in ON.
--    No group filter. Count the joined rows before grouping:
SELECT COUNT(*) AS joined_rows
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
                  AND o.status = 'delivered';

-- 5. Group to the grain, count a column of orders, sort with a tie-breaker.
SELECT r.name, COUNT(o.order_id) AS delivered
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
                  AND o.status = 'delivered'
GROUP BY r.restaurant_id, r.name
ORDER BY delivered DESC, r.name;

6. Check. 8 restaurants expected. The join gives 21 rows: 20 delivered orders, plus one row for Pizza Corner with NULLs. The final result has 8 rows, the counts add up to 20, and Pizza Corner shows 0. Every number agrees with another one, so you can trust it.

The hook's query used LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id and then WHERE o.status = 'delivered'. It returns 7 rows. WHERE runs after the join, Pizza Corner's status is NULL, NULL = 'delivered' is not true, and the row is thrown away: the LEFT JOIN has quietly become an inner join. No error, believable numbers. Step 1 said 8 rows, step 6 counted 7, and that gap is the bug. The fix is step 3: a filter on the right-hand table of a LEFT JOIN goes in ON.

Worked example 2: above the average customer

-- Question: which customers spent more on food (delivered orders only)
-- than the average customer who has a delivered order?
-- 1. Grain: one row per customer.
-- 2. Tables: customers -> orders -> order_items -> menu_items (for price).
-- 3. Only customers who spent something: inner joins are right here.
-- 4. Row filter: delivered (WHERE). Group filter: above average (HAVING).

-- Step A: spend per customer. Check: 10 customers have a delivered order.
SELECT c.name, SUM(oi.qty * m.price) AS spend
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN menu_items m ON m.item_id = oi.item_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY spend DESC, c.name;

-- Step B: the same, keeping only groups above the average of Step A.
SELECT c.name, SUM(oi.qty * m.price) AS spend
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN menu_items m ON m.item_id = oi.item_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.name
HAVING SUM(oi.qty * m.price) > (
  SELECT AVG(t.spend)
  FROM (SELECT o.customer_id, SUM(oi.qty * m.price) AS spend
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.order_id
        JOIN menu_items m ON m.item_id = oi.item_id
        WHERE o.status = 'delivered'
        GROUP BY o.customer_id) AS t
)
ORDER BY spend DESC, c.name;

Step A gives 10 rows; Diya and Arjun never ordered. The derived table in brackets is Step A again, and AVG of it is ₹1,104. Step B keeps 4 customers. Check one by hand: Rohan's ₹1,140 is just above ₹1,104, so he stays. If the question had meant all 12 customers, Diya and Arjun would count as ₹0, and step 3 would need a LEFT JOIN. When a question can be read two ways, ask.

Step 6: test one case by hand

-- Kabir (customer 3) has 3 delivered orders. His delivery fees, from orders alone:
SELECT SUM(delivery_fee) AS fees
FROM orders
WHERE customer_id = 3
  AND status = 'delivered';

-- The same sum after joining order_items, as a spend query would:
SELECT SUM(o.delivery_fee) AS fees
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.customer_id = 3
  AND o.status = 'delivered';

₹40 by hand, ₹120 after the join. Order 1004 has 3 lines in order_items, so its ₹40 fee is added 3 times. This is fan-out (Module 5): a fee lives at the grain of the order, but the joined rows are at the grain of the order line. Count rows before and after each join; if the count grows, ask whether your sums should grow with it.

Steps 3 and 4: where does each condition go?

In an interview, say the grain before you type: "one row per customer, so I will group by customer_id." Then name the tables, the join path and the join kind. Interviewers grade your thinking as much as your SQL, and saying it aloud lets them correct a wrong reading of the question before you write ten lines. Finish with a check out loud: "8 restaurants in, 8 rows out."

In one line

Before writing: say the grain, list the tables and keys, choose each join (LEFT if unmatched rows stay, filters in ON), split WHERE from HAVING, write SELECT last, then check the row count.