Detect Stock Price Spike Compared to Previous Day

Mr. Raju, he is my friend and he is working on the Stock market project. He got a requirement to create a dashboard which show that Spike of the Stock price. We need to get the list of the Stock which price is either increase or decrease by 5%. In a simple term we say that we have 3 stocks as below

Ticker	TradeDate	ClosePrice
AAAA	2025-12-01	100
AAAA	2025-12-02	106
AAAA	2025-12-03	102
BBBB	2025-12-01	200
BBBB	2025-12-02	170
BBBB	2025-12-03	190
CCCC	2025-12-01	50
CCCC	2025-12-02	52
CCCC	2025-12-03	56

In this example if we see Ticker AAAA value of this fund is 106 which is more than yesterday price, here price gain more then 5% same for the Ticker BBBB is drop less than 5% for the ‘2025-12-02’, we need to write the script to get this list and for the Ticker CCCC has normal spike.

For this demo we are creating a table and inserting the data into this table.

--Creating a tble CREATE TABLE StockPrices (     Ticker   VARCHAR(10),     TradeDate     DATE,     ClosePrice    DECIMAL(10,2) );  --Inserting few records insert into StockPrices (Ticker,TradeDate,ClosePrice) values ('AAAA','2025-12-01',  100), ('AAAA','2025-12-02',  106), ('AAAA','2025-12-03',  102), ('BBBB','2025-12-01',   200), ('BBBB','2025-12-02',   170), ('BBBB','2025-12-03',   190), ('CCCC','2025-12-01',   50 ), ('CCCC','2025-12-02',   52 ), ('CCCC','2025-12-03',   56 )

See the record in the table.

Now we are writing the script to get the Spike Ticker.

; WITH cte      AS (SELECT ticker,                 tradedate,                 closeprice,                 Lag(closeprice)                   OVER (                     partition BY ticker                     ORDER BY tradedate) AS PrevDayPrice,                 Round(( closeprice - Lag(closeprice)                                        OVER (                                          partition BY ticker                                          ORDER BY tradedate) ) * 100.0 / Lag(                       closeprice)                       OVER (                         partition BY ticker                         ORDER BY tradedate)                 , 2)                    AS PriceChangePct          FROM   stockprices) SELECT * FROM   cte WHERE  Abs(pricechangepct) > 5

 See the result.

Here price change pct negative (-) shows that Stock is in loss and positive means Stock is in profit.