Use NTILE(4) to Divide Customers into Revenue Quartiles
Use NTILE(4) to Divide Customers into Revenue Quartiles
Customers Table:
Payments Table:
Query Explanation:
-
The inner query calculates
TotalRevenueper customer by summing their payments. -
The outer query uses
NTILE(4)to assign each customer to one of four quartiles:-
Quartile 1: Highest spenders
-
Quartile 4: Lowest spenders
-
-
ORDER BY TotalRevenue DESC: ensures that higher revenue customers fall into the first quartile.
SQL Query:
SELECT
CustomerID,
TotalRevenue,
NTILE(4) OVER (ORDER BY TotalRevenue DESC) AS RevenueQuartile
FROM (
SELECT
CustomerID,
SUM(Amount) AS TotalRevenue
FROM
Payments
GROUP BY
CustomerID
) AS CustomerRevenue;
Output:
