SQL in Business!

By using different SQL elements such as CTE, Joins, and Group By, a business can manipulate its data in order to analyze ways to maximize its profits. Let’s create a table called client_purchases to see all the customers and what they have purchased recently.

create table client_purchases(

	purchase_date date,
	customer_name varchar(20),
	business_line varchar(20),
	item varchar(20)
)

INSERT INTO client_purchases VALUES
('01/01/2023','Abby','Snack','Doritos'),
('01/02/2023','Abby','Desserts','Tiramisu'),
('01/03/2023','Abby','Desserts','Panna_Cotta'),
('01/04/2023','Abby','Hot_Breakfast','McGriddle'),
('01/05/2023','Abby','Hot_Breakfast','Bacon'),
('01/06/2023','Abby','Cold_Breakfast','Tuna Salad'),
('01/01/2023','John','Snack','Doritos')

SELECT *
FROM dbo.client_purchases

This will print out:

purchase_date customer_name        business_line        item
------------- -------------------- -------------------- --------------------
2023-01-01    Abby                 Snack                Doritos
2023-01-02    Abby                 Desserts             Tiramisu
2023-01-03    Abby                 Desserts             Panna_Cotta
2023-01-04    Abby                 Hot_Breakfast        McGriddle
2023-01-05    Abby                 Hot_Breakfast        Bacon
2023-01-06    Abby                 Cold_Breakfast        Tuna Salad
2023-01-01    John                 Snack                Doritos

(7 rows affected)

Let’s say that the business wants to find out whether and when the customer is new to the company. Furthermore, the business also wants to know if each order is a new business line for each customer.

We can query using:

WITH cte_New AS (
SELECT customer_name
, MIN(purchase_date) AS min_Date
FROM dbo.test_cte
GROUP BY customer_name
),

 cte_New2 AS(
SELECT customer_name
, business_line
,MIN(purchase_date) AS min_Date
FROM dbo.test_cte tc
GROUP BY customer_name, business_line
)

SELECT purchase_date
, tc.customer_name
, tc.business_line
, item
, CASE WHEN tc.purchase_date = cte_New.min_Date THEN 'Yes'
ELSE 'No' END AS IsNewCompany

, CASE WHEN tc.purchase_date = cte_New2.min_Date THEN 'Yes'
ELSE 'No' END AS IsNewBusinessLine

FROM dbo.test_cte tc
LEFT JOIN cte_New2
ON tc.customer_name = cte_New2.customer_name
AND tc.business_line = cte_New2.business_line

LEFT JOIN cte_New
ON tc.customer_name = cte_New.customer_name

Therefore, this query returns two new columns “IsNewCompany” and “IsNewBusinessLine” showing more information about each customer and their order details below.

purchase_date customer_name        business_line        item                 IsNewCompany IsNewBusinessLine
------------- -------------------- -------------------- -------------------- ------------ -----------------
2023-01-01    Abby                 Snack                Doritos              Yes          Yes
2023-01-02    Abby                 Desserts             Tiramisu             No           Yes
2023-01-03    Abby                 Desserts             Panna_Cotta          No           No
2023-01-04    Abby                 Hot_Breakfast        McGriddle            No           Yes
2023-01-05    Abby                 Hot_Breakfast        Bacon                No           No
2023-01-06    Abby                 Cold_Breakfast        Tuna Salad           No           Yes
2023-01-01    John                 Snack                Doritos              Yes          Yes

(7 rows affected)

“IsNewCompany” returns “Yes” for Abby’s and John’s first purchase and “IsNewBusinessLine” focuses on purely the “business_line” column being newly distinct for each customer.