DataQuest Question2.sql

-- DataQuest.io SQL project
-- stores.db
--
-- how should we match marketing and communication strategies to customer behaviors? 
-- This involves categorizing customers: finding the VIP (very important person) customers and those who are less engaged.
-- We'll do that by computing VIP Rank based on profits generated by customer, then print top 10% and bottom 10%
--
-- Determine how much profit each customer generates
with orderProfit as( SELECT od.orderNumber ,  sum(od.quantityOrdered * (od.priceEach - p.buyPrice)) as profit
from orderdetails od
INNER join products p on p.productCode = od.productCode
GROUP by od.orderNumber),

-- Rank based on profits generated
viprank as (
SELECT c.contactFirstName, c.contactLastName, o.customerNumber, printf('$%10.2f', sum(op.profit)) as profitByCustomer,
ntile(10) OVER (ORDER by sum(op.profit) DESC) as VIPRank
from orders o
INNER join orderProfit op on op.orderNumber = o.orderNumber
INNER JOIN customers c on c.customerNumber = o.customerNumber
GROUP by o.customerNumber
ORDER by profitByCustomer DESC
)

-- Print top 10% and bottom 10%
SELECT vip.contactFirstName, vip.contactLastName, vip.customerNumber, vip.profitByCustomer, vip.VIPRank
FROM viprank vip
WHERE vip.VIPRank = 1 or vip.VIPRank = 10;