/*
Swiggy SQL Interview Question

Find the customers who bought a product for atleast three consecutive days
*/

-- Schema Setup
CREATE TABLE purchases (
    empid INT,
    purchase_date DATE
);

INSERT INTO purchases (empid, purchase_date) VALUES
(1, '2024-08-01'),
(1, '2024-08-02'),
(1, '2024-08-03'),
(1, '2024-08-03'),
(1, '2024-08-03'),
(2, '2024-08-01'),
(2, '2024-08-03'),
(3, '2024-08-02'),
(3, '2024-08-03'),
(3, '2024-08-04'),
(4, '2024-08-02');

-- Solution
WITH employee_purchases AS
(
 SELECT
     empid,
     purchase_date,
     ROW_NUMBER() OVER(ORDER BY empid) AS rn,
     DENSE_RANK() OVER(PARTITION BY empid ORDER BY purchase_date) AS rnk
 FROM purchases
)
    
SELECT 
    empid
FROM employee_purchases
GROUP BY empid
HAVING COUNT(rn-rnk) >= 3;

-- 2nd Solution
WITH consecutive_purchases AS (
    SELECT
        empid,
        purchase_date,
        LAG(purchase_date, 1) OVER (PARTITION BY empid ORDER BY purchase_date) AS prev_date1,
        LAG(purchase_date, 2) OVER (PARTITION BY empid ORDER BY purchase_date) AS prev_date2
    FROM purchases
)
SELECT DISTINCT empid
FROM consecutive_purchases
WHERE DATEDIFF(purchase_date, prev_date1) = 1
  AND DATEDIFF(prev_date1, prev_date2) = 1;