How do I add multiple conditions in the code with only one condition

[Zohaib Ahmed]

I am attempting to implement multiple conditions on a stock. As indicated by the condition below, buy price is currently applied solely as -3%; however, I aim to extend this to -4%, -5%, -6%, and -7%. Additionally, the sell price is set at only 2%, but I wish to include sell prices at 3%, 2%, 1%, and 0.5% for each buy price value.

Furthermore, a threshold condition of -3% is executed in the 'else' case. I am also interested in testing the same scenario with -2%. How do I run it in a loop that each buy price is run against all four sell prices and then the same thing runs over again with the threshold condition of -2%?



-- Create and use the stock database
CREATE DATABASE stock;
USE stock;

-- Create a table to hold stock data
CREATE TABLE stock_data (
Date DATE,
Open FLOAT,
High FLOAT,
Low FLOAT,
Close FLOAT,
AdjClose FLOAT,
Volume BIGINT
);

-- Select all data from the stock_data table
SELECT * FROM stockanalysis.stock_data;

-- Complex query with CTEs for analyzing stock data
WITH cte AS (
SELECT
*,
LEAD(AdjClose, 1) OVER () AS NextDayAdjClose,
ROUND((Open - LEAD(AdjClose, 1) OVER ()) / LEAD(AdjClose, 1) Over(), 2) AS PrevVersusOpenPercentage
FROM
stockanalysis.stock_data
), SELLPRICE AS (
SELECT
*,
CASE
WHEN prevVersusOpenPercentage >= -0.03 THEN NextDayAdjClose * (1 + (-0.03))
ELSE open * (1 + (-0.03))
END AS BuyPrice
FROM cte
), SELL_SIGNALS AS (
SELECT
*,
BuyPrice * (1.02) AS SellPrice, -- (1 + 2%) where 2% is the sell price
CASE WHEN BuyPrice > Low THEN 'Y' ELSE 'N' END AS BuySignal,
CASE WHEN Close > (BuyPrice * (1.02)) THEN 'Y' ELSE 'N' END AS SellSameDayClose,
CASE WHEN GREATEST(AdjClose, LAG(High, 1) OVER ()) > (BuyPrice * (1.02)) THEN 'Y' ELSE 'N' END AS SellNextDay,
CASE WHEN GREATEST(AdjClose, LAG(High, 1) OVER (), LAG(High, 2) OVER ()) > (BuyPrice * (1.02)) THEN 'Y' ELSE 'N' END AS SellNext3Days,
CASE WHEN GREATEST(AdjClose, LAG(High, 1) OVER (), LAG(High, 2) OVER (), LAG(High, 3) OVER (), LAG(High, 4) OVER (), LAG(High, 5) OVER (), LAG(High, 6) OVER (), LAG(High, 7) OVER ()) > (BuyPrice * (1.02)) THEN 'Y' ELSE 'N' END AS SellNext7Days,
CASE WHEN GREATEST(AdjClose, LAG(High, 1) OVER (), LAG(High, 2) OVER (), LAG(High, 3) OVER (), LAG(High, 4) OVER (), LAG(High, 5) OVER (), LAG(High, 6) OVER (), LAG(High, 7) OVER (), LAG(High, 8) OVER (), LAG(High, 9) OVER (), LAG(High, 10) OVER ()) > (BuyPrice * (1.02)) THEN 'Y' ELSE 'N' END AS SellNext10Days
FROM
SELLPRICE
),
CATEGORIZED AS (
SELECT
*,
CASE
WHEN BuySignal = 'N' THEN 'No Buy'
WHEN SellSameDayClose = 'Y' THEN 'Sell - Same day'
WHEN SellNextDay = 'Y' THEN 'Sell - Next Day'
WHEN SellNext3Days = 'Y' THEN 'Sell - Next 3 Days'
WHEN SellNext7Days = 'Y' THEN 'Sell - Next 7 Days'
WHEN SellNext10Days = 'Y' THEN 'Sell - Next 10 Days'
ELSE 'Buy But Not Sell'
END AS Category
FROM
SELL_SIGNALS
)
SELECT * FROM CATEGORIZED;

SELECT
Category,
COUNT(*) AS Count
FROM
CATEGORIZED
GROUP BY
Category;
SELECT * FROM CATEGORIZED;