These are also very popular SQL Interview queries for major organizations.
We are going to be working with Orders sample data.
Please use the below SQL statements to create and insert the table and data.
Create table TblOrders
(OrderDate date null,
OrderKey varchar(50) null,
CustomerID integer null
);
Insert into TblOrders VALUES ('2021-01-03', 'AAA1', 11);
Insert into TblOrders VALUES ('2021-02-13', 'ABA1', 11);
Insert into TblOrders VALUES ('2021-04-30', 'BAA1', 11);
Insert into TblOrders VALUES ('2021-12-20', 'YAA1', 11);
Insert into TblOrders VALUES ('2022-03-03', 'AYA1', 11);
Insert into TblOrders VALUES ('2022-05-11', 'AZA1', 11);
Insert into TblOrders VALUES ('2022-01-30', 'HAA1', 18);
Insert into TblOrders VALUES ('2022-07-03', 'GBA1', 18);
Insert into TblOrders VALUES ('2022-09-08', 'KHA1', 18);
SQL Query 1 - In the first scenario, we are going to simply identify New and Repeat Customers.
Approach -
- New Customers will be identified when a Customer makes his first purchase.
- Repeat Customers will be identified when they make their next purchases.
Logic -
- Find Minimum Order date for each customer.
- Compare for each record, its Order Date with the Minimum Order Date calculated above for each customer.
- When the Order Date = Minimum Order Date, Customer is a New Customer as on that Date.
- When the Order Date > Minimum Order Date, Customer is a Repeat Customer as on that Date.
SQL Query 2 - In the second scenario, we are going to identify New, Active and Lapsed (Inactive) Customers.
Approach -
- New Customers will be identified when a Customer makes his first purchase.
- Active Customers will be identified when the time gap between two consecutive purchases by a Customer is not greater than a defined time period (ex - 6 months or 180 days)
- Lapsed/Inactive Customers will be identified when the time gap between two consecutive purchases by a Customer is not greater than a defined time period (ex - 6 months or 180 days)
Logic -
- Order purchase records for each Customer by Order Date and use LAG function to identify the previous order date.
- For the Purchase, the Lag Order Date will be NULL as no previous record exists. Identify it as a New Customer.
- When the difference between Row Order Date and Lag Order Date is greater than the required time period, mark Customer as Lapsed or Inactive.
- When the difference between Row Order Date and Lag Order Date is NOT greater than the required time period, mark Customer as Active.
- Use DATEDIFF function to calculate the difference in days.