Guides › CTEs for reports

CTEs in SQL Server: name a step so the report can be read

A CTE is a named subquery at the top. You write the steps in the order a colleague can read them. SQL Server does not care; the next person does.

Last order date, then the quiet customers

WITH last_order AS (
  SELECT customer_id, MAX(order_date) AS last_date
  FROM Orders
  GROUP BY customer_id
)
SELECT c.customer_id, c.first_name, c.last_name, lo.last_date
FROM Customers c
JOIN last_order lo ON lo.customer_id = c.customer_id
WHERE lo.last_date < DATEADD(day, -90, CAST(GETDATE() AS date))
ORDER BY lo.last_date;

Read it top to bottom: build last order, join names, keep 90 days quiet. The same thing as a nested FROM, without the nest.

A CTE is not a table

It lives for one statement. The next query cannot see last_order unless you write the WITH again, or you make a real temp table / view.

When to use one

You need a result twice. You need to filter a ROW_NUMBER. You are about to nest three levels and you can still smell the fire drill. Start a CTE.

Try it on Harbour & Co

James Rowe (Sales Manager) needs this now: “When did each customer last order? Names, not IDs. Most recent first.” Write the query, run it, send the results. On the paid plan.

Take the ticket →

Learn it on a real ticket

Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.

Clock in free See Monday’s tickets