SQL Challenge

You are given a sales table with the following columns:-

id (INT)- customer_id (INT)- purchase_date (DATE)- amount (DECIMAL)

Write a query to find customers who made exactly two purchases, and those purchases were made on two consecutive days.

**Sample Data**:

1. id: 1, customer_id: 101, purchase_date: 2023-09-01, amount: 500

2. id: 2, customer_id: 101, purchase_date: 2023-09-02, amount: 300

3. id: 3, customer_id: 102, purchase_date: 2023-09-01, amount: 100

4. id: 4, customer_id: 102, purchase_date: 2023-09-03, amount: 200

5. id: 5, customer_id: 103, purchase_date: 2023-09-01, amount: 150

6. id: 6, customer_id: 103, purchase_date: 2023-09-01, amount: 150

**Expected Output**: - customer_id: 101

Here to solve this query , we can use CTE (with query )

WITH cte AS ( 

SELECT customer_id, COUNT(*) AS purchase_count 

FROM sales1 

GROUP BY customer_id 

HAVING COUNT(*) = 2)

SELECT a.customer_id, a.purchase_date AS day1, b.purchase_date AS day2

FROM sales1 a

JOIN sales1 b 

ON a.customer_id = b.customer_id 

AND b.purchase_date = DATEADD(day, 1, a.purchase_date)

JOIN cte c 

ON a.customer_id = c.customer_id;        

In the above query first we filtered out the customer data who have exactly two purchases

Here in outer query we have used DATEADD() function what is that lets know

The DATEADD function is used to manipulate SQL date and time values based on some specified parameters. We can add or subtract a numeric value to a specified date-time to get future or past timelines.

The syntax of this function is:

DATEADD (datepart, number, date)
        

• datepart is a portion of the date to which a specified numeric value will be added
• number is an integer value that will be added to the specified datepart
• date is the value of a specified date to which specified numeric value will be added on specified datepart

The datepart is a part of the date such as the year, month, date, day, hour, minutes, second, etc.

By using this function we get consecutive date purchases for next 1 day .

Thats how we achieve this . This solution is based on SQLServer db.

One can use this insert statement to prepare your data :

INSERT INTO purchases 
(id, customer_id, purchase_date, amount) 
VALUES
(1, 101, '2023-09-01', 500.00),
(2, 101, '2023-09-02', 300.00),
(8, 101, '2024-09-01', 500.00),
(9, 101, '2024-09-02', 300.00),
(3, 102, '2023-09-01', 100.00),
(4, 102, '2023-09-03', 200.00),
(5, 103, '2023-09-01', 150.00),
(6, 103, '2023-09-01', 250.00),
(7, 103, '2023-09-02', 150.00),
(10, 103, '2023-09-03', 150.00);