Find number of days between two date records in the same table-CodePudding

I have a DATE, ORDERID, CLIENTID and STOREID.

I'm trying to get the average number of days between the FIRST order date and THIRD order date for all clients who have placed at least 3 orders.

This is what I have so far but when I add OrderId, it doesn’t return anything anymore.

select Date, OrderId, ClientId
from ClientOrders
group by Date, OrderId, ClientId
having count(ClientId) > 3

CodePudding user response：

We can use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY Date) rn
    FROM ClientOrders
)

SELECT AVG(diff)
FROM
(
    SELECT DATEDIFF(day,
                    MAX(CASE WHEN rn = 1 THEN Date END),
                    MAX(CASE WHEN rn = 3 THEN Date END)) AS diff
    FROM cte
    GROUP BY ClientId
    HAVING COUNT(*) >= 3   -- at least 3 orders
) t;