CODEWITHSUNDEEP
sql
Manuel Silva
Manuel Silva

Reputation: 35

SQL Datediff without datediff?

I have the following data sample with 3 columns:

Customer_ID; Customer_Class; Purchase_day

How can I calculate the average days between purchases for each class of customers?

I have tried using a CTE, but I can´t really figure out how can I use some sort of datediff here:

WITH recency_cte --(SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT Customer_ID, Customer_class, purchase_day
    FROM transactions_table b join customer_table a on customer_id=b.customer_id
    group by Customer_ID, Customer_class, purchase_day
    order by purchase_day asc
)
-- Define the outer query referencing the CTE name.
SELECT customer_class, avg(datediff(call_day)
FROM recency_cte
group by a.comm

Thanks.

Upvotes: 0

Views: 525

Answers (1)

AHiggins
AHiggins

Reputation: 7219

This might help. Note that it will be a bit limited, only returning customers who have made purchases on at least two different days. I've included some (commmented out) sample data so you can see what I'm expecting the underlying data to look like. I also made the assumption that customer_class is unique to customer_id: i.e., each customer_id has only one customer_class. If that's not true, you'll need to add customer_class to the JOIN condition between the two CTEs. 

WITH recency_cte --(SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT Customer_ID, Customer_class, purchase_day, ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY purchase_day) AS RowNumber
    FROM transactions_table b join customer_table a on customer_id=b.customer_id
     -- (
        --SELECT 1 Customer_ID, 'a' Customer_Class, '6/01/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/03/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/04/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/05/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/08/2014' Purchase_Day
        --UNION 
        --SELECT 2 Customer_ID, 'b' Customer_Class, '6/12/2014' Purchase_Day
        --UNION 
        --SELECT 1 Customer_ID, 'a' Customer_Class, '6/12/2014' Purchase_Day
     -- )s
    GROUP BY Customer_ID, Customer_class, purchase_day
)
-- Define the outer query referencing the CTE name.
SELECT Day1.customer_class, avg(datediff(day, Day1.Purchase_Day, Day2.Purchase_Day)) AverageDaysBetweenPurchases
FROM 
    recency_cte Day1
     INNER JOIN 
    recency_cte Day2 ON 
        Day1.Customer_ID = Day2.Customer_ID AND 
        Day1.RowNumber + 1 = Day2.RowNumber
group by Day1.Customer_Class

Upvotes: 2

Related Questions