Here is the tables: https://dbfiddle.uk/markdown?rdbms=sqlserver_2019&fiddle=effc94afe681b2dfdb3e2c02c2b005ea

I want to find the average Total Amount for last 3 values (I mean the last 3 OrderID) for each customer. If customer doesn't have 3 operation, result should be null.

Here is my answer (T-SQL):

SELECT s.CustomerID,avg(s.TotalAmount) as AverageofLast3_operation
FROM (SELECT OrderID, CustomerID, EventDate, TotalAmount, 
      ROW_NUMBER() over (partition by CustomerID ORDER BY OrderID asc) as Row_num
      FROM CustomerOperation
    )s
WHERE s.Row_num>3
GROUP BY CustomerID

And the result is:

According to the question, I should also have a row like this:

How can I achieve this with T-SQL?

CodePudding user response：

You need conditional aggregation:

SELECT CustomerID, 
       AVG(CASE WHEN counter >= 3 THEN TotalAmount END) AS AverageofLast3_operation 
from (
  SELECT OrderID, CustomerID, EventDate, TotalAmount, 
         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC) AS Row_num,
         COUNT(*) OVER (PARTITION BY CustomerID) counter
  FROM CustomerOperation
) s 
WHERE Row_num <= 3 
GROUP BY CustomerID;

Or:

SELECT CustomerID, 
       CASE WHEN COUNT(*) = 3 THEN AVG(TotalAmount) END AS AverageofLast3_operation 
from (
  SELECT OrderID, CustomerID, EventDate, TotalAmount, 
         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC) AS Row_num
  FROM CustomerOperation
) s 
WHERE Row_num <= 3 
GROUP BY CustomerID;

See the demo.

CodePudding user response：

You can use a conditional average like so:

with t as (
    select customerId,
        case when
            Row_Number() over(partition by customerid order by orderid desc) <=3 then totalamount
        else 0 end TotalAmount,
        Count(*) over (partition by customerid) cnt
    from CustomerOperation
)
select customerId, Avg(case when cnt>=3 then totalamount end) as Average
from t
where totalAmount>0
group by CustomerId