SQL Puzzle 1: Identify new Customers in Real Time

XYZ, the renowned online retail platform, requires customers to create an account to browse and purchase items of their choice. In this analysis, our objective is to pinpoint the new customers who have joined in the current month.

Hint: We can consider the date of a customer's first transaction as their account creation date, enabling us to distinguish between new and existing customers

Input Data:

Tap For Closer Look

Expected Output:

Tap For Closer Look

Data Script:

CREATE TABLE dbo.CustomerTransactions
(
	CustomerID NVARCHAR(250),
	CustomerName NVARCHAR(MAX),
	BilledItems NVARCHAR(MAX),
	TransactionDate DATETIME
)

INSERT INTO dbo.CustomerTransactions
VALUES 
('1000A','Alex','BoatHeadPhones, Samsung Mobile',GETDATE()-200),
('1000A','Alex','ITC Notebooks, Pencile',GETDATE()-150),
('1000A','Alex','Filla Shoe, Bata School Shoe',GETDATE()-58),
('2AB01','Shinde','Laptop',GETDATE()-10),
('2AB01','Shinde','Laptop Charger, Laptop Bag',GETDATE()),
('89CR0','Naani','Shorts',GETDATE()-3),
('89CR0','Naani','Boost,  Good Day Bicuts',GETDATE()),
('Y87AC','OutByte','Avast Anti virus',GETDATE()-90),
('Y87AC','OutByte','T-Shirts',GETDATE()-71)

SELECT * FROM CustomerTransactions

Please try to answer the question, if you still not able to get it click below show button to see the answer.

  ;WITH CTE AS
(
	SELECT CustomerID, MIN(TransactionDate) AS MinDate 
	FROM dbo.CustomerTransactions
	GROUP BY CustomerID
)
SELECT * FROM dbo.CustomerTransactions
WHERE CustomerID IN (SELECT CustomerID FROM CTE Where MinDate 
						BETWEEN CAST(DATEADD(DAY,-DAY(GETDATE())+1,GETDATE()) AS DATE) AND CAST(GETDATE() AS DATE)
					)