Exploring Customer Data for Profit Maximization: A Sprocket Central Case Study

--Counting the number of columns in CustomerDemographic
SELECT COUNT(*) as total_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘CustomerDemographic’;

 -- Identifying duplicate customer_id records
SELECT COUNT(customer_id) — COUNT(DISTINCT customer_id) as duplicates
FROM CustomerDemographic;

--Handling NULL values in job_industry_category
UPDATE CustomerDemographic
SET
gender = CASE 
 WHEN gender = ‘F’ THEN ‘Female’
 WHEN gender = ‘Femal’ THEN ‘Female’
 WHEN gender = ‘M’ THEN ‘Male’
 WHEN gender = ‘U’ THEN ‘Male’
 ELSE gender
 END,
job_industry_category =
 CASE 
 WHEN job_industry_category = ‘n/a’ THEN NULL
 ELSE job_industry_category
 END;

--Checking for empty customer_id records
SUM(CASE WHEN customer_id = ‘ ‘ THEN 1 ELSE 0 END) as customer_id
FROM CustomerDemographic;

--Standardizing gender and handling NULL values in job_industry_category
UPDATE CustomerDemographic
SET
gender = CASE 
 WHEN gender = ‘F’ THEN ‘Female’
 WHEN gender = ‘Femal’ THEN ‘Female’
 WHEN gender = ‘M’ THEN ‘Male’
 WHEN gender = ‘U’ THEN ‘Male’
 ELSE gender
 END,
job_industry_category =
 CASE 
 WHEN job_industry_category = ‘n/a’ THEN NULL
 ELSE job_industry_category
 END;

 -- Adding age column and calculating ages
ALTER TABLE CustomerDemographic
ADD age INT;

-- Calculating age
UPDATE CustomerDemographic
SET age = DATEDIFF(YEAR, DOB, GETDATE());

 --Removing an outlier (customer age 180)
DELETE FROM CustomerDemographic
WHERE customer_id = 34;

-- Adding age column and calculating ages for new customers
ALTER TABLE NewCustomerList
ADD age INT


-- Calculating age
UPDATE NewCustomerList
SET age = DATEDIFF(YEAR, DOB, GETDATE());

-- Adding a profit column
ALTER TABLE Transactions
ADD profit FLOAT

-- Calculating profit
UPDATE Transactions
SET profit = list_price - standard_cost

-- Creating profit quartiles
WITH quartiles AS
(
SELECT
 *,
 NTILE(4) OVER(ORDER BY profit) AS quartile
FROM Transactions
WHERE profit IS NOT NULL
)

--Assigning sale categories based on quartiles
SELECT
 *,
 CASE
 WHEN quartile = 1 THEN 'Bronze'
 WHEN quartile = 2 THEN 'Silver'
 WHEN quartile = 3 THEN 'Gold'
 WHEN quartile = 4 THEN 'Platinum'
 ELSE CAST(quartile AS VARCHAR)
 END AS sale_category
FROM quartiles

--Updating the Transactions table with quartile and sale_category
WITH quartiles AS
(
SELECT
 transaction_id,
 NTILE(4) OVER(ORDER BY profit) AS quartile
FROM Transactions
WHERE profit IS NOT NULL
)

--Updating the Transactions table
UPDATE Transactions
SET quartile = q.quartile
FROM quartiles q
WHERE Transactions.transaction_id = q.transaction_id;

 --Modifying the sale_category column to increase the length
ALTER TABLE Transactions
ALTER COLUMN sale_category VARCHAR(MAX);

 --Updating the sale_category
UPDATE Transactions
SET sale_category =
 CASE
 WHEN quartile = 1 THEN 'Bronze'
 WHEN quartile = 2 THEN 'Silver'
 WHEN quartile = 3 THEN 'Gold'
 WHEN quartile = 4 THEN 'Platinum'
 ELSE CAST(quartile AS VARCHAR)
 END

-- Adding age_range columns to CustomerDemographic and NewCustomerList
ALTER TABLE CustomerDemographic
ADD age_range VARCHAR(20)

ALTER TABLE NewCustomerList
ADD age_range VARCHAR(20)

 -- Assigning age ranges
UPDATE CustomerDemographic
SET
age_range = CASE
 WHEN age BETWEEN 20 AND 40 THEN '21–40'
 WHEN age BETWEEN 41 AND 60 THEN '41–60'
 ELSE '60<'
 END

UPDATE NewCustomerList
SET
age_range = CASE
 WHEN age BETWEEN 20 AND 40 THEN '21–40'
 WHEN age BETWEEN 41 AND 60 THEN '41–60
'
 ELSE '60<'
 END;

 -- Profits by job industry category

SELECT
 c.job_industry_category,
 ROUND(SUM(t.profit), 2) AS total_profit
FROM Transactions t
LEFT JOIN CustomerDemographic c
ON t.customer_id = c.customer_id
WHERE c.job_industry_category IS NOT NULL
AND t.order_status = ‘Approved’
GROUP BY c.job_industry_category
ORDER BY total_profit DESC;

-- Profits by job title
SELECT
 c.job_title,
 ROUND(SUM(t.profit), 2) AS total_profit
FROM Transactions t
LEFT JOIN CustomerDemographic c
ON t.customer_id = c.customer_id
WHERE c.job_title IS NOT NULL
AND t.order_status = ‘Approved’
GROUP BY c.job_title
ORDER BY total_profit DESC;

-- Profits by state
SELECT
 c.state,
 ROUND(SUM(t.profit), 2) AS total_profit
FROM Transactions t
LEFT JOIN CustomerAddress c
ON t.customer_id = c.customer_id
WHERE c.state IS NOT NULL
AND t.order_status = ‘Approved’
GROUP BY c.state
ORDER BY total_profit DESC;

-- Profits by wealth segment
SELECT
 c.wealth_segment,
 ROUND(SUM(t.profit), 2) AS total_profit
FROM Transactions t
LEFT JOIN CustomerDemographic c
ON t.customer_id = c.customer_id
WHERE c.wealth_segment IS NOT NULL
AND t.order_status = ‘Approved’
GROUP BY c.wealth_segment
ORDER BY total_profit DESC;

-- Profits by age range
SELECT
 c.age_range,
 ROUND(SUM(t.profit), 2) AS total_profit
FROM Transactions t
LEFT JOIN CustomerDemographic c
ON t.customer_id = c.customer_id
WHERE c.age_range IS NOT NULL
AND t.order_status = ‘Approved’
GROUP BY c.age_range
ORDER BY total_profit DESC;