SQL – Find customers who have not ordered in the last year and what they ordered

I am able to find the customers who have not ordered in the last year by doing this:

SELECT O.CustID, MAX(O.ORDERDATE) AS LastOrder FROM Orders O
WHERE LastOrder <= DATEADD(year,-1,GETDATE())
GROUP BY O.CustID

I would like to join a second table (OrderDetails) that contains the order specifics on the field called OrderID.

There is only one item per order. (my data isn’t actually customers and orders – but the analogy works for what I am doing).

When I add this OrderID field into the query, my results multiply. This is happening because while when I am grouping, I am grouping by OrderID and CustID, which would pull each specific OrderID.

My question is how would I pull just the last OrderID from the OrderDetails table where the orderID for that customer is over 1 year old.

This is what I have so far

SELECT OD.OrderID, O.CustID, MAX(O.ORDERDATE) AS LastOrder 
FROM Orders O
INNER JOIN OrderDetails OD
ON O.OrderID = OD.OrderID
WHERE LastOrder <= DATEADD(year,-1,GETDATE())
GROUP BY OD.OrderID, O.CustID
 

>Solution :

Using:

WITH cte AS (
  SELECT O.*
  FROM Orders O
  INNER JOIN OrderDetails OD
    ON O.OrderID = OD.OrderID
  QUALIFY MAX(O.OrderDate) OVER(PARTITION BY O.CustId) <= DATEADD(year,-1,CURRENT_DATE())
)
SELECT *
FROM cte
QUALIFY ROW_NUMBER() OVER(PARTITION BY CustId ORDER BY OrderDate DESC) = 1