Drills

Return the id and total of every order over 1000, most expensive first. Two columns, and an order the query actually promises.

For each order, return its id and a label: 'big' when the total is over 1000, 'unknown' when there is no total at all, and 'small' otherwise. Order by id. Call the second column size.

Return the ids of every order with no total, in ascending order.

Return the names of every customer who is not signed up to the newsletter — including the ones who never answered. Order by name.

Return the names of every customer with no orders at all, ordered by name.

Return each city and the total of its orders, biggest spender first, then by city name. Call the second column spent. Cities with no orders are not wanted.

For each customer id that has orders, return the id, how many orders they placed, and how many of those have a total. Call the columns customer_id, placed and priced. Order by customer_id.

Return each city with more than five shipped orders, and that count, busiest first then by city. Call the count shipped_orders.

Return the id and total of every order worth more than the average order, largest first, then by id.

Return the names of every customer who has no shipped order — including customers with no orders at all. Order by name.

Return how many submissions answered the plan question with pro. Call the count submissions. The same responses exist in two places; use the one the database can index.

Return the id and duration of every event belonging to actor 137, oldest id first. There is an index on actor_id; the budget assumes you let the planner use it.

Return the actor_id of every event for actor 200 — that column and nothing else, ordered by actor_id. The index already carries everything you need, so the table should never be read.

Return the ids of every 'error' event that happened on or after 2024-06-01, oldest first. There is an index on (kind, happened_on).

Return the ids of every event whose email is '[email protected]', regardless of how it was capitalised in the query — oldest id first. There is no index on lower(email), so find the way to ask that keeps the index you do have.

Return the actor_id and event count for the five busiest actors among 'purchase' events, busiest first then by actor_id. Call the count events.

Return the ids of every 'error' event, oldest first. There are far fewer of these than of any other kind, and the plan should reflect that.

Return each customer's name alongside their order totals, ordered by name then total. Only customers who have orders. Two columns: name and total.

This ran in a nightly report that used to finish in seconds. Nobody has changed the query. Read the plan and say which node is costing you, and what you would do about it.

Two small tables, and a plan that does far more work than either of them contains. Which node, and why?

For every actor with events, return the actor_id and how many events they have, busiest first then by actor_id. Call the count events. One pass, not one query per actor.

Return the next twenty event ids after id 4000, in ascending order. Do it the way that costs the same on page twenty thousand as on page one.

An agent produced `SELECT * FROM events WHERE lower(email) = '[email protected]' ORDER BY id`. It returns the right rows. Write the query that returns the same id, actor_id and happened_on — and lets the database use the index it already has.