SQLQuestion 08 of 14
CTE — customers with the most orders
Answer out loud or in your head first, then compare.
Answer
A CTE, i.e. WITH, gives a name to a stage of the query. Below I first count orders in the period, then select all the joint leaders.
SQL
-- orders(id, customer_id, created_at timestamp)
WITH totals AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= TIMESTAMP '2026-01-01'
AND created_at < TIMESTAMP '2026-02-01'
GROUP BY customer_id
)
SELECT customer_id, order_count
FROM totals
WHERE order_count = (SELECT MAX(order_count) FROM totals);Pitfall: I ask whether “the biggest orders” means the count, the amount or a single record. The half-open range covers the whole of January. A CTE isn't an automatic guarantee of speed or materialisation.
How did it go? Be honest — “Not yet” puts the question on your review list.