In this lesson, you will learn how to use the Coefficient of Variation (CV) to compare the relative stability of two different datasets, even when they operate on completely different scales.
Why Standard Deviation Isn't Enough
When we calculate the standard deviation using STDDEV_SAMP(value), we get a measure of spread in the same units as our data. If you are comparing the price volatility of a $10 coffee and a $100,000 car, the car will naturally have a much higher standard deviation simply because its price range is larger.
To see which item is actually more "volatile" relative to its price, we use the Coefficient of Variation (CV). The formula is simple:
CV = Standard Deviation / Mean
Implementing CV in SQL
Because the CV normalizes the data, it allows us to compare "apples to oranges." Let’s look at how we calculate this in a single query:
SELECT
product_category,
STDDEV_SAMP(price) / AVG(price) AS coefficient_of_variation
FROM sales_data
GROUP BY product_category;
In this example, if the "Coffee" category has a CV of 0.05 and the "Automotive" category has a CV of 0.02, we can definitively say that even though the automotive prices fluctuate by much larger dollar amounts, the coffee prices are actually more volatile relative to their average cost.
Why Use This?
This technique is vital when you are monitoring data quality across different tables or analyzing business metrics that exist at different magnitudes. By dividing the standard deviation by the mean, you strip away the "scale" of the data, leaving you with a dimensionless number that represents the consistency of your metrics. A lower CV indicates higher consistency, while a higher CV signals that the data is spread widely relative to its average.
Key Takeaway: The Coefficient of Variation allows you to compare the relative dispersion of datasets with different units or scales by dividing the standard deviation by the mean.