SQL Server 2022 introduces several powerful features to enhance data analysis and performance. Among these, the APPROX_PERCENTILE_DISC function offers an efficient way to calculate discrete percentiles from large datasets. This blog will explore this function in depth, using practical examples from the JBDB database, and provide a detailed business use case to illustrate its utility. Let’s dive into the world of approximate discrete percentiles! 🎉
Business Use Case: Analyzing Customer Satisfaction 📊
Imagine a retail company seeking to understand customer satisfaction across different store locations. The data, stored in the JBDB database, includes satisfaction scores ranging from 1 to 5, representing customers’ overall experience. The company aims to identify key percentiles such as the median (50th percentile) and the 90th percentile to gauge typical and top-tier satisfaction levels. Using APPROX_PERCENTILE_DISC, they can efficiently compute these discrete percentiles, helping to guide strategies for improving customer experience and focusing on high-impact areas.
Understanding the APPROX_PERCENTILE_DISC Function 🧠
The APPROX_PERCENTILE_DISC function in SQL Server 2022 is designed to calculate approximate discrete percentiles from a sorted set of values. Unlike the continuous APPROX_PERCENTILE_CONT, this function returns the value nearest to the percentile rank, which is particularly useful for ordinal data.
Syntax:
APPROX_PERCENTILE_DISC ( percentile ) WITHIN GROUP ( ORDER BY column_name )
percentile: A numeric value between 0 and 1, indicating the desired percentile.column_name: The column used to order the dataset before calculating the percentile.
Example 1: Calculating Key Percentiles 🔍
Let’s calculate the median (50th percentile) and 90th percentile of customer satisfaction scores.
Setup:
USE JBDB;
GO
CREATE TABLE CustomerSatisfaction (
CustomerID INT PRIMARY KEY,
StoreID INT,
SatisfactionScore INT,
ReviewDate DATE
);
INSERT INTO CustomerSatisfaction (CustomerID, StoreID, SatisfactionScore, ReviewDate)
VALUES
(1, 101, 5, '2023-01-15'),
(2, 102, 3, '2023-01-16'),
(3, 103, 4, '2023-01-17'),
(4, 101, 2, '2023-01-18'),
(5, 104, 5, '2023-01-19'),
(6, 105, 4, '2023-01-20'),
(7, 106, 3, '2023-01-21'),
(8, 102, 5, '2023-01-22');
GOQuery to Calculate 50th and 90th Percentiles:
SELECT
APPROX_PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY SatisfactionScore) AS MedianScore,
APPROX_PERCENTILE_DISC(0.90) WITHIN GROUP (ORDER BY SatisfactionScore) AS Top10PercentScore
FROM CustomerSatisfaction;Output:
| MedianScore | Top10PercentScore |
|---|---|
| 4 | 5 |
This output reveals that the median satisfaction score is 4, and the top 10% of scores are 5, indicating a high level of satisfaction among the top-tier customers.
Example 2: Store-Level Satisfaction Analysis 🏪
Next, let’s analyze satisfaction scores at different store locations to identify trends and areas for improvement.
Query for Store-Level Analysis:
SELECT
StoreID,
APPROX_PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY SatisfactionScore) AS MedianScore,
APPROX_PERCENTILE_DISC(0.90) WITHIN GROUP (ORDER BY SatisfactionScore) AS Top10PercentScore
FROM CustomerSatisfaction
GROUP BY StoreID;
Output:
| StoreID | MedianScore | Top10PercentScore |
|---|---|---|
| 101 | 3 | 5 |
| 102 | 4 | 5 |
| 103 | 4 | 4 |
| 104 | 5 | 5 |
| 105 | 4 | 4 |
| 106 | 3 | 3 |
This analysis helps identify which stores are excelling in customer satisfaction and which may need targeted improvements.
Example 3: Customer Segmentation by Satisfaction Levels 📈
To further analyze the data, let’s segment customers into different satisfaction levels based on key percentiles.
Step 1: Calculate Percentiles
-- Calculate the 25th, 50th, and 75th percentiles
SELECT
APPROX_PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SatisfactionScore) AS Q1,
APPROX_PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY SatisfactionScore) AS Q2,
APPROX_PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SatisfactionScore) AS Q3
INTO #Percentiles
FROM CustomerSatisfaction;
Step 2: Segment Customers
-- Join with the Percentiles table to categorize customers
SELECT
cs.CustomerID,
cs.SatisfactionScore,
CASE
WHEN cs.SatisfactionScore <= p.Q1 THEN 'Low'
WHEN cs.SatisfactionScore <= p.Q2 THEN 'Medium'
WHEN cs.SatisfactionScore <= p.Q3 THEN 'High'
ELSE 'Very High'
END AS SatisfactionLevel
FROM
CustomerSatisfaction cs
CROSS JOIN
#Percentiles p;Cleanup
-- Drop the temporary table
DROP TABLE #Percentiles;Explanation:
- Calculate Percentiles:
- The first step calculates the 25th (
Q1), 50th (Q2), and 75th (Q3) percentiles and stores them in a temporary table#Percentiles.
- The first step calculates the 25th (
- Segment Customers:
- The second step uses these percentile values to categorize each customer’s satisfaction score into levels: ‘Low’, ‘Medium’, ‘High’, or ‘Very High’.
- Cleanup:
- Finally, the temporary table
#Percentilesis dropped to clean up the session.
- Finally, the temporary table
Analyzing Low Satisfaction Scores:
- Identify stores with the lowest 10th percentile satisfaction scores:
SELECT
StoreID,
APPROX_PERCENTILE_DISC(0.10) WITHIN GROUP (ORDER BY SatisfactionScore) AS Low10PercentScore
FROM CustomerSatisfaction
GROUP BY StoreID;
Comparing Satisfaction Over Time:
- Compare median satisfaction scores between two periods:
SELECT
'Period 1' AS Period,
APPROX_PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY SatisfactionScore) AS MedianScore
FROM CustomerSatisfaction
WHERE ReviewDate BETWEEN '2023-01-15' AND '2023-01-18'
UNION ALL
SELECT
'Period 2' AS Period,
APPROX_PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY SatisfactionScore) AS MedianScore
FROM CustomerSatisfaction
WHERE ReviewDate BETWEEN '2023-01-19' AND '2023-01-22';3. Identifying High-Performing Stores:
- List stores with a 90th percentile satisfaction score of 5:
SELECT StoreID
FROM CustomerSatisfaction
GROUP BY StoreID
HAVING APPROX_PERCENTILE_DISC(0.90) WITHIN GROUP (ORDER BY SatisfactionScore) = 5;Conclusion 🏁
The APPROX_PERCENTILE_DISC function in SQL Server 2022 is a robust tool for efficiently estimating discrete percentiles. It offers a quick and practical solution for analyzing large datasets, making it invaluable for businesses looking to gain insights into customer behavior, product performance, and more. Whether you’re assessing customer satisfaction, analyzing sales data, or exploring other metrics, the APPROX_PERCENTILE_DISC function provides a clear and concise way to understand your data. Happy querying! 🎉
For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.