📊 280 SQL QUESTIONS & ANSWERS
Using JIOFIN (Jio Financial Services) Stock Market Data
📑 TABLE OF CONTENTS
SECTION 1: DATA DEFINITION LANGUAGE (DDL) Q1-Q10
Q1 Create the JIOFIN_STOCK table.
CREATE TABLE JIOFIN_STOCK (
TradeDate DATE PRIMARY KEY,
Series VARCHAR(3),
OpenPrice DECIMAL(10,2),
HighPrice DECIMAL(10,2),
LowPrice DECIMAL(10,2),
PrevClose DECIMAL(10,2),
LTP DECIMAL(10,2),
ClosePrice DECIMAL(10,2),
VWAP DECIMAL(10,2),
High52Week DECIMAL(10,2),
Low52Week DECIMAL(10,2),
Volume BIGINT,
Value DECIMAL(20,2),
NumberOfTrades BIGINT
);
Q2 Add a column 'PriceChange'.
ALTER TABLE JIOFIN_STOCK ADD PriceChange DECIMAL(10,2);
Q3 Add a column 'PercentChange'.
ALTER TABLE JIOFIN_STOCK ADD PercentChange DECIMAL(8,2);
Q4 Add a column 'DayOfWeek'.
ALTER TABLE JIOFIN_STOCK ADD DayOfWeek VARCHAR(10);
Q5 Create MONTHLY_SUMMARY table.
CREATE TABLE JIOFIN_MONTHLY_SUMMARY (
TradeMonth VARCHAR(7) PRIMARY KEY,
AvgOpen DECIMAL(10,2),
AvgHigh DECIMAL(10,2),
AvgLow DECIMAL(10,2),
AvgClose DECIMAL(10,2),
TotalVolume BIGINT,
AvgVolume BIGINT,
MaxPrice DECIMAL(10,2),
MinPrice DECIMAL(10,2),
AvgTrades BIGINT,
TotalValue DECIMAL(20,2)
);
Q6 Add PRIMARY KEY on TradeDate.
ALTER TABLE JIOFIN_STOCK ADD PRIMARY KEY (TradeDate);
Q7 Add CHECK constraint on PriceChange.
ALTER TABLE JIOFIN_STOCK MODIFY PriceChange DECIMAL(10,2) NOT NULL;
Q8 Create index on ClosePrice.
CREATE INDEX idx_close_price ON JIOFIN_STOCK(ClosePrice);
Q9 Create index on TradeDate.
CREATE INDEX idx_trade_date ON JIOFIN_STOCK(TradeDate);
Q10 Create view for high volume days.
CREATE VIEW HighVolumeDays AS
SELECT TradeDate, ClosePrice, Volume, NumberOfTrades
FROM JIOFIN_STOCK
WHERE Volume > 10000000
ORDER BY Volume DESC;SECTION 2: INSERT - DATA LOADING Q11-Q20
Q11 Insert record for 28-Jul-2026.
INSERT INTO JIOFIN_STOCK VALUES (
'2026-07-28', 'EQ', 236.00, 239.21, 235.51, 236.17,
237.50, 237.08, 237.30, 338.60, 223.30,
11263195, 2672724599.46, 85415
);
Q12 Insert multiple records for 27-Jul and 24-Jul.
INSERT INTO JIOFIN_STOCK VALUES
('2026-07-27', 'EQ', 237.00, 237.40, 235.26, 234.71, 236.24, 236.17, 236.13, 338.60, 223.30, 6984152, 1649186077.86, 59868),
('2026-07-24', 'EQ', 232.00, 235.48, 231.21, 233.16, 234.98, 234.71, 233.28, 338.60, 223.30, 10217898, 2383594466.23, 72676);
Q13 Insert record with NULL VWAP.
INSERT INTO JIOFIN_STOCK (TradeDate, Series, OpenPrice, HighPrice, LowPrice, PrevClose, LTP, ClosePrice, Volume, Value, NumberOfTrades)
VALUES ('2026-07-20', 'EQ', 243.00, 243.11, 238.10, 242.98, 238.95, 238.92, 19257829, 4614668378.87, 133690);
Q14 Update PriceChange for all records.
UPDATE JIOFIN_STOCK SET PriceChange = ClosePrice - PrevClose;
Q15 Update PercentChange for all records.
UPDATE JIOFIN_STOCK SET PercentChange = ROUND((ClosePrice - PrevClose) * 100.0 / PrevClose, 2);
Q16 Update DayOfWeek for all records.
UPDATE JIOFIN_STOCK SET DayOfWeek = DAYNAME(TradeDate);
Q17 Insert record with high volume (5+ crores).
INSERT INTO JIOFIN_STOCK VALUES (
'2026-07-17', 'EQ', 247.50, 249.95, 242.00, 235.65,
242.70, 242.98, 245.59, 338.60, 223.30,
95978862, 23571143871.70, 388967
);
Q18 Insert 50 days using a stored procedure.
DELIMITER //
CREATE PROCEDURE LoadData()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 50 DO
INSERT INTO JIOFIN_STOCK (TradeDate, Series, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume, Value)
VALUES (DATE_SUB(CURDATE(), INTERVAL i DAY), 'EQ', 200 + RAND()*100, 210 + RAND()*100, 190 + RAND()*100, 205 + RAND()*100, FLOOR(RAND()*10000000), FLOOR(RAND()*1000000000));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
Q19 Insert weekdays only.
INSERT INTO JIOFIN_STOCK (TradeDate, Series, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume, Value)
SELECT TradeDate, 'EQ', OpenPrice, HighPrice, LowPrice, ClosePrice, Volume, Value
FROM JIOFIN_STOCK
WHERE DAYOFWEEK(TradeDate) NOT IN (1, 7);
Q20 Insert with random variation for backtesting.
INSERT INTO JIOFIN_STOCK (TradeDate, Series, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume, Value)
SELECT
DATE_ADD('2026-07-28', INTERVAL n DAY) AS TradeDate,
'EQ',
ROUND(200 + RAND()*100, 2),
ROUND(210 + RAND()*100, 2),
ROUND(190 + RAND()*100, 2),
ROUND(205 + RAND()*100, 2),
FLOOR(RAND()*50000000),
FLOOR(RAND()*5000000000)
FROM (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) numbers;SECTION 3: SELECT BASICS Q21-Q40
Q21 Display all records.
SELECT * FROM JIOFIN_STOCK;
Q22 Display TradeDate, Open, High, Low, Close.
SELECT TradeDate, OpenPrice, HighPrice, LowPrice, ClosePrice FROM JIOFIN_STOCK;
Q23 Display TradeDate, Volume, NumberOfTrades.
SELECT TradeDate, Volume, NumberOfTrades FROM JIOFIN_STOCK;
Q24 Display the 10 most recent trading days.
SELECT * FROM JIOFIN_STOCK ORDER BY TradeDate DESC LIMIT 10;
Q25 Display records where ClosePrice > 300.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > 300;
Q26 Display records where Volume > 1 crore.
SELECT * FROM JIOFIN_STOCK WHERE Volume > 10000000;
Q27 Display TradeDate, ClosePrice, VWAP.
SELECT TradeDate, ClosePrice, VWAP FROM JIOFIN_STOCK;
Q28 Display records where HighPrice > 350.
SELECT * FROM JIOFIN_STOCK WHERE HighPrice > 350;
Q29 Display records where LowPrice < 230.
SELECT * FROM JIOFIN_STOCK WHERE LowPrice < 230;
Q30 Display TradeDate and Price Range (High-Low).
SELECT TradeDate, HighPrice - LowPrice AS PriceRange FROM JIOFIN_STOCK;
Q31 Display records with Series 'EQ'.
SELECT * FROM JIOFIN_STOCK WHERE Series = 'EQ';
Q32 Display TradeDate, ClosePrice, 52W High/Low.
SELECT TradeDate, ClosePrice, High52Week, Low52Week FROM JIOFIN_STOCK;
Q33 Display top 5 highest volume days.
SELECT * FROM JIOFIN_STOCK ORDER BY Volume DESC LIMIT 5;
Q34 Display top 5 lowest volume days.
SELECT * FROM JIOFIN_STOCK ORDER BY Volume ASC LIMIT 5;
Q35 Display records where ClosePrice is between 250 and 300.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice BETWEEN 250 AND 300;
Q36 Display distinct Series values.
SELECT DISTINCT Series FROM JIOFIN_STOCK;
Q37 Display TradeDate and DayOfWeek.
SELECT TradeDate, DAYNAME(TradeDate) AS DayOfWeek FROM JIOFIN_STOCK;
Q38 Display total number of records.
SELECT COUNT(*) AS TotalRecords FROM JIOFIN_STOCK;
Q39 Display with column aliases.
SELECT TradeDate AS Date, OpenPrice AS Opening, ClosePrice AS Closing FROM JIOFIN_STOCK;
Q40 Display records where NumberOfTrades > 100,000.
SELECT * FROM JIOFIN_STOCK WHERE NumberOfTrades > 100000;SECTION 4: WHERE CLAUSE Q41-Q60
Q41 Records where ClosePrice > PrevClose (gained).
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > PrevClose;
Q42 Records where stock closed lower than opened.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice < OpenPrice;
Q43 Records where stock made new 52-week high.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice = High52Week;
Q44 Records where Volume between 5 crore and 10 crore.
SELECT * FROM JIOFIN_STOCK WHERE Volume BETWEEN 50000000 AND 100000000;
Q45 Records where NumberOfTrades between 50K and 100K.
SELECT * FROM JIOFIN_STOCK WHERE NumberOfTrades BETWEEN 50000 AND 100000;
Q46 Records where VWAP is NULL.
SELECT * FROM JIOFIN_STOCK WHERE VWAP IS NULL;
Q47 Records where PriceChange is positive.
SELECT * FROM JIOFIN_STOCK WHERE PriceChange > 0;
Q48 Records where PercentChange > 2%.
SELECT * FROM JIOFIN_STOCK WHERE PercentChange > 2.00;
Q49 Records where PercentChange < -2%.
SELECT * FROM JIOFIN_STOCK WHERE PercentChange < -2.00;
Q50 Records from 2026 where ClosePrice > 250.
SELECT * FROM JIOFIN_STOCK WHERE YEAR(TradeDate) = 2026 AND ClosePrice > 250;
Q51 Records from July 2026.
SELECT * FROM JIOFIN_STOCK WHERE TradeDate BETWEEN '2026-07-01' AND '2026-07-31';
Q52 Records where stock closed at its high.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice = HighPrice;
Q53 Records where stock closed at its low.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice = LowPrice;
Q54 Records with Volume > average volume.
SELECT * FROM JIOFIN_STOCK WHERE Volume > (SELECT AVG(Volume) FROM JIOFIN_STOCK);
Q55 Records with NumberOfTrades > average.
SELECT * FROM JIOFIN_STOCK WHERE NumberOfTrades > (SELECT AVG(NumberOfTrades) FROM JIOFIN_STOCK);
Q56 Records within 5% of 52-week high.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice >= High52Week * 0.95;
Q57 Records within 5% of 52-week low.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice <= Low52Week * 1.05;
Q58 Records where VWAP > ClosePrice.
SELECT * FROM JIOFIN_STOCK WHERE VWAP > ClosePrice;
Q59 Records where stock gained but volume below average.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > PrevClose AND Volume < (SELECT AVG(Volume) FROM JIOFIN_STOCK);
Q60 Records where stock lost but volume above average.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice < PrevClose AND Volume > (SELECT AVG(Volume) FROM JIOFIN_STOCK);SECTION 5: ORDER BY, GROUP BY, HAVING Q61-Q80
Q61 Records sorted by ClosePrice descending.
SELECT * FROM JIOFIN_STOCK ORDER BY ClosePrice DESC;
Q62 Records sorted by Volume descending.
SELECT * FROM JIOFIN_STOCK ORDER BY Volume DESC;
Q63 Records sorted by TradeDate ascending.
SELECT * FROM JIOFIN_STOCK ORDER BY TradeDate ASC;
Q64 Records sorted by PercentChange descending.
SELECT * FROM JIOFIN_STOCK ORDER BY PercentChange DESC;
Q65 Group by month, count trading days.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, COUNT(*) AS TradingDays
FROM JIOFIN_STOCK GROUP BY DATE_FORMAT(TradeDate, '%Y-%m');
Q66 Group by DayOfWeek, average volume.
SELECT DayOfWeek, COUNT(*) AS DaysCount, AVG(Volume) AS AvgVolume
FROM JIOFIN_STOCK GROUP BY DayOfWeek;
Q67 Group by year, average close price.
SELECT YEAR(TradeDate) AS TradeYear, AVG(ClosePrice) AS AvgClose, MAX(ClosePrice) AS MaxClose, MIN(ClosePrice) AS MinClose
FROM JIOFIN_STOCK GROUP BY YEAR(TradeDate);
Q68 Group by month, total volume.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, SUM(Volume) AS TotalVolume
FROM JIOFIN_STOCK GROUP BY DATE_FORMAT(TradeDate, '%Y-%m');
Q69 Group by day of week, average price change.
SELECT DayOfWeek, AVG(PriceChange) AS AvgPriceChange, AVG(PercentChange) AS AvgPercentChange
FROM JIOFIN_STOCK GROUP BY DayOfWeek;
Q70 Group by year, total number of trades.
SELECT YEAR(TradeDate) AS TradeYear, SUM(NumberOfTrades) AS TotalTrades
FROM JIOFIN_STOCK GROUP BY YEAR(TradeDate);
Q71 Group by month, average VWAP.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, AVG(VWAP) AS AvgVWAP
FROM JIOFIN_STOCK WHERE VWAP IS NOT NULL GROUP BY DATE_FORMAT(TradeDate, '%Y-%m');
Q72 Months with average volume > 5 crore.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, AVG(Volume) AS AvgVolume
FROM JIOFIN_STOCK GROUP BY DATE_FORMAT(TradeDate, '%Y-%m') HAVING AVG(Volume) > 50000000;
Q73 Days with PriceChange > 5.
SELECT TradeDate, ClosePrice, PrevClose, PriceChange
FROM JIOFIN_STOCK WHERE PriceChange > 5 ORDER BY PriceChange DESC;
Q74 Days with PercentChange > 5%.
SELECT TradeDate, ClosePrice, PrevClose, PercentChange
FROM JIOFIN_STOCK WHERE PercentChange > 5 ORDER BY PercentChange DESC;
Q75 Days with PercentChange < -5%.
SELECT TradeDate, ClosePrice, PrevClose, PercentChange
FROM JIOFIN_STOCK WHERE PercentChange < -5 ORDER BY PercentChange ASC;
Q76 Group by month, average daily range.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, AVG(HighPrice - LowPrice) AS AvgDailyRange
FROM JIOFIN_STOCK GROUP BY DATE_FORMAT(TradeDate, '%Y-%m');
Q77 Group by month, max and min close.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, MAX(ClosePrice) AS MaxClose, MIN(ClosePrice) AS MinClose, MAX(ClosePrice) - MIN(ClosePrice) AS Range
FROM JIOFIN_STOCK GROUP BY DATE_FORMAT(TradeDate, '%Y-%m');
Q78 Months with more than 20 trading days.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS TradeMonth, COUNT(*) AS TradingDays
FROM JIOFIN_STOCK GROUP BY DATE_FORMAT(TradeDate, '%Y-%m') HAVING COUNT(*) > 20;
Q79 Group by year, average volume and total value.
SELECT YEAR(TradeDate) AS TradeYear, AVG(Volume) AS AvgVolume, SUM(Value) AS TotalValue, AVG(Value) AS AvgValue
FROM JIOFIN_STOCK GROUP BY YEAR(TradeDate);
Q80 Bullish days (Close > Open).
SELECT TradeDate, OpenPrice, ClosePrice, ClosePrice - OpenPrice AS Gain
FROM JIOFIN_STOCK WHERE ClosePrice > OpenPrice ORDER BY Gain DESC;SECTION 6: AGGREGATE FUNCTIONS Q81-Q100
Q81 Maximum ClosePrice.
SELECT MAX(ClosePrice) AS MaxPrice FROM JIOFIN_STOCK;
Q82 Minimum ClosePrice.
SELECT MIN(ClosePrice) AS MinPrice FROM JIOFIN_STOCK;
Q83 Average ClosePrice.
SELECT AVG(ClosePrice) AS AvgPrice FROM JIOFIN_STOCK;
Q84 Total volume traded.
SELECT SUM(Volume) AS TotalVolume FROM JIOFIN_STOCK;
Q85 Average volume per day.
SELECT AVG(Volume) AS AvgVolume FROM JIOFIN_STOCK;
Q86 Total value traded.
SELECT SUM(Value) AS TotalValue FROM JIOFIN_STOCK;
Q87 Average number of trades per day.
SELECT AVG(NumberOfTrades) AS AvgTrades FROM JIOFIN_STOCK;
Q88 Maximum volume in a single day.
SELECT MAX(Volume) AS MaxVolume FROM JIOFIN_STOCK;
Q89 Minimum volume in a single day.
SELECT MIN(Volume) AS MinVolume FROM JIOFIN_STOCK;
Q90 Total number of trading days.
SELECT COUNT(DISTINCT TradeDate) AS TradingDays FROM JIOFIN_STOCK;
Q91 Maximum price range (High - Low).
SELECT MAX(HighPrice - LowPrice) AS MaxRange FROM JIOFIN_STOCK;
Q92 Average price range per day.
SELECT AVG(HighPrice - LowPrice) AS AvgRange FROM JIOFIN_STOCK;
Q93 Total number of trades across all days.
SELECT SUM(NumberOfTrades) AS TotalTrades FROM JIOFIN_STOCK;
Q94 Maximum percentage gain in a single day.
SELECT MAX(PercentChange) AS MaxGain FROM JIOFIN_STOCK;
Q95 Maximum percentage loss in a single day.
SELECT MIN(PercentChange) AS MaxLoss FROM JIOFIN_STOCK;
Q96 Standard deviation of ClosePrice.
SELECT STD(ClosePrice) AS PriceStdDev FROM JIOFIN_STOCK;
Q97 Variance of ClosePrice.
SELECT VARIANCE(ClosePrice) AS PriceVariance FROM JIOFIN_STOCK;
Q98 Correlation between Volume and NumberOfTrades.
SELECT ROUND(CORR(Volume, NumberOfTrades), 4) AS Correlation FROM JIOFIN_STOCK;
Q99 Median ClosePrice.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ClosePrice) AS MedianPrice FROM JIOFIN_STOCK;
Q100 25th and 75th percentiles of ClosePrice.
SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY ClosePrice) AS Q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY ClosePrice) AS Q3
FROM JIOFIN_STOCK;SECTION 7: DATE FUNCTIONS Q101-Q120
Q101 Display in 'DD-MM-YYYY' format.
SELECT TradeDate, DATE_FORMAT(TradeDate, '%d-%m-%Y') AS FormattedDate FROM JIOFIN_STOCK;
Q102 Display day name.
SELECT TradeDate, DAYNAME(TradeDate) AS DayName FROM JIOFIN_STOCK;
Q103 Display month name.
SELECT TradeDate, MONTHNAME(TradeDate) AS MonthName FROM JIOFIN_STOCK;
Q104 Extract year.
SELECT TradeDate, YEAR(TradeDate) AS TradeYear FROM JIOFIN_STOCK;
Q105 Extract month number.
SELECT TradeDate, MONTH(TradeDate) AS TradeMonth FROM JIOFIN_STOCK;
Q106 Extract day of month.
SELECT TradeDate, DAY(TradeDate) AS DayOfMonth FROM JIOFIN_STOCK;
Q107 Extract quarter.
SELECT TradeDate, QUARTER(TradeDate) AS TradeQuarter FROM JIOFIN_STOCK;
Q108 Day of week number.
SELECT TradeDate, DAYOFWEEK(TradeDate) AS DayOfWeekNum FROM JIOFIN_STOCK;
Q109 Days where stock made new 52-week high.
SELECT TradeDate, ClosePrice, High52Week FROM JIOFIN_STOCK WHERE ClosePrice = High52Week;
Q110 First trading day of each month.
SELECT MIN(TradeDate) AS FirstTradingDay FROM JIOFIN_STOCK GROUP BY YEAR(TradeDate), MONTH(TradeDate);
Q111 Last trading day of each month.
SELECT MAX(TradeDate) AS LastTradingDay FROM JIOFIN_STOCK GROUP BY YEAR(TradeDate), MONTH(TradeDate);
Q112 Days between trades.
SELECT TradeDate, DATEDIFF(TradeDate, LAG(TradeDate) OVER (ORDER BY TradeDate)) AS DaysSincePrevious FROM JIOFIN_STOCK;
Q113 First quarter 2026.
SELECT * FROM JIOFIN_STOCK WHERE QUARTER(TradeDate) = 1 AND YEAR(TradeDate) = 2026;
Q114 Second quarter 2026.
SELECT * FROM JIOFIN_STOCK WHERE QUARTER(TradeDate) = 2 AND YEAR(TradeDate) = 2026;
Q115 July 2025 only.
SELECT * FROM JIOFIN_STOCK WHERE TradeDate BETWEEN '2025-07-01' AND '2025-07-31';
Q116 Last 30 days.
SELECT * FROM JIOFIN_STOCK WHERE DATEDIFF(CURDATE(), TradeDate) < 30;
Q117 Monday trading days.
SELECT * FROM JIOFIN_STOCK WHERE DAYNAME(TradeDate) = 'Monday';
Q118 Friday trading days.
SELECT * FROM JIOFIN_STOCK WHERE DAYNAME(TradeDate) = 'Friday';
Q119 Monthly average close by year and month.
SELECT YEAR(TradeDate) AS Year, MONTH(TradeDate) AS Month, AVG(ClosePrice) AS AvgClose
FROM JIOFIN_STOCK GROUP BY YEAR(TradeDate), MONTH(TradeDate);
Q120 Last 90 days.
SELECT * FROM JIOFIN_STOCK WHERE TradeDate >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);SECTION 8: STRING FUNCTIONS Q121-Q140
Q121 Series in uppercase.
SELECT TradeDate, UPPER(Series) AS SeriesUpper FROM JIOFIN_STOCK;
Q122 First 3 characters of DayOfWeek.
SELECT TradeDate, LEFT(DAYNAME(TradeDate), 3) AS ShortDay FROM JIOFIN_STOCK;
Q123 Date as string 'YYYYMMDD'.
SELECT TradeDate, DATE_FORMAT(TradeDate, '%Y%m%d') AS DateString FROM JIOFIN_STOCK;
Q124 Concatenate Open and Close.
SELECT TradeDate, CONCAT('O:', OpenPrice, ' C:', ClosePrice) AS PriceString FROM JIOFIN_STOCK;
Q125 Format ClosePrice with 2 decimals.
SELECT TradeDate, FORMAT(ClosePrice, 2) AS FormattedPrice FROM JIOFIN_STOCK;
Q126 Volume in 'K' format.
SELECT TradeDate, CONCAT(ROUND(Volume/1000, 2), 'K') AS VolumeInK FROM JIOFIN_STOCK;
Q127 Volume in 'M' format.
SELECT TradeDate, CONCAT(ROUND(Volume/1000000, 2), 'M') AS VolumeInM FROM JIOFIN_STOCK;
Q128 Replace 'EQ' with 'Equity'.
SELECT TradeDate, REPLACE(Series, 'EQ', 'Equity') AS SeriesName FROM JIOFIN_STOCK;
Q129 PriceChange with '+' sign.
SELECT TradeDate, CONCAT(CASE WHEN PriceChange >= 0 THEN '+' ELSE '' END, PriceChange) AS PriceChangeDisplay FROM JIOFIN_STOCK;
Q130 PercentChange with '+' sign.
SELECT TradeDate, CONCAT(CASE WHEN PercentChange >= 0 THEN '+' ELSE '' END, PercentChange, '%') AS PercentDisplay FROM JIOFIN_STOCK;
Q131 Extract day number.
SELECT TradeDate, DAY(TradeDate) AS DayNumber FROM JIOFIN_STOCK;
Q132 Extract month name.
SELECT TradeDate, MONTHNAME(TradeDate) AS MonthName FROM JIOFIN_STOCK;
Q133 'Day, Month Date, Year' format.
SELECT TradeDate, DATE_FORMAT(TradeDate, '%W, %M %e, %Y') AS LongDate FROM JIOFIN_STOCK;
Q134 First word of DayOfWeek.
SELECT TradeDate, SUBSTRING_INDEX(DAYNAME(TradeDate), ' ', 1) AS FirstWord FROM JIOFIN_STOCK;
Q135 Length of date string.
SELECT TradeDate, LENGTH(DATE_FORMAT(TradeDate, '%Y-%m-%d')) AS DateLength FROM JIOFIN_STOCK;
Q136 Price with currency.
SELECT TradeDate, CONCAT('₹', FORMAT(ClosePrice, 2)) AS PriceWithCurrency FROM JIOFIN_STOCK;
Q137 Summary string for each day.
SELECT TradeDate, CONCAT('Day: ', DAYNAME(TradeDate), ', Price: ', ClosePrice, ', Volume: ', FORMAT(Volume, 0)) AS Summary FROM JIOFIN_STOCK;
Q138 Month abbreviation.
SELECT TradeDate, DATE_FORMAT(TradeDate, '%b') AS MonthAbbr FROM JIOFIN_STOCK;
Q139 Day of week abbreviation.
SELECT TradeDate, DATE_FORMAT(TradeDate, '%a') AS DayAbbr FROM JIOFIN_STOCK;
Q140 JSON-like string.
SELECT TradeDate, CONCAT('{"date":"', TradeDate, '","open":', OpenPrice, ',"close":', ClosePrice, ',"volume":', Volume, '}') AS JSONData FROM JIOFIN_STOCK;SECTION 9: ADVANCED SUBQUERIES Q141-Q160
Q141 ClosePrice above 30-day moving average.
SELECT S1.TradeDate, S1.ClosePrice,
(SELECT AVG(ClosePrice) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 30 DAY) AND S1.TradeDate) AS MA30
FROM JIOFIN_STOCK S1
WHERE S1.ClosePrice > (SELECT AVG(ClosePrice) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 30 DAY) AND S1.TradeDate);
Q142 Volume greater than previous day.
SELECT S1.TradeDate, S1.Volume,
(SELECT S2.Volume FROM JIOFIN_STOCK S2 WHERE S2.TradeDate < S1.TradeDate ORDER BY S2.TradeDate DESC LIMIT 1) AS PrevDayVolume
FROM JIOFIN_STOCK S1
WHERE S1.Volume > (SELECT S2.Volume FROM JIOFIN_STOCK S2 WHERE S2.TradeDate < S1.TradeDate ORDER BY S2.TradeDate DESC LIMIT 1);
Q143 PercentChange in top 10%.
WITH PercentileData AS (
SELECT PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY PercentChange) AS P90 FROM JIOFIN_STOCK
)
SELECT TradeDate, PercentChange FROM JIOFIN_STOCK
WHERE PercentChange >= (SELECT P90 FROM PercentileData) ORDER BY PercentChange DESC;
Q144 Higher high than previous 5 days.
SELECT S1.TradeDate, S1.HighPrice
FROM JIOFIN_STOCK S1
WHERE S1.HighPrice > (SELECT MAX(S2.HighPrice) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate < S1.TradeDate AND S2.TradeDate >= DATE_SUB(S1.TradeDate, INTERVAL 5 DAY));
Q145 Lower low than previous 5 days.
SELECT S1.TradeDate, S1.LowPrice
FROM JIOFIN_STOCK S1
WHERE S1.LowPrice < (SELECT MIN(S2.LowPrice) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate < S1.TradeDate AND S2.TradeDate >= DATE_SUB(S1.TradeDate, INTERVAL 5 DAY));
Q146 Days with 3 consecutive gains.
SELECT S1.TradeDate, S1.ClosePrice, S1.PriceChange
FROM JIOFIN_STOCK S1
WHERE S1.PriceChange > 0
AND (SELECT S2.PriceChange FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)) > 0
AND (SELECT S3.PriceChange FROM JIOFIN_STOCK S3 WHERE S3.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 2 DAY)) > 0;
Q147 Days with 3 consecutive losses.
SELECT S1.TradeDate, S1.ClosePrice, S1.PriceChange
FROM JIOFIN_STOCK S1
WHERE S1.PriceChange < 0
AND (SELECT S2.PriceChange FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)) < 0
AND (SELECT S3.PriceChange FROM JIOFIN_STOCK S3 WHERE S3.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 2 DAY)) < 0;
Q148 Days with above-average volume and price gain.
SELECT TradeDate, ClosePrice, Volume, PriceChange
FROM JIOFIN_STOCK
WHERE Volume > (SELECT AVG(Volume) FROM JIOFIN_STOCK)
AND PriceChange > 0
ORDER BY Volume DESC;
Q149 Days with below-average volume and price loss.
SELECT TradeDate, ClosePrice, Volume, PriceChange
FROM JIOFIN_STOCK
WHERE Volume < (SELECT AVG(Volume) FROM JIOFIN_STOCK)
AND PriceChange < 0;
Q150 Days closing above 52-week high.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > High52Week;
Q151 Days closing below 52-week low.
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice < Low52Week;
Q152 Top 5 best performing days.
SELECT TradeDate, ClosePrice, PrevClose, PercentChange
FROM JIOFIN_STOCK ORDER BY PercentChange DESC LIMIT 5;
Q153 Top 5 worst performing days.
SELECT TradeDate, ClosePrice, PrevClose, PercentChange
FROM JIOFIN_STOCK ORDER BY PercentChange ASC LIMIT 5;
Q154 Days with highest volume on green days.
SELECT TradeDate, Volume, PriceChange, PercentChange
FROM JIOFIN_STOCK
WHERE PriceChange > 0
ORDER BY Volume DESC LIMIT 10;
Q155 Days with highest volume on red days.
SELECT TradeDate, Volume, PriceChange, PercentChange
FROM JIOFIN_STOCK
WHERE PriceChange < 0
ORDER BY Volume DESC LIMIT 10;
Q156 Days where VWAP is significantly different from Close.
SELECT TradeDate, ClosePrice, VWAP, ABS(ClosePrice - VWAP) AS Difference
FROM JIOFIN_STOCK
WHERE VWAP IS NOT NULL
AND ABS(ClosePrice - VWAP) > 5
ORDER BY Difference DESC;
Q157 Days with price range greater than 10.
SELECT TradeDate, HighPrice, LowPrice, HighPrice - LowPrice AS Range
FROM JIOFIN_STOCK
WHERE HighPrice - LowPrice > 10
ORDER BY Range DESC;
Q158 Days with price range less than 2.
SELECT TradeDate, HighPrice, LowPrice, HighPrice - LowPrice AS Range
FROM JIOFIN_STOCK
WHERE HighPrice - LowPrice < 2
ORDER BY Range ASC;
Q159 Days where stock opened gap up (>2%).
SELECT TradeDate, PrevClose, OpenPrice, (OpenPrice - PrevClose) * 100.0 / PrevClose AS GapPercent
FROM JIOFIN_STOCK
WHERE (OpenPrice - PrevClose) * 100.0 / PrevClose > 2
ORDER BY GapPercent DESC;
Q160 Days where stock opened gap down (<-2 strong="">
SELECT TradeDate, PrevClose, OpenPrice, (OpenPrice - PrevClose) * 100.0 / PrevClose AS GapPercent
FROM JIOFIN_STOCK
WHERE (OpenPrice - PrevClose) * 100.0 / PrevClose < -2
ORDER BY GapPercent ASC;SECTION 10: SELF JOINS Q161-Q175
Q161 Compare each day's close with previous day.
SELECT S1.TradeDate, S1.ClosePrice, S2.ClosePrice AS PrevClosePrice
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY);
Q162 Compare each day's volume with previous day.
SELECT S1.TradeDate, S1.Volume, S2.Volume AS PrevVolume,
ROUND((S1.Volume - S2.Volume) * 100.0 / S2.Volume, 2) AS VolumeChangePercent
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY);
Q163 Find days with 5-day high.
SELECT S1.TradeDate, S1.HighPrice
FROM JIOFIN_STOCK S1
WHERE S1.HighPrice > ALL (
SELECT S2.HighPrice FROM JIOFIN_STOCK S2
WHERE S2.TradeDate < S1.TradeDate
AND S2.TradeDate >= DATE_SUB(S1.TradeDate, INTERVAL 5 DAY)
);
Q164 Find days with 5-day low.
SELECT S1.TradeDate, S1.LowPrice
FROM JIOFIN_STOCK S1
WHERE S1.LowPrice < ALL (
SELECT S2.LowPrice FROM JIOFIN_STOCK S2
WHERE S2.TradeDate < S1.TradeDate
AND S2.TradeDate >= DATE_SUB(S1.TradeDate, INTERVAL 5 DAY)
);
Q165 Calculate 5-day moving average.
SELECT S1.TradeDate, S1.ClosePrice,
AVG(S2.ClosePrice) AS MA5
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 4 DAY) AND S1.TradeDate
GROUP BY S1.TradeDate, S1.ClosePrice;
Q166 Calculate 10-day moving average.
SELECT S1.TradeDate, S1.ClosePrice,
AVG(S2.ClosePrice) AS MA10
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 9 DAY) AND S1.TradeDate
GROUP BY S1.TradeDate, S1.ClosePrice;
Q167 Find golden cross (MA5 > MA10).
SELECT S1.TradeDate,
AVG(S2.ClosePrice) AS MA5,
AVG(S3.ClosePrice) AS MA10
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 4 DAY) AND S1.TradeDate
JOIN JIOFIN_STOCK S3 ON S3.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 9 DAY) AND S1.TradeDate
GROUP BY S1.TradeDate
HAVING AVG(S2.ClosePrice) > AVG(S3.ClosePrice)
AND AVG(S2.ClosePrice) < AVG(S3.ClosePrice) + 2;
Q168 Find death cross (MA5 < MA10).
SELECT S1.TradeDate,
AVG(S2.ClosePrice) AS MA5,
AVG(S3.ClosePrice) AS MA10
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 4 DAY) AND S1.TradeDate
JOIN JIOFIN_STOCK S3 ON S3.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 9 DAY) AND S1.TradeDate
GROUP BY S1.TradeDate
HAVING AVG(S2.ClosePrice) < AVG(S3.ClosePrice)
AND AVG(S2.ClosePrice) > AVG(S3.ClosePrice) - 2;
Q169 Calculate daily volatility (High-Low)/Close.
SELECT S1.TradeDate, S1.ClosePrice,
ROUND((S1.HighPrice - S1.LowPrice) * 100.0 / S1.ClosePrice, 2) AS VolatilityPercent
FROM JIOFIN_STOCK S1
ORDER BY VolatilityPercent DESC;
Q170 Find days with highest volatility.
SELECT TradeDate, ClosePrice,
ROUND((HighPrice - LowPrice) * 100.0 / ClosePrice, 2) AS VolatilityPercent
FROM JIOFIN_STOCK
ORDER BY VolatilityPercent DESC LIMIT 10;
Q171 Compare week-over-week performance.
SELECT S1.TradeDate, S1.ClosePrice,
(SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 7 DAY)) AS WeekAgoPrice,
ROUND(((S1.ClosePrice - (SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 7 DAY))) * 100.0 /
(SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 7 DAY))), 2) AS WeekOverWeekPct
FROM JIOFIN_STOCK S1
WHERE (SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 7 DAY)) IS NOT NULL;
Q172 Compare month-over-month performance.
SELECT S1.TradeDate, S1.ClosePrice,
(SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 30 DAY)) AS MonthAgoPrice,
ROUND(((S1.ClosePrice - (SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 30 DAY))) * 100.0 /
(SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 30 DAY))), 2) AS MonthOverMonthPct
FROM JIOFIN_STOCK S1
WHERE (SELECT S2.ClosePrice FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 30 DAY)) IS NOT NULL;
Q173 Find days where volume > previous 5-day average.
SELECT S1.TradeDate, S1.Volume,
AVG(S2.Volume) AS Avg5DayVolume
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 5 DAY) AND DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)
GROUP BY S1.TradeDate, S1.Volume
HAVING S1.Volume > AVG(S2.Volume);
Q174 Find days where volume < previous 5-day average.
SELECT S1.TradeDate, S1.Volume,
AVG(S2.Volume) AS Avg5DayVolume
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate BETWEEN DATE_SUB(S1.TradeDate, INTERVAL 5 DAY) AND DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)
GROUP BY S1.TradeDate, S1.Volume
HAVING S1.Volume < AVG(S2.Volume);
Q175 Calculate cumulative returns.
SELECT S1.TradeDate, S1.ClosePrice,
ROUND((S1.ClosePrice - (SELECT MIN(ClosePrice) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate <= S1.TradeDate)) * 100.0 /
(SELECT MIN(ClosePrice) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate <= S1.TradeDate), 2) AS CumulativeReturnPct
FROM JIOFIN_STOCK S1
ORDER BY S1.TradeDate;SECTION 11: WINDOW FUNCTIONS Q176-Q195
Q176 Row number by date.
SELECT TradeDate, ClosePrice,
ROW_NUMBER() OVER (ORDER BY TradeDate) AS RowNum
FROM JIOFIN_STOCK;
Q177 Rank by ClosePrice.
SELECT TradeDate, ClosePrice,
RANK() OVER (ORDER BY ClosePrice DESC) AS PriceRank
FROM JIOFIN_STOCK;
Q178 Dense rank by Volume.
SELECT TradeDate, Volume,
DENSE_RANK() OVER (ORDER BY Volume DESC) AS VolumeRank
FROM JIOFIN_STOCK;
Q179 Running total of Volume.
SELECT TradeDate, Volume,
SUM(Volume) OVER (ORDER BY TradeDate) AS RunningTotalVolume
FROM JIOFIN_STOCK;
Q180 Running average of ClosePrice.
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningAvg
FROM JIOFIN_STOCK;
Q181 5-day moving average using window.
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MA5
FROM JIOFIN_STOCK;
Q182 10-day moving average using window.
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS MA10
FROM JIOFIN_STOCK;
Q183 20-day moving average using window.
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20
FROM JIOFIN_STOCK;
Q184 Rolling standard deviation.
SELECT TradeDate, ClosePrice,
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS RollingStdDev
FROM JIOFIN_STOCK;
Q185 Lag (previous day's close).
SELECT TradeDate, ClosePrice,
LAG(ClosePrice, 1) OVER (ORDER BY TradeDate) AS PrevClose
FROM JIOFIN_STOCK;
Q186 Lead (next day's close).
SELECT TradeDate, ClosePrice,
LEAD(ClosePrice, 1) OVER (ORDER BY TradeDate) AS NextClose
FROM JIOFIN_STOCK;
Q187 First value (first close price).
SELECT TradeDate, ClosePrice,
FIRST_VALUE(ClosePrice) OVER (ORDER BY TradeDate) AS FirstClose
FROM JIOFIN_STOCK;
Q188 Last value (last close price).
SELECT TradeDate, ClosePrice,
LAST_VALUE(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastClose
FROM JIOFIN_STOCK;
Q189 Percent rank of ClosePrice.
SELECT TradeDate, ClosePrice,
ROUND(PERCENT_RANK() OVER (ORDER BY ClosePrice) * 100, 2) AS PercentRank
FROM JIOFIN_STOCK;
Q190 Cumulative distribution of ClosePrice.
SELECT TradeDate, ClosePrice,
ROUND(CUME_DIST() OVER (ORDER BY ClosePrice) * 100, 2) AS CumulativePercent
FROM JIOFIN_STOCK;
Q191 NTILE (quartiles of ClosePrice).
SELECT TradeDate, ClosePrice,
NTILE(4) OVER (ORDER BY ClosePrice) AS Quartile
FROM JIOFIN_STOCK;
Q192 5-day high using window.
SELECT TradeDate, HighPrice,
MAX(HighPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS High5
FROM JIOFIN_STOCK;
Q193 5-day low using window.
SELECT TradeDate, LowPrice,
MIN(LowPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS Low5
FROM JIOFIN_STOCK;
Q194 Difference from 5-day high.
SELECT TradeDate, HighPrice,
MAX(HighPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS High5,
MAX(HighPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) - HighPrice AS DiffFromHigh
FROM JIOFIN_STOCK;
Q195 Price momentum (current close - close 5 days ago).
SELECT TradeDate, ClosePrice,
LAG(ClosePrice, 5) OVER (ORDER BY TradeDate) AS Close5Ago,
ClosePrice - LAG(ClosePrice, 5) OVER (ORDER BY TradeDate) AS Momentum5
FROM JIOFIN_STOCK;SECTION 12: ADVANCED ANALYTICS Q196-Q215
Q196 Calculate daily return percentage.
SELECT TradeDate, ClosePrice, PrevClose,
ROUND((ClosePrice - PrevClose) * 100.0 / PrevClose, 2) AS ReturnPct
FROM JIOFIN_STOCK;
Q197 Calculate cumulative return.
SELECT TradeDate, ClosePrice,
ROUND((ClosePrice / (SELECT FIRST_VALUE(ClosePrice) OVER (ORDER BY TradeDate) FROM JIOFIN_STOCK) - 1) * 100, 2) AS CumulativeReturn
FROM JIOFIN_STOCK;
Q198 Calculate average true range (ATR).
WITH TrueRange AS (
SELECT TradeDate,
GREATEST(HighPrice - LowPrice,
ABS(HighPrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate)),
ABS(LowPrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate))) AS TR
FROM JIOFIN_STOCK
)
SELECT TradeDate, TR,
AVG(TR) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS ATR14
FROM TrueRange;
Q199 Identify bullish engulfing pattern.
SELECT S1.TradeDate AS PatternDate, S1.OpenPrice, S1.ClosePrice,
S2.OpenPrice AS PrevOpen, S2.ClosePrice AS PrevClose
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)
WHERE S1.ClosePrice > S1.OpenPrice
AND S1.OpenPrice < S2.ClosePrice
AND S1.ClosePrice > S2.OpenPrice
AND S2.ClosePrice < S2.OpenPrice;
Q200 Identify bearish engulfing pattern.
SELECT S1.TradeDate AS PatternDate, S1.OpenPrice, S1.ClosePrice,
S2.OpenPrice AS PrevOpen, S2.ClosePrice AS PrevClose
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)
WHERE S1.ClosePrice < S1.OpenPrice
AND S1.OpenPrice > S2.ClosePrice
AND S1.ClosePrice < S2.OpenPrice
AND S2.ClosePrice > S2.OpenPrice;
Q201 Identify doji pattern.
SELECT TradeDate, OpenPrice, ClosePrice,
ABS(ClosePrice - OpenPrice) AS BodySize
FROM JIOFIN_STOCK
WHERE ABS(ClosePrice - OpenPrice) < (HighPrice - LowPrice) * 0.10
ORDER BY TradeDate;
Q202 Identify hammer pattern.
SELECT TradeDate, OpenPrice, HighPrice, LowPrice, ClosePrice
FROM JIOFIN_STOCK
WHERE (ClosePrice - OpenPrice) > 0
AND (LowPrice - OpenPrice) > 2 * (ClosePrice - OpenPrice)
AND (HighPrice - ClosePrice) < (ClosePrice - OpenPrice) * 0.5;
Q203 Identify shooting star pattern.
SELECT TradeDate, OpenPrice, HighPrice, LowPrice, ClosePrice
FROM JIOFIN_STOCK
WHERE (OpenPrice - ClosePrice) > 0
AND (HighPrice - OpenPrice) > 2 * (OpenPrice - ClosePrice)
AND (ClosePrice - LowPrice) < (OpenPrice - ClosePrice) * 0.5;
Q204 Calculate monthly returns.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
(MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice) AS MonthlyReturnPct
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
ORDER BY Month;
Q205 Calculate yearly returns.
SELECT YEAR(TradeDate) AS Year,
(MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice) AS YearlyReturnPct
FROM JIOFIN_STOCK
GROUP BY YEAR(TradeDate)
ORDER BY Year;
Q206 Identify oversold condition (RSI-like).
WITH PriceChanges AS (
SELECT TradeDate,
ClosePrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate) AS Change
FROM JIOFIN_STOCK
),
GainsLosses AS (
SELECT TradeDate,
CASE WHEN Change > 0 THEN Change ELSE 0 END AS Gain,
CASE WHEN Change < 0 THEN -Change ELSE 0 END AS Loss
FROM PriceChanges
)
SELECT TradeDate,
ROUND(AVG(Gain) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) * 100.0 /
(AVG(Gain) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) +
AVG(Loss) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)), 2) AS RSI14
FROM GainsLosses;
Q207 Find days with highest volume spike.
SELECT TradeDate, Volume,
ROUND(Volume * 100.0 / AVG(Volume) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND 1 FOLLOWING), 2) AS VolumeSpikePct
FROM JIOFIN_STOCK
ORDER BY VolumeSpikePct DESC LIMIT 10;
Q208 Calculate support and resistance levels.
SELECT TradeDate, ClosePrice,
MIN(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS Support20,
MAX(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS Resistance20
FROM JIOFIN_STOCK;
Q209 Identify breakout above resistance.
SELECT TradeDate, ClosePrice,
MAX(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND 1 PRECEDING) AS PreviousResistance
FROM JIOFIN_STOCK
WHERE ClosePrice > MAX(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND 1 PRECEDING);
Q210 Identify breakdown below support.
SELECT TradeDate, ClosePrice,
MIN(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND 1 PRECEDING) AS PreviousSupport
FROM JIOFIN_STOCK
WHERE ClosePrice < MIN(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND 1 PRECEDING);
Q211 Calculate Sharpe ratio (assuming risk-free rate = 0).
WITH Returns AS (
SELECT TradeDate, ROUND((ClosePrice - PrevClose) * 100.0 / PrevClose, 2) AS DailyReturn
FROM JIOFIN_STOCK
),
ReturnStats AS (
SELECT AVG(DailyReturn) AS AvgReturn, STD(DailyReturn) AS StdReturn
FROM Returns
)
SELECT ROUND(AvgReturn * SQRT(252) / StdReturn, 2) AS SharpeRatio
FROM ReturnStats;
Q212 Calculate maximum drawdown.
WITH CumulativeReturns AS (
SELECT TradeDate, ClosePrice,
ROUND((ClosePrice - FIRST_VALUE(ClosePrice) OVER (ORDER BY TradeDate)) * 100.0 / FIRST_VALUE(ClosePrice) OVER (ORDER BY TradeDate), 2) AS CumReturn
FROM JIOFIN_STOCK
),
RunningMax AS (
SELECT TradeDate, CumReturn,
MAX(CumReturn) OVER (ORDER BY TradeDate) AS MaxCumReturn
FROM CumulativeReturns
)
SELECT TradeDate, CumReturn, MaxCumReturn,
ROUND((CumReturn - MaxCumReturn), 2) AS Drawdown
FROM RunningMax
ORDER BY Drawdown ASC LIMIT 1;
Q213 Calculate rolling volatility (20-day).
SELECT TradeDate, ClosePrice,
ROUND(STD(ROUND((ClosePrice - PrevClose) * 100.0 / PrevClose, 2)) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW), 2) AS Volatility20
FROM JIOFIN_STOCK;
Q214 Identify days with volume > 2x average volume.
SELECT TradeDate, Volume,
AVG(Volume) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND 1 PRECEDING) AS AvgVolume20
FROM JIOFIN_STOCK
WHERE Volume > 2 * AVG(Volume) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND 1 PRECEDING);
Q215 Create a complete trading summary.
SELECT
COUNT(*) AS TotalDays,
SUM(CASE WHEN ClosePrice > PrevClose THEN 1 ELSE 0 END) AS UpDays,
SUM(CASE WHEN ClosePrice < PrevClose THEN 1 ELSE 0 END) AS DownDays,
ROUND(SUM(CASE WHEN ClosePrice > PrevClose THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS WinRate,
ROUND(AVG(ClosePrice), 2) AS AvgPrice,
ROUND(MAX(ClosePrice), 2) AS MaxPrice,
ROUND(MIN(ClosePrice), 2) AS MinPrice,
SUM(Volume) AS TotalVolume,
ROUND(AVG(Volume), 0) AS AvgVolume,
ROUND(SUM(Value), 2) AS TotalValue,
ROUND(AVG(Value), 2) AS AvgValue
FROM JIOFIN_STOCK;SECTION 13: PERFORMANCE & OPTIMIZATION Q216-Q230
Q216 Use EXPLAIN to analyze a query.
EXPLAIN SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > 250 AND TradeDate > '2026-01-01';
Q217 Create index on (TradeDate, ClosePrice).
CREATE INDEX idx_date_close ON JIOFIN_STOCK(TradeDate, ClosePrice);
Q218 Optimize query with covering index.
-- Create covering index
CREATE INDEX idx_covering ON JIOFIN_STOCK(TradeDate, ClosePrice, Volume, Value);
-- Query that can use covering index
SELECT TradeDate, ClosePrice, Volume, Value FROM JIOFIN_STOCK WHERE TradeDate > '2026-01-01';
Q219 Use query hint to force index.
SELECT * FROM JIOFIN_STOCK USE INDEX (idx_close_price) WHERE ClosePrice BETWEEN 200 AND 300;
Q220 Optimize COUNT(*) query.
-- Slow: SELECT COUNT(*) FROM JIOFIN_STOCK WHERE ClosePrice > 250;
-- Optimized with index
CREATE INDEX idx_close ON JIOFIN_STOCK(ClosePrice);
SELECT COUNT(*) FROM JIOFIN_STOCK WHERE ClosePrice > 250;
Q221 Use batch processing for updates.
DELIMITER //
CREATE PROCEDURE BatchUpdatePriceChange(IN batch_size INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
REPEAT
UPDATE JIOFIN_STOCK
SET PriceChange = ClosePrice - PrevClose
WHERE PriceChange IS NULL
LIMIT batch_size;
UNTIL ROW_COUNT() = 0 END REPEAT;
END //
DELIMITER ;
CALL BatchUpdatePriceChange(1000);
Q222 Use partition for date range queries.
CREATE TABLE JIOFIN_STOCK_PARTITIONED (
TradeDate DATE,
Series VARCHAR(3),
ClosePrice DECIMAL(10,2),
Volume BIGINT
) PARTITION BY RANGE (YEAR(TradeDate)) (
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027)
);
Q223 Create materialized view for daily summary.
CREATE TABLE DailySummary AS
SELECT TradeDate,
AVG(ClosePrice) AS AvgClose,
SUM(Volume) AS TotalVolume,
COUNT(*) AS RecordCount
FROM JIOFIN_STOCK
GROUP BY TradeDate;
CREATE INDEX idx_summary_date ON DailySummary(TradeDate);
Q224 Use derived table for complex aggregations.
SELECT D.TradeDate, D.ClosePrice, Stats.AvgClose
FROM JIOFIN_STOCK D
JOIN (
SELECT AVG(ClosePrice) AS AvgClose FROM JIOFIN_STOCK
) Stats ON 1=1
WHERE D.ClosePrice > Stats.AvgClose;
Q225 Optimize OR condition with UNION.
-- Slow: SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > 300 OR Volume > 10000000;
-- Optimized:
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > 300
UNION
SELECT * FROM JIOFIN_STOCK WHERE Volume > 10000000;
Q226 Use EXISTS for existence checks.
-- Slow with COUNT
SELECT TradeDate FROM JIOFIN_STOCK S1
WHERE (SELECT COUNT(*) FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = S1.TradeDate) > 0;
-- Optimized with EXISTS
SELECT TradeDate FROM JIOFIN_STOCK S1
WHERE EXISTS (SELECT 1 FROM JIOFIN_STOCK S2 WHERE S2.TradeDate = S1.TradeDate);
Q227 Use CASE for conditional aggregations.
-- Multiple queries:
-- SELECT SUM(Volume) FROM JIOFIN_STOCK WHERE ClosePrice > 250;
-- SELECT SUM(Volume) FROM JIOFIN_STOCK WHERE ClosePrice <= 250;
-- Single query with CASE:
SELECT
SUM(CASE WHEN ClosePrice > 250 THEN Volume ELSE 0 END) AS HighPriceVolume,
SUM(CASE WHEN ClosePrice <= 250 THEN Volume ELSE 0 END) AS LowPriceVolume
FROM JIOFIN_STOCK;
Q228 Use LIMIT for pagination.
-- Pagination query
SELECT * FROM JIOFIN_STOCK
ORDER BY TradeDate DESC
LIMIT 100 OFFSET 1000;
-- Better with seek method
SELECT * FROM JIOFIN_STOCK
WHERE TradeDate < '2026-07-20'
ORDER BY TradeDate DESC
LIMIT 100;
Q229 Analyze table for optimizer.
ANALYZE TABLE JIOFIN_STOCK;
Q230 Use SHOW PROFILE for query analysis.
SET profiling = 1;
SELECT * FROM JIOFIN_STOCK WHERE ClosePrice > 250 AND TradeDate > '2026-01-01';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;SECTION 14: COMPLEX REPORTS Q231-Q250
Q231 Executive summary dashboard.
SELECT
(SELECT COUNT(*) FROM JIOFIN_STOCK) AS TradingDays,
(SELECT ROUND(AVG(ClosePrice), 2) FROM JIOFIN_STOCK) AS AvgPrice,
(SELECT MAX(ClosePrice) FROM JIOFIN_STOCK) AS MaxPrice,
(SELECT MIN(ClosePrice) FROM JIOFIN_STOCK) AS MinPrice,
(SELECT SUM(Volume) FROM JIOFIN_STOCK) AS TotalVolume,
(SELECT ROUND(AVG(Volume), 0) FROM JIOFIN_STOCK) AS AvgVolume,
(SELECT ROUND(AVG(PercentChange), 2) FROM JIOFIN_STOCK) AS AvgReturn,
(SELECT MAX(PercentChange) FROM JIOFIN_STOCK) AS MaxGain,
(SELECT MIN(PercentChange) FROM JIOFIN_STOCK) AS MaxLoss;
Q232 Monthly performance report.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
COUNT(*) AS TradingDays,
ROUND(AVG(ClosePrice), 2) AS AvgClose,
ROUND(MAX(ClosePrice), 2) AS High,
ROUND(MIN(ClosePrice), 2) AS Low,
ROUND((MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice), 2) AS MonthlyReturn,
SUM(Volume) AS TotalVolume,
ROUND(SUM(Value), 2) AS TotalValue
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
ORDER BY Month DESC;
Q233 Weekly performance report.
SELECT YEARWEEK(TradeDate) AS Week,
MIN(TradeDate) AS WeekStart,
MAX(TradeDate) AS WeekEnd,
COUNT(*) AS TradingDays,
ROUND(AVG(ClosePrice), 2) AS AvgClose,
ROUND((MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice), 2) AS WeeklyReturn,
SUM(Volume) AS TotalVolume
FROM JIOFIN_STOCK
GROUP BY YEARWEEK(TradeDate)
ORDER BY Week DESC;
Q234 Yearly summary report.
SELECT YEAR(TradeDate) AS Year,
COUNT(*) AS TradingDays,
ROUND(AVG(ClosePrice), 2) AS AvgClose,
ROUND(MAX(ClosePrice), 2) AS High,
ROUND(MIN(ClosePrice), 2) AS Low,
ROUND((MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice), 2) AS YearlyReturn,
SUM(Volume) AS TotalVolume,
ROUND(SUM(Value), 2) AS TotalValue,
SUM(NumberOfTrades) AS TotalTrades
FROM JIOFIN_STOCK
GROUP BY YEAR(TradeDate)
ORDER BY Year DESC;
Q235 Volatility report by month.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
ROUND(AVG(HighPrice - LowPrice), 2) AS AvgDailyRange,
ROUND(STD(ClosePrice), 2) AS PriceStdDev,
ROUND(STD(PercentChange), 2) AS ReturnStdDev,
ROUND(MAX(HighPrice - LowPrice), 2) AS MaxRange,
ROUND(MIN(HighPrice - LowPrice), 2) AS MinRange
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
ORDER BY Month DESC;
Q236 Volume analysis report.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
SUM(Volume) AS TotalVolume,
ROUND(AVG(Volume), 0) AS AvgDailyVolume,
MAX(Volume) AS MaxVolume,
MIN(Volume) AS MinVolume,
ROUND(SUM(Volume) * 100.0 / (SELECT SUM(Volume) FROM JIOFIN_STOCK), 2) AS VolumeShare
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
ORDER BY Month DESC;
Q237 Best and worst performing months.
WITH MonthlyReturns AS (
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
ROUND((MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice), 2) AS MonthlyReturn
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
)
SELECT 'Best Month' AS Category, Month, MonthlyReturn
FROM MonthlyReturns
ORDER BY MonthlyReturn DESC LIMIT 1
UNION
SELECT 'Worst Month' AS Category, Month, MonthlyReturn
FROM MonthlyReturns
ORDER BY MonthlyReturn ASC LIMIT 1;
Q238 Day of week analysis.
SELECT DayOfWeek,
COUNT(*) AS TradingDays,
ROUND(AVG(ClosePrice), 2) AS AvgClose,
ROUND(AVG(PriceChange), 2) AS AvgPriceChange,
ROUND(AVG(PercentChange), 2) AS AvgPctChange,
ROUND(AVG(Volume), 0) AS AvgVolume,
SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) AS UpDays,
SUM(CASE WHEN PriceChange < 0 THEN 1 ELSE 0 END) AS DownDays,
ROUND(SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS WinRate
FROM JIOFIN_STOCK
GROUP BY DayOfWeek
ORDER BY FIELD(DayOfWeek, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday');
Q239 Price range analysis.
SELECT
CASE
WHEN ClosePrice < 250 THEN 'Below 250'
WHEN ClosePrice BETWEEN 250 AND 300 THEN '250-300'
WHEN ClosePrice BETWEEN 300 AND 350 THEN '300-350'
ELSE 'Above 350'
END AS PriceRange,
COUNT(*) AS Days,
ROUND(AVG(Volume), 0) AS AvgVolume,
ROUND(AVG(PercentChange), 2) AS AvgReturn,
SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) AS UpDays,
ROUND(SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS WinRate
FROM JIOFIN_STOCK
GROUP BY PriceRange
ORDER BY PriceRange;
Q240 Volume range analysis.
SELECT
CASE
WHEN Volume < 5000000 THEN 'Low (<5m 10000000="" 50000000="" 5000000="" and="" between="" edium="" else="" ery="" high="" igh="" then="" volume="" when="">50M)'
END AS VolumeRange,
COUNT(*) AS Days,
ROUND(AVG(ClosePrice), 2) AS AvgClose,
ROUND(AVG(PercentChange), 2) AS AvgReturn,
SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) AS UpDays,
ROUND(SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS WinRate
FROM JIOFIN_STOCK
GROUP BY VolumeRange
ORDER BY FIELD(VolumeRange, 'Low (<5m edium="" ery="" high="" igh="">50M)');
Q241 Consecutive days analysis.
WITH ConsecutiveDays AS (
SELECT TradeDate, ClosePrice, PriceChange,
CASE
WHEN PriceChange > 0 THEN 1
ELSE 0
END AS IsUp,
ROW_NUMBER() OVER (ORDER BY TradeDate) -
ROW_NUMBER() OVER (PARTITION BY CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END ORDER BY TradeDate) AS Grp
FROM JIOFIN_STOCK
)
SELECT
CASE WHEN IsUp = 1 THEN 'Up' ELSE 'Down' END AS Direction,
COUNT(*) AS StreakLength,
COUNT(*) AS Occurrences
FROM ConsecutiveDays
GROUP BY IsUp, Grp
ORDER BY StreakLength DESC LIMIT 10;
Q242 Correlation analysis.
SELECT
ROUND(CORR(Volume, NumberOfTrades), 4) AS Volume_Trades_Corr,
ROUND(CORR(ClosePrice, Volume), 4) AS Price_Volume_Corr,
ROUND(CORR(ClosePrice, NumberOfTrades), 4) AS Price_Trades_Corr,
ROUND(CORR(PriceChange, Volume), 4) AS Return_Volume_Corr
FROM JIOFIN_STOCK;
Q243 Gap analysis.
SELECT TradeDate, PrevClose, OpenPrice,
ROUND((OpenPrice - PrevClose) * 100.0 / PrevClose, 2) AS GapPercent,
CASE
WHEN OpenPrice > PrevClose THEN 'Gap Up'
WHEN OpenPrice < PrevClose THEN 'Gap Down'
ELSE 'No Gap'
END AS GapType
FROM JIOFIN_STOCK
WHERE ABS((OpenPrice - PrevClose) * 100.0 / PrevClose) > 0.5
ORDER BY ABS(GapPercent) DESC;
Q244 Moving average crossover analysis.
WITH MovingAverages AS (
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MA5,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20
FROM JIOFIN_STOCK
),
Crossovers AS (
SELECT TradeDate, MA5, MA20,
LAG(MA5, 1) OVER (ORDER BY TradeDate) AS PrevMA5,
LAG(MA20, 1) OVER (ORDER BY TradeDate) AS PrevMA20
FROM MovingAverages
)
SELECT TradeDate, MA5, MA20,
CASE
WHEN MA5 > MA20 AND PrevMA5 <= PrevMA20 THEN 'Golden Cross'
WHEN MA5 < MA20 AND PrevMA5 >= PrevMA20 THEN 'Death Cross'
ELSE 'No Crossover'
END AS CrossoverType
FROM Crossovers
WHERE CrossoverType != 'No Crossover';
Q245 52-week high/low analysis.
SELECT TradeDate, ClosePrice, High52Week, Low52Week,
ROUND((ClosePrice - Low52Week) * 100.0 / (High52Week - Low52Week), 2) AS PercentFromLow,
ROUND((High52Week - ClosePrice) * 100.0 / (High52Week - Low52Week), 2) AS PercentFromHigh,
CASE
WHEN ClosePrice > High52Week * 0.95 THEN 'Near 52W High'
WHEN ClosePrice < Low52Week * 1.05 THEN 'Near 52W Low'
ELSE 'Middle Range'
END AS Position
FROM JIOFIN_STOCK
ORDER BY TradeDate DESC;
Q246 Daily return distribution.
SELECT
CASE
WHEN PercentChange >= 5 THEN '>5%'
WHEN PercentChange >= 2 THEN '2-5%'
WHEN PercentChange >= 0 THEN '0-2%'
WHEN PercentChange >= -2 THEN '-2-0%'
WHEN PercentChange >= -5 THEN '-5 to -2%'
ELSE '<-5 100.0="" 2="" as="" by="" count="" days="" end="" eturnrange="" field="" from="" group="" jiofin_stock="" order="" percentage="" returnrange="" round="">5%', '2-5%', '0-2%', '-2-0%', '-5 to -2%', '<-5 code="">
Q247 Quarter performance.
SELECT
CONCAT(YEAR(TradeDate), '-Q', QUARTER(TradeDate)) AS Quarter,
COUNT(*) AS TradingDays,
ROUND(AVG(ClosePrice), 2) AS AvgClose,
ROUND(MAX(ClosePrice), 2) AS High,
ROUND(MIN(ClosePrice), 2) AS Low,
ROUND((MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice), 2) AS QuarterlyReturn,
SUM(Volume) AS TotalVolume
FROM JIOFIN_STOCK
GROUP BY YEAR(TradeDate), QUARTER(TradeDate)
ORDER BY Quarter DESC;
Q248 Trading day analysis by month.
SELECT DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
COUNT(*) AS TotalDays,
SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) AS UpDays,
SUM(CASE WHEN PriceChange < 0 THEN 1 ELSE 0 END) AS DownDays,
SUM(CASE WHEN PriceChange = 0 THEN 1 ELSE 0 END) AS FlatDays,
ROUND(SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS WinRate,
ROUND(AVG(PercentChange), 2) AS AvgReturn
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
ORDER BY Month DESC;
Q249 Volume and price relationship.
SELECT
CASE
WHEN Volume > (SELECT AVG(Volume) FROM JIOFIN_STOCK)
AND PriceChange > 0 THEN 'High Volume Up'
WHEN Volume > (SELECT AVG(Volume) FROM JIOFIN_STOCK)
AND PriceChange < 0 THEN 'High Volume Down'
WHEN Volume <= (SELECT AVG(Volume) FROM JIOFIN_STOCK)
AND PriceChange > 0 THEN 'Low Volume Up'
ELSE 'Low Volume Down'
END AS Scenario,
COUNT(*) AS Days,
ROUND(AVG(PercentChange), 2) AS AvgReturn,
ROUND(AVG(Volume), 0) AS AvgVolume
FROM JIOFIN_STOCK
GROUP BY Scenario
ORDER BY AvgReturn DESC;
Q250 Complete statistical summary.
SELECT
'ClosePrice' AS Metric,
ROUND(AVG(ClosePrice), 2) AS Mean,
ROUND(STD(ClosePrice), 2) AS StdDev,
ROUND(MIN(ClosePrice), 2) AS Min,
ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY ClosePrice), 2) AS Q1,
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ClosePrice), 2) AS Median,
ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY ClosePrice), 2) AS Q3,
ROUND(MAX(ClosePrice), 2) AS Max
FROM JIOFIN_STOCK
UNION ALL
SELECT
'Volume',
ROUND(AVG(Volume), 0),
ROUND(STD(Volume), 0),
MIN(Volume),
ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Volume), 0),
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Volume), 0),
ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Volume), 0),
MAX(Volume)
FROM JIOFIN_STOCK
UNION ALL
SELECT
'PercentChange',
ROUND(AVG(PercentChange), 2),
ROUND(STD(PercentChange), 2),
MIN(PercentChange),
ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY PercentChange), 2),
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY PercentChange), 2),
ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY PercentChange), 2),
MAX(PercentChange)
FROM JIOFIN_STOCK;SECTION 15: ADVANCED CALCULATIONS Q251-Q270
Q251 Calculate exponential moving average (EMA).
SELECT TradeDate, ClosePrice,
ROUND(AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 2) AS SMA10,
ROUND(ClosePrice * 0.1818 +
LAG(ClosePrice, 1) OVER (ORDER BY TradeDate) * 0.1818 * 0.1818 +
LAG(ClosePrice, 2) OVER (ORDER BY TradeDate) * 0.1818 * 0.1818 * 0.1818, 2) AS EMA10
FROM JIOFIN_STOCK;
Q252 Calculate Bollinger Bands.
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20,
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS StdDev20,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) + 2 *
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS UpperBand,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) - 2 *
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS LowerBand
FROM JIOFIN_STOCK;
Q253 Identify overbought/oversold using Bollinger Bands.
WITH Bollinger AS (
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20,
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS StdDev20
FROM JIOFIN_STOCK
)
SELECT TradeDate, ClosePrice, MA20,
CASE
WHEN ClosePrice > MA20 + 2 * StdDev20 THEN 'Overbought'
WHEN ClosePrice < MA20 - 2 * StdDev20 THEN 'Oversold'
ELSE 'Neutral'
END AS Signal
FROM Bollinger
WHERE StdDev20 IS NOT NULL;
Q254 Calculate MACD (Moving Average Convergence Divergence).
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) -
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS MACD
FROM JIOFIN_STOCK;
Q255 Calculate daily value traded per volume.
SELECT TradeDate, Volume, Value,
ROUND(Value / Volume, 2) AS AvgTradePrice
FROM JIOFIN_STOCK
WHERE Volume > 0
ORDER BY AvgTradePrice DESC;
Q256 Calculate price to 52-week high ratio.
SELECT TradeDate, ClosePrice, High52Week,
ROUND(ClosePrice * 100.0 / High52Week, 2) AS PercentOfHigh
FROM JIOFIN_STOCK
ORDER BY PercentOfHigh DESC;
Q257 Calculate price to 52-week low ratio.
SELECT TradeDate, ClosePrice, Low52Week,
ROUND(ClosePrice * 100.0 / Low52Week, 2) AS PercentOfLow
FROM JIOFIN_STOCK
ORDER BY PercentOfLow ASC;
Q258 Calculate turnover ratio.
SELECT TradeDate, Volume, Value,
ROUND(Value * 100.0 / (SELECT MAX(Value) FROM JIOFIN_STOCK), 2) AS TurnoverRatio
FROM JIOFIN_STOCK
ORDER BY TurnoverRatio DESC;
Q259 Calculate average trade size.
SELECT TradeDate, Volume, NumberOfTrades,
ROUND(Volume / NULLIF(NumberOfTrades, 0), 2) AS AvgTradeSize
FROM JIOFIN_STOCK
WHERE NumberOfTrades > 0
ORDER BY AvgTradeSize DESC;
Q260 Calculate price momentum (ROC - Rate of Change).
SELECT TradeDate, ClosePrice,
ROUND((ClosePrice - LAG(ClosePrice, 10) OVER (ORDER BY TradeDate)) * 100.0 /
LAG(ClosePrice, 10) OVER (ORDER BY TradeDate), 2) AS ROC10
FROM JIOFIN_STOCK;
Q261 Calculate Average Directional Index (ADX) approximation.
WITH TR AS (
SELECT TradeDate,
GREATEST(HighPrice - LowPrice,
ABS(HighPrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate)),
ABS(LowPrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate))) AS TrueRange
FROM JIOFIN_STOCK
)
SELECT TradeDate,
ROUND(AVG(TrueRange) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW), 2) AS ADX14
FROM TR;
Q262 Calculate volume weighted average price (VWAP).
SELECT TradeDate, ClosePrice, VWAP,
ROUND(ClosePrice - VWAP, 2) AS VWAPDeviation,
ROUND((ClosePrice - VWAP) * 100.0 / VWAP, 2) AS VWAPDeviationPct
FROM JIOFIN_STOCK
WHERE VWAP IS NOT NULL
ORDER BY VWAPDeviationPct DESC;
Q263 Calculate daily range percentage.
SELECT TradeDate, HighPrice, LowPrice, ClosePrice,
ROUND((HighPrice - LowPrice) * 100.0 / ClosePrice, 2) AS RangePct,
ROUND((HighPrice - ClosePrice) * 100.0 / ClosePrice, 2) AS UpperWickPct,
ROUND((ClosePrice - LowPrice) * 100.0 / ClosePrice, 2) AS LowerWickPct
FROM JIOFIN_STOCK
ORDER BY RangePct DESC;
Q264 Identify inside day pattern.
SELECT S1.TradeDate, S1.HighPrice, S1.LowPrice,
S2.HighPrice AS PrevHigh, S2.LowPrice AS PrevLow
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)
WHERE S1.HighPrice < S2.HighPrice
AND S1.LowPrice > S2.LowPrice;
Q265 Identify outside day pattern.
SELECT S1.TradeDate, S1.HighPrice, S1.LowPrice,
S2.HighPrice AS PrevHigh, S2.LowPrice AS PrevLow
FROM JIOFIN_STOCK S1
JOIN JIOFIN_STOCK S2 ON S2.TradeDate = DATE_SUB(S1.TradeDate, INTERVAL 1 DAY)
WHERE S1.HighPrice > S2.HighPrice
AND S1.LowPrice < S2.LowPrice;
Q266 Calculate average true range (ATR) with smoothing.
WITH TrueRange AS (
SELECT TradeDate,
GREATEST(HighPrice - LowPrice,
ABS(HighPrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate)),
ABS(LowPrice - LAG(ClosePrice, 1) OVER (ORDER BY TradeDate))) AS TR
FROM JIOFIN_STOCK
)
SELECT TradeDate,
ROUND(TR, 2) AS TrueRange,
ROUND(AVG(TR) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW), 2) AS ATR14
FROM TrueRange
ORDER BY ATR14 DESC;
Q267 Calculate stochastic oscillator.
SELECT TradeDate, ClosePrice,
MIN(LowPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS LowestLow14,
MAX(HighPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS HighestHigh14,
ROUND((ClosePrice - MIN(LowPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)) * 100.0 /
(MAX(HighPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) -
MIN(LowPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)), 2) AS Stochastic14
FROM JIOFIN_STOCK;
Q268 Identify overbought/oversold using Stochastic.
WITH Stochastic AS (
SELECT TradeDate, ClosePrice,
ROUND((ClosePrice - MIN(LowPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)) * 100.0 /
(MAX(HighPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) -
MIN(LowPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)), 2) AS Stoch14
FROM JIOFIN_STOCK
)
SELECT TradeDate, ClosePrice, Stoch14,
CASE
WHEN Stoch14 > 80 THEN 'Overbought'
WHEN Stoch14 < 20 THEN 'Oversold'
ELSE 'Neutral'
END AS Signal
FROM Stochastic
WHERE Stoch14 IS NOT NULL;
Q269 Calculate cumulative volume.
SELECT TradeDate, Volume,
SUM(Volume) OVER (ORDER BY TradeDate) AS CumulativeVolume,
ROUND(SUM(Volume) OVER (ORDER BY TradeDate) * 100.0 / (SELECT SUM(Volume) FROM JIOFIN_STOCK), 2) AS CumulativeVolumePct
FROM JIOFIN_STOCK
ORDER BY TradeDate;
Q270 Calculate price-volume relationship index.
SELECT TradeDate, ClosePrice, Volume,
ROUND(ClosePrice * Volume / 1000000, 2) AS PriceVolumeIndex,
RANK() OVER (ORDER BY ClosePrice * Volume DESC) AS PVRank
FROM JIOFIN_STOCK
ORDER BY PriceVolumeIndex DESC;SECTION 16: FINAL CHALLENGE Q271-Q280
Q271 Complete stock performance dashboard.
SELECT
'Overall Performance' AS Metric,
ROUND((SELECT MAX(ClosePrice) - MIN(ClosePrice) FROM JIOFIN_STOCK), 2) AS Value,
CONCAT(ROUND((SELECT (MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice) FROM JIOFIN_STOCK), 2), '%') AS Percentage
UNION ALL
SELECT 'Max Daily Gain', ROUND(MAX(PercentChange), 2), CONCAT(MAX(PercentChange), '%')
FROM JIOFIN_STOCK
UNION ALL
SELECT 'Max Daily Loss', ROUND(MIN(PercentChange), 2), CONCAT(MIN(PercentChange), '%')
FROM JIOFIN_STOCK
UNION ALL
SELECT 'Current Price', ROUND(MAX(ClosePrice), 2), NULL
FROM JIOFIN_STOCK WHERE TradeDate = (SELECT MAX(TradeDate) FROM JIOFIN_STOCK)
UNION ALL
SELECT '52W High', ROUND(MAX(High52Week), 2), NULL
FROM JIOFIN_STOCK
UNION ALL
SELECT '52W Low', ROUND(MIN(Low52Week), 2), NULL
FROM JIOFIN_STOCK;
Q272 Complete trading system signals.
WITH MovingAverages AS (
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MA5,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS MA50
FROM JIOFIN_STOCK
),
Bollinger AS (
SELECT TradeDate, ClosePrice,
MA20,
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS StdDev20
FROM MovingAverages
)
SELECT M.TradeDate, M.ClosePrice,
CASE WHEN M.MA5 > M.MA20 AND M.MA20 > M.MA50 THEN 'Strong Buy'
WHEN M.MA5 < M.MA20 AND M.MA20 < M.MA50 THEN 'Strong Sell'
WHEN M.MA5 > M.MA20 AND M.MA20 < M.MA50 THEN 'Cautious Buy'
WHEN M.MA5 < M.MA20 AND M.MA20 > M.MA50 THEN 'Cautious Sell'
ELSE 'Neutral'
END AS TrendSignal,
CASE WHEN B.ClosePrice > B.MA20 + 2 * B.StdDev20 THEN 'Overbought'
WHEN B.ClosePrice < B.MA20 - 2 * B.StdDev20 THEN 'Oversold'
ELSE 'Neutral'
END AS VolatilitySignal
FROM MovingAverages M
JOIN Bollinger B ON M.TradeDate = B.TradeDate
WHERE M.MA5 IS NOT NULL AND M.MA20 IS NOT NULL AND M.MA50 IS NOT NULL
ORDER BY M.TradeDate DESC;
Q273 Complete risk management analysis.
SELECT
COUNT(*) AS TotalDays,
SUM(CASE WHEN PriceChange < 0 THEN 1 ELSE 0 END) AS LosingDays,
SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) AS WinningDays,
ROUND(AVG(CASE WHEN PriceChange < 0 THEN ABS(PercentChange) ELSE NULL END), 2) AS AvgLoss,
ROUND(AVG(CASE WHEN PriceChange > 0 THEN PercentChange ELSE NULL END), 2) AS AvgGain,
ROUND(AVG(CASE WHEN PriceChange < 0 THEN ABS(PercentChange) ELSE NULL END) /
NULLIF(AVG(CASE WHEN PriceChange > 0 THEN PercentChange ELSE NULL END), 0), 2) AS RiskRewardRatio,
ROUND(SUM(CASE WHEN PriceChange < 0 THEN -PriceChange ELSE 0 END) /
NULLIF(SUM(CASE WHEN PriceChange > 0 THEN PriceChange ELSE 0 END), 0), 2) AS ProfitFactor
FROM JIOFIN_STOCK;
Q274 Complete price prediction using linear regression.
WITH PriceData AS (
SELECT TradeDate, ClosePrice,
ROW_NUMBER() OVER (ORDER BY TradeDate) AS DayNum
FROM JIOFIN_STOCK
),
Regression AS (
SELECT
AVG(DayNum) AS XBar,
AVG(ClosePrice) AS YBar,
SUM((DayNum - AVG(DayNum) OVER ()) * (ClosePrice - AVG(ClosePrice) OVER ())) /
SUM((DayNum - AVG(DayNum) OVER ()) * (DayNum - AVG(DayNum) OVER ())) AS Slope,
AVG(ClosePrice) - (SUM((DayNum - AVG(DayNum) OVER ()) * (ClosePrice - AVG(ClosePrice) OVER ())) /
SUM((DayNum - AVG(DayNum) OVER ()) * (DayNum - AVG(DayNum) OVER ()))) * AVG(DayNum) AS Intercept
FROM PriceData
)
SELECT MAX(TradeDate) AS LastDate,
ROUND(MAX(Slope) * MAX(DayNum) + MAX(Intercept), 2) AS PredictedPrice
FROM PriceData, Regression;
Q275 Complete anomaly detection.
WITH PriceStats AS (
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 9 PRECEDING AND 1 PRECEDING) AS Avg10,
STD(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 9 PRECEDING AND 1 PRECEDING) AS Std10
FROM JIOFIN_STOCK
)
SELECT TradeDate, ClosePrice, Avg10, Std10,
CASE
WHEN ABS(ClosePrice - Avg10) > 3 * Std10 THEN 'Extreme Anomaly'
WHEN ABS(ClosePrice - Avg10) > 2 * Std10 THEN 'Potential Anomaly'
ELSE 'Normal'
END AS AnomalyStatus,
ROUND((ClosePrice - Avg10) * 100.0 / Avg10, 2) AS DeviationPct
FROM PriceStats
WHERE Std10 IS NOT NULL AND Std10 > 0
ORDER BY ABS(ClosePrice - Avg10) DESC;
Q276 Complete seasonality analysis.
SELECT
MONTH(TradeDate) AS Month,
AVG(ClosePrice) AS AvgPrice,
STD(ClosePrice) AS PriceStdDev,
AVG(PercentChange) AS AvgReturn,
STD(PercentChange) AS ReturnStdDev,
AVG(Volume) AS AvgVolume,
COUNT(*) AS TradingDays,
SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) AS UpDays,
ROUND(SUM(CASE WHEN PriceChange > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS WinRate
FROM JIOFIN_STOCK
GROUP BY MONTH(TradeDate)
ORDER BY Month;
Q277 Complete bull/bear market identification.
WITH MovingAverages AS (
SELECT TradeDate, ClosePrice,
AVG(ClosePrice) OVER (ORDER BY TradeDate ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS MA200
FROM JIOFIN_STOCK
),
MarketCondition AS (
SELECT TradeDate, ClosePrice, MA200,
CASE WHEN ClosePrice > MA200 THEN 'Bull Market'
WHEN ClosePrice < MA200 THEN 'Bear Market'
ELSE 'Transition'
END AS MarketPhase
FROM MovingAverages
WHERE MA200 IS NOT NULL
)
SELECT TradeDate, ClosePrice, MA200, MarketPhase,
RANK() OVER (PARTITION BY MarketPhase ORDER BY TradeDate) AS DaysInPhase
FROM MarketCondition
ORDER BY TradeDate;
Q278 Complete position sizing calculator.
SELECT TradeDate, ClosePrice,
ROUND(ClosePrice * 0.02, 2) AS RiskAmount,
ROUND((ClosePrice * 0.02) / (ClosePrice * 0.05), 0) AS PositionSize,
ROUND((ClosePrice * 0.02) / (ClosePrice * 0.05) * ClosePrice, 2) AS PositionValue,
ROUND((ClosePrice * 0.02) / (ClosePrice * 0.05) * ClosePrice * 0.05, 2) AS MaxLoss
FROM JIOFIN_STOCK
ORDER BY TradeDate DESC LIMIT 10;
Q279 Complete market breadth analysis.
SELECT
DATE_FORMAT(TradeDate, '%Y-%m') AS Month,
COUNT(*) AS TradingDays,
SUM(CASE WHEN ClosePrice > PrevClose THEN 1 ELSE 0 END) AS AdvancingDays,
SUM(CASE WHEN ClosePrice < PrevClose THEN 1 ELSE 0 END) AS DecliningDays,
ROUND(SUM(CASE WHEN ClosePrice > PrevClose THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS AdvanceDeclineRatio,
SUM(Volume) AS TotalVolume,
ROUND(AVG(Volume), 0) AS AvgVolume,
ROUND(AVG(ClosePrice), 2) AS AvgClose
FROM JIOFIN_STOCK
GROUP BY DATE_FORMAT(TradeDate, '%Y-%m')
ORDER BY Month DESC;
Q280 Complete final comprehensive analysis.
SELECT
'=== PERFORMANCE SUMMARY ===' AS Section,
CONCAT(ROUND((MAX(ClosePrice) - MIN(ClosePrice)) * 100.0 / MIN(ClosePrice), 2), '%') AS Value,
'Overall Return' AS Metric
FROM JIOFIN_STOCK
UNION ALL
SELECT '', CONCAT(ROUND(MAX(PercentChange), 2), '%'), 'Best Day'
FROM JIOFIN_STOCK
UNION ALL
SELECT '', CONCAT(ROUND(MIN(PercentChange), 2), '%'), 'Worst Day'
FROM JIOFIN_STOCK
UNION ALL
SELECT '=== VOLUME ANALYSIS ===', CONCAT(FORMAT(SUM(Volume), 0), ' shares'), 'Total Volume'
FROM JIOFIN_STOCK
UNION ALL
SELECT '', CONCAT(FORMAT(AVG(Volume), 0), ' shares'), 'Avg Daily Volume'
FROM JIOFIN_STOCK
UNION ALL
SELECT '=== PRICE ANALYSIS ===', CONCAT('₹', FORMAT(MAX(ClosePrice), 2)), 'Highest Close'
FROM JIOFIN_STOCK
UNION ALL
SELECT '', CONCAT('₹', FORMAT(MIN(ClosePrice), 2)), 'Lowest Close'
FROM JIOFIN_STOCK
UNION ALL
SELECT '', CONCAT('₹', FORMAT(AVG(ClosePrice), 2)), 'Average Close'
FROM JIOFIN_STOCK
UNION ALL
SELECT '=== TRADING STATISTICS ===', FORMAT(SUM(NumberOfTrades), 0), 'Total Trades'
FROM JIOFIN_STOCK
UNION ALL
SELECT '', FORMAT(AVG(NumberOfTrades), 0), 'Avg Daily Trades'
FROM JIOFIN_STOCK
UNION ALL
SELECT '=== RISK METRICS ===', CONCAT(ROUND(STD(PercentChange), 2), '%'), 'Daily Volatility'
FROM JIOFIN_STOCK
UNION ALL
SELECT '', CONCAT(ROUND(MIN(PercentChange), 2), '%'), 'Max Drawdown'
FROM JIOFIN_STOCK;
0 Comments