Temp tables vs table variables vs CTEs in SQL Server

A CTE is a name for this statement. A temp table (#name) lives for the batch and can have indexes. A table variable is easy and often a worse plan. For a morning report, CTE or #temp is enough.

A #temp you reuse

SELECT customer_id, MAX(order_date) AS last_date
INTO #last
FROM Orders
GROUP BY customer_id;

SELECT c.first_name, c.last_name, l.last_date
FROM Customers c
JOIN #last l ON l.customer_id = c.customer_id
WHERE l.last_date < DATEADD(day, -90, CAST(GETDATE() AS date));

Drop is automatic at the end of the session. In SSMS, run the whole batch, not just the second half.