Get Number of customers who have done transaction on 1st Anniversary week and soo on - SQL

user_13370214 0
Viewed 34

I have a transaction table which contains purchase history of all the customers. I want to find out the number of customers who have have purchased within a week(7 days) of their 1st purchase anniversary(Using Min(Bill_Date))

Customer_Id   Bill_Date
   1          2020-01-23
   1          2021-01-28
   2          2020-09-01
   2          2021-09-07
   3          2020-03-01

So the expected output:

The No of customers who have purchased with a week of their 1st purchase anniversary is : 3

I am stuck after this step

with CTE As
(Select customer_id,Bill_Date from
(Select Customer_id,Bill_Date,Dense_Rank() over(partition by Customer_id order by Bill_Date)r1 from CDM_Bill_Details)t1
where t1.r1=1
group by customer_id)
mysql sql
1 Answers

I would use exists logic here:

WITH cte AS (
    SELECT *, MIN(Bill_Date) OVER (PARTITION BY Customer_Id) Min_Bill_Date
    FROM CDM_Bill_Details
)

SELECT COUNT(DISTINCT Customer_Id) AS cnt
FROM
(
    SELECT Customer_Id
    FROM cte
    WHERE Bill_Date > Min_Bill_Date AND
          DATEDIFF(Bill_Date - interval 1 year, Min_Bill_Date) <= 7
) t;

The above WHERE clause compares a bill date other than the first one against the first bill date by subtracting one year, then asserting that this subsequent purchase is plus or minus 7 days from the first purchase.

user_18632290
Related