Calculate average days between orders The last three records tsql

Question

I trying to take an average per customer, but you're not grouping by customer. I would like to calculate the average days between several order dates from a table called invoice. For each BusinessPartnerID, what is the average days between orders i want average days last three records orders . I got the average of all order for each user but need days last three records orders The sample table is as below

   ;WITH temp (avg,invoiceid,carname,carid,fullname,mobail)
AS
(
    SELECT AvgLag = AVG(Lag)  , Lagged.idinvoice,
     Lagged.carname ,
     Lagged.carid ,Lagged.fullname,Lagged.mobail
FROM 
(
 SELECT   
  
  (car2.Name) as carname ,
    (car2.id) as carid ,( busin.Name) as fullname, ( busin.Mobile) as mobail , INV.Id as idinvoice , Lag = CONVERT(int, DATEDIFF(DAY, LAG(Date,1) 
    OVER (PARTITION BY car2.Id ORDER BY Date ), Date))
     FROM  [dbo].[Invoice]    AS    INV
    JOIN  [dbo].[InvoiceItem] AS INITEM  on INV.Id=INITEM.Invoiceid
    JOIN [dbo].[BusinessPartner]  as busin on busin.Id=INV.BuyerId and Type=5
    JOIN [dbo].[Product] as pt on pt.Id=INITEM.ProductId and  INITEM.ProductId is not null and  pt.ProductTypeId=3  
    JOIN [dbo].[Car] as car2 on car2.id=INv.BusinessPartnerCarId 
    
    
) AS Lagged 
 GROUP BY
  Lagged.carname,
 Lagged.carid,Lagged.fullname,Lagged.mobail, Lagged.idinvoice

 -- order by Lagged.fullname
  
  )
 SELECT  *  FROM temp  where avg is not null   order by avg  

Answer 1

I don't really see how your query relate to your question. Starting from a table called invoice that has columns businesspartnerid, and date, here is how you would take the average of the day difference between the last 3 invoices of each business partner:

select businesspartnerid, 
    avg(1.0 * datediff(
        day, 
        lag(date) over(partition by businesspartnerid order by date),
        date
    ) avg_diff_day
from (
    select i.*,
        row_number() over(partiton by businesspartnerid order by date desc) rn
    from invoice i
) i
where rn <= 3
group by businesspartnerid

Note that 3 rows gives you 2 intervals only, that will be averaged.