Assistance needed: T-SQL query for recent order averages

Data and Databases
1

I’m stuck on a T-SQL problem and could use some help. I have an Orders table with columns for order date, total, and customer number. What I’m trying to do is calculate the average order amount for each customer’s two most recent orders.

Here’s a sample of what my table looks like:

CREATE TABLE Orders (
    OrderDate DATE,
    OrderTotal DECIMAL(10,2),
    CustomerNum INT
);

INSERT INTO Orders VALUES
('2008-09-30', 8.00, 1),
('2008-09-15', 6.00, 1),
('2008-09-01', 9.50, 1),
('2008-09-01', 1.45, 2),
('2008-09-16', 4.50, 2),
('2008-09-17', 8.75, 3),
('2008-09-18', 2.50, 3);

For example, Customer 1’s result should be 7.00 (average of 8.00 and 6.00), not 7.83 (average of all three orders).

I’ve been trying to figure this out for a while now, but I’m drawing a blank. Any suggestions on how to approach this query?

2

ooh, interesting problem! have you considered using a windowing function like ROW_NUMBER() to rank the orders by date? then you could filter for the top 2 ranks per customer and avg those. just brainstorming here - what approaches have u tried so far? curious to hear your thoughts!

3

hey there! u could try using a subquery with TOP 2 to grab the latest orders for each customer, then AVG those. something like:

SELECT CustomerNum, AVG(OrderTotal) AS AvgRecent
FROM (SELECT TOP 2 * FROM Orders
ORDER BY OrderDate DESC) AS Recent
GROUP BY CustomerNum

might need some tweaking but hope that helps get u started!

4

Your approach using ROW_NUMBER() is on the right track. Here’s a query that should solve this problem:

WITH RankedOrders AS (
    SELECT 
        CustomerNum,
        OrderTotal,
        ROW_NUMBER() OVER (PARTITION BY CustomerNum ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
)
SELECT 
    CustomerNum,
    AVG(OrderTotal) AS AvgRecentOrderTotal
FROM RankedOrders
WHERE RowNum <= 2
GROUP BY CustomerNum;

This query first ranks each customer’s orders by date, then filters for the top two, and finally calculates the average. It is efficient and should work well even with large datasets. Let me know if you need any clarification on how this works.