Snippet Name: SQL_Rolling_Percentage_Calculation
Tags:
Created Date: 2025-06-16 13:12:46
Last Modified Date: 2025-05-31 10:32:09
Dependencies:
DBA Notes:
Sample Output:
Title: SQL Rolling Percentage Calculation
Author: user
i have attached a list of stock prices and dates for a specific stock. can you help me create a SQL Server script that uses that data as a Common Table Expression and calculates the rolling percentage of increases or decreases over time
Author: ChatGPT
Sure! Please upload the file containing the stock prices and dates, and I'll generate a SQL Server script that loads the data into a Common Table Expression (CTE) and calculates the rolling percentage change over time.
Author: user
Price Effective Date
$18.05 11/01/2024
$16.46 08/01/2024
$16.25 05/01/2024
$15.20 03/01/2024
$15.10 11/01/2023
$14.75 08/01/2023
$14.97 05/01/2023
$14.55 03/01/2023
$13.19 11/01/2022
$13.84 08/01/2022
$14.91 05/01/2022
$13.76 04/15/2022
$13.76 03/01/2022
$13.28 11/01/2021
$12.62 08/01/2021
$12.26 05/01/2021
$12.04 03/01/2021
$11.59 11/01/2020
Author: ChatGPT
Here???s a SQL Server script that:
- Loads the provided stock price data into a Common Table Expression (CTE).
- Calculates the rolling percentage change in stock price compared to the previous date.
```sql
WITH StockPrices AS (
SELECT * FROM (VALUES
(CAST(18.05 AS DECIMAL(10,2)), '2024-11-01'),
(16.46, '2024-08-01'),
(16.25, '2024-05-01'),
(15.20, '2024-03-01'),
(15.10, '2023-11-01'),
(14.75, '2023-08-01'),
(14.97, '2023-05-01'),
(14.55, '2023-03-01'),
(13.19, '2022-11-01'),
(13.84, '2022-08-01'),
(14.91, '2022-05-01'),
(13.76, '2022-04-15'),
(13.76, '2022-03-01'),
(13.28, '2021-11-01'),
(12.62, '2021-08-01'),
(12.26, '2021-05-01'),
(12.04, '2021-03-01'),
(11.59, '2020-11-01')
) AS T(Price, EffectiveDate)
)
SELECT
sp.EffectiveDate,
sp.Price,
LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate) AS PreviousPrice,
CASE
WHEN LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate) IS NOT NULL THEN
((sp.Price - LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate)) / LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate)) * 100
ELSE NULL
END AS PercentageChange
FROM StockPrices sp
ORDER BY sp.EffectiveDate;
```
### Explanation:
- The `StockPrices` CTE holds the stock price data.
- The `LAG()` function retrieves the previous price based on the `EffectiveDate`.
- The percentage change is calculated using:
\[
\text{Percentage Change} = \left(\frac{\text{Current Price} - \text{Previous Price}}{\text{Previous Price}}\right) \times 100
\]
- The final result lists the `EffectiveDate`, `Price`, `PreviousPrice`, and `PercentageChange`.
Would you like any modifications, such as calculating rolling averages over multiple periods?
Author: user
yes, please add a rolling average
Author: ChatGPT
Here's an enhanced version of your SQL Server script that calculates both:
1. **Rolling Percentage Change** compared to the previous period.
2. **Rolling Average Price** over the last 3 periods.
```sql
WITH StockPrices AS (
SELECT * FROM (VALUES
(CAST(18.05 AS DECIMAL(10,2)), '2024-11-01'),
(16.46, '2024-08-01'),
(16.25, '2024-05-01'),
(15.20, '2024-03-01'),
(15.10, '2023-11-01'),
(14.75, '2023-08-01'),
(14.97, '2023-05-01'),
(14.55, '2023-03-01'),
(13.19, '2022-11-01'),
(13.84, '2022-08-01'),
(14.91, '2022-05-01'),
(13.76, '2022-04-15'),
(13.76, '2022-03-01'),
(13.28, '2021-11-01'),
(12.62, '2021-08-01'),
(12.26, '2021-05-01'),
(12.04, '2021-03-01'),
(11.59, '2020-11-01')
) AS T(Price, EffectiveDate)
)
SELECT
sp.EffectiveDate,
sp.Price,
LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate) AS PreviousPrice,
CASE
WHEN LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate) IS NOT NULL THEN
((sp.Price - LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate)) / LAG(sp.Price) OVER (ORDER BY sp.EffectiveDate)) * 100
ELSE NULL
END AS PercentageChange,
AVG(sp.Price) OVER (ORDER BY sp.EffectiveDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RollingAvgPrice
FROM StockPrices sp
ORDER BY sp.EffectiveDate;
```
### Enhancements:
- **`RollingAvgPrice` Calculation**:
- Uses `AVG()` with a **window of 3 periods** (`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`).
- This ensures the rolling average price is computed over the last 3 available prices.
### Output Example:
| EffectiveDate | Price | PreviousPrice | PercentageChange | RollingAvgPrice |
|--------------|--------|---------------|------------------|-----------------|
| 2020-11-01 | 11.59 | NULL | NULL | 11.59 |
| 2021-03-01 | 12.04 | 11.59 | 3.88% | 11.82 |
| 2021-05-01 | 12.26 | 12.04 | 1.83% | 11.96 |
| 2021-08-01 | 12.62 | 12.26 | 2.94% | 12.31 |
| 2021-11-01 | 13.28 | 12.62 | 5.23% | 12.72 |
Let me know if you need any refinements! ????
Author: user
by my ballpark calculations, it seems the value went up 35.7% over the course of the perionds identified, does that sound correct? select (((53195 * 18.05) - (53195 * 11.59)) / (53195 * 18.05))
Author: ChatGPT
Let's verify your percentage increase calculation over the given periods using the following formula:
\[
\text{Percentage Increase} = \left(\frac{\text{Final Price} - \text{Initial Price}}{\text{Initial Price}}\right) \times 100
\]
Given:
- **Initial Price (first recorded)** = **$11.59** (from **2020-11-01**)
- **Final Price (latest recorded)** = **$18.05** (from **2024-11-01**)
Substituting the values:
\[
\left(\frac{18.05 - 11.59}{11.59}\right) \times 100
\]
\[
\left(\frac{6.46}{11.59}\right) \times 100
\]
\[
0.5574 \times 100 = 55.74\%
\]
So, the total percentage increase over the entire period is **55.74%**, not 35.7%.
### Understanding Your Formula:
Your formula:
\[
\left(\frac{(53195 \times 18.05) - (53195 \times 11.59)}{(53195 \times 18.05)}\right)
\]
Effectively simplifies to:
\[
\left(\frac{18.05 - 11.59}{18.05}\right) \times 100
\]
\[
\left(\frac{6.46}{18.05}\right) \times 100
\]
\[
35.79\%
\]
This calculates the percentage decrease relative to the final price (18.05), rather than the initial price (11.59). However, the standard way to calculate total growth percentage is relative to the starting value, which gives **55.74%**. ????