HDFC Bank — MySQL 100 Questions and Answers
Dataset: NSE daily quotes for HDFCBANK (EQ), 248 trading days. All answers use the cleaned table hdfc and are written for MySQL 8.0+.
Step 1 — Setup: create the tables and load the CSV
CREATE DATABASE IF NOT EXISTS stocks;
USE stocks;
-- Raw staging table (everything as text, exactly as in the CSV)
CREATE TABLE hdfc_raw (
trade_date VARCHAR(20),
series VARCHAR(5),
open_p VARCHAR(20),
high_p VARCHAR(20),
low_p VARCHAR(20),
prev_close VARCHAR(20),
ltp VARCHAR(20),
close_p VARCHAR(20),
vwap VARCHAR(20),
wk52_high VARCHAR(20),
wk52_low VARCHAR(20),
volume VARCHAR(30),
value_amt VARCHAR(40),
no_of_trades VARCHAR(30)
);
LOAD DATA LOCAL INFILE 'Quote-Equity-HDFCBANK-EQ-10-09-2025-10-09-2026 1.csv'
INTO TABLE hdfc_raw
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
-- Clean table used by every answer below
CREATE TABLE hdfc (
trade_date DATE PRIMARY KEY,
series CHAR(2),
open_p DECIMAL(10,2),
high_p DECIMAL(10,2),
low_p DECIMAL(10,2),
prev_close DECIMAL(10,2),
ltp DECIMAL(10,2),
close_p DECIMAL(10,2),
vwap DECIMAL(10,2),
wk52_high DECIMAL(10,2),
wk52_low DECIMAL(10,2),
volume BIGINT,
value_amt DECIMAL(20,2),
no_of_trades INT
);
INSERT INTO hdfc
SELECT STR_TO_DATE(trade_date,'%d-%b-%Y'), series,
REPLACE(open_p,',',''), REPLACE(high_p,',',''), REPLACE(low_p,',',''),
REPLACE(prev_close,',',''), REPLACE(ltp,',',''), REPLACE(close_p,',',''),
REPLACE(vwap,',',''), REPLACE(wk52_high,',',''), REPLACE(wk52_low,',',''),
REPLACE(volume,',',''), REPLACE(value_amt,',',''), REPLACE(no_of_trades,',','')
FROM hdfc_raw;
SELECT COUNT(*) FROM hdfc; -- expect 248Section A — Basics: SELECT, WHERE, ORDER BY, LIMIT Q1–20
Display all records from the table.
SELECT * FROM hdfc;Show only trade_date, open_p and close_p for all days.
SELECT trade_date, open_p, close_p
FROM hdfc
ORDER BY trade_date;Count the total number of trading days in the data.
SELECT COUNT(*) AS total_trading_days
FROM hdfc;Find the earliest and latest trade_date in the table.
SELECT MIN(trade_date) AS first_day, MAX(trade_date) AS last_day
FROM hdfc;List all days on which the closing price was above 1,000.
SELECT trade_date, close_p
FROM hdfc
WHERE close_p > 1000
ORDER BY trade_date;List all days on which the closing price was below 700.
SELECT trade_date, close_p
FROM hdfc
WHERE close_p < 700
ORDER BY trade_date;Show the 10 most recent trading days.
SELECT *
FROM hdfc
ORDER BY trade_date DESC
LIMIT 10;Show the 10 oldest trading days.
SELECT *
FROM hdfc
ORDER BY trade_date ASC
LIMIT 10;Find the day with the highest closing price.
SELECT trade_date, close_p
FROM hdfc
WHERE close_p = (SELECT MAX(close_p) FROM hdfc);Find the day with the lowest closing price.
SELECT trade_date, close_p
FROM hdfc
WHERE close_p = (SELECT MIN(close_p) FROM hdfc);Find the day with the highest intraday high (high_p).
SELECT trade_date, high_p
FROM hdfc
WHERE high_p = (SELECT MAX(high_p) FROM hdfc);Find the day with the lowest intraday low (low_p).
SELECT trade_date, low_p
FROM hdfc
WHERE low_p = (SELECT MIN(low_p) FROM hdfc);List the top 5 days by traded volume.
SELECT trade_date, volume
FROM hdfc
ORDER BY volume DESC
LIMIT 5;List the bottom 5 days by traded volume.
SELECT trade_date, volume
FROM hdfc
ORDER BY volume ASC
LIMIT 5;Show all trading days in the month of January 2026.
SELECT *
FROM hdfc
WHERE trade_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY trade_date;Show all trading days between 1 March 2026 and 31 March 2026.
SELECT *
FROM hdfc
WHERE trade_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY trade_date;List days where the stock opened higher than the previous close.
SELECT trade_date, prev_close, open_p
FROM hdfc
WHERE open_p > prev_close
ORDER BY trade_date;List days where the stock opened lower than the previous close.
SELECT trade_date, prev_close, open_p
FROM hdfc
WHERE open_p < prev_close
ORDER BY trade_date;List days where the closing price equals the opening price.
SELECT trade_date, open_p, close_p
FROM hdfc
WHERE close_p = open_p;Show distinct values of series in the table.
SELECT DISTINCT series
FROM hdfc;Section B — Aggregate Functions Q21–35
Find the average closing price over the whole period.
SELECT ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc;Find the maximum and minimum closing price in a single query.
SELECT MAX(close_p) AS max_close, MIN(close_p) AS min_close
FROM hdfc;Find the total volume traded over the whole period.
SELECT SUM(volume) AS total_volume
FROM hdfc;Find the average daily volume.
SELECT ROUND(AVG(volume), 0) AS avg_daily_volume
FROM hdfc;Find the total traded value (value_amt) over the whole period.
SELECT SUM(value_amt) AS total_value
FROM hdfc;Find the average number of trades per day.
SELECT ROUND(AVG(no_of_trades), 0) AS avg_trades_per_day
FROM hdfc;Find the highest number of trades in a single day and its date.
SELECT trade_date, no_of_trades
FROM hdfc
ORDER BY no_of_trades DESC
LIMIT 1;Find the average VWAP for the whole period.
SELECT ROUND(AVG(vwap), 2) AS avg_vwap
FROM hdfc;Count how many days the stock closed higher than it opened.
SELECT COUNT(*) AS green_days
FROM hdfc
WHERE close_p > open_p;Count how many days the stock closed lower than it opened.
SELECT COUNT(*) AS red_days
FROM hdfc
WHERE close_p < open_p;Count how many days the close was higher than the previous close.
SELECT COUNT(*) AS up_days
FROM hdfc
WHERE close_p > prev_close;Find the average daily range (high_p - low_p).
SELECT ROUND(AVG(high_p - low_p), 2) AS avg_daily_range
FROM hdfc;Find the largest single-day range and the date it occurred.
SELECT trade_date, (high_p - low_p) AS day_range
FROM hdfc
ORDER BY day_range DESC
LIMIT 1;Find the smallest single-day range and the date it occurred.
SELECT trade_date, (high_p - low_p) AS day_range
FROM hdfc
ORDER BY day_range ASC
LIMIT 1;Find the standard deviation of the closing price.
SELECT ROUND(STDDEV_SAMP(close_p), 2) AS std_close
FROM hdfc;Section C — Date Functions and GROUP BY Q36–55
Find the average closing price for each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY ym
ORDER BY ym;Find the total volume for each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, SUM(volume) AS total_volume
FROM hdfc
GROUP BY ym
ORDER BY ym;Find the month with the highest total volume.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, SUM(volume) AS total_volume
FROM hdfc
GROUP BY ym
ORDER BY total_volume DESC
LIMIT 1;Find the month with the highest average closing price.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY ym
ORDER BY avg_close DESC
LIMIT 1;Find the month with the lowest average closing price.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY ym
ORDER BY avg_close ASC
LIMIT 1;Find the number of trading days in each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, COUNT(*) AS trading_days
FROM hdfc
GROUP BY ym
ORDER BY ym;Find the average closing price for each quarter.
SELECT CONCAT(YEAR(trade_date), '-Q', QUARTER(trade_date)) AS qtr,
ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY YEAR(trade_date), QUARTER(trade_date)
ORDER BY YEAR(trade_date), QUARTER(trade_date);Find the average volume for each day of the week (Monday to Friday).
SELECT DAYNAME(trade_date) AS weekday, ROUND(AVG(volume), 0) AS avg_volume
FROM hdfc
GROUP BY WEEKDAY(trade_date), DAYNAME(trade_date)
ORDER BY WEEKDAY(trade_date);Find which weekday has the highest average closing price.
SELECT DAYNAME(trade_date) AS weekday, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY WEEKDAY(trade_date), DAYNAME(trade_date)
ORDER BY avg_close DESC
LIMIT 1;Find which weekday has the highest average volume.
SELECT DAYNAME(trade_date) AS weekday, ROUND(AVG(volume), 0) AS avg_volume
FROM hdfc
GROUP BY WEEKDAY(trade_date), DAYNAME(trade_date)
ORDER BY avg_volume DESC
LIMIT 1;Count how many trading days fall on each weekday.
SELECT DAYNAME(trade_date) AS weekday, COUNT(*) AS days
FROM hdfc
GROUP BY WEEKDAY(trade_date), DAYNAME(trade_date)
ORDER BY WEEKDAY(trade_date);For each month, find the highest high_p and lowest low_p.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, MAX(high_p) AS month_high, MIN(low_p) AS month_low
FROM hdfc
GROUP BY ym
ORDER BY ym;For each month, find the average daily range.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(high_p - low_p), 2) AS avg_range
FROM hdfc
GROUP BY ym
ORDER BY ym;For each year-month, count the number of days the stock closed higher than it opened.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, SUM(close_p > open_p) AS green_days
FROM hdfc
GROUP BY ym
ORDER BY ym;Find the average number of trades for each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(no_of_trades), 0) AS avg_trades
FROM hdfc
GROUP BY ym
ORDER BY ym;For each month, find the total traded value in crores (divide by 10,000,000).
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(SUM(value_amt) / 10000000, 2) AS value_crore
FROM hdfc
GROUP BY ym
ORDER BY ym;List months that have more than 20 trading days.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, COUNT(*) AS trading_days
FROM hdfc
GROUP BY ym
HAVING COUNT(*) > 20
ORDER BY ym;List months where the average volume exceeded the overall average volume (use HAVING with a subquery).
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(volume), 0) AS avg_volume
FROM hdfc
GROUP BY ym
HAVING AVG(volume) > (SELECT AVG(volume) FROM hdfc)
ORDER BY ym;Find the week number with the highest total volume.
SELECT YEARWEEK(trade_date, 1) AS year_week, SUM(volume) AS total_volume
FROM hdfc
GROUP BY year_week
ORDER BY total_volume DESC
LIMIT 1;For each quarter, find the maximum closing price and the date it occurred.
WITH q AS (
SELECT trade_date, close_p,
CONCAT(YEAR(trade_date), '-Q', QUARTER(trade_date)) AS qtr,
ROW_NUMBER() OVER (
PARTITION BY YEAR(trade_date), QUARTER(trade_date)
ORDER BY close_p DESC
) AS rn
FROM hdfc
)
SELECT qtr, trade_date, close_p
FROM q
WHERE rn = 1
ORDER BY qtr;Section D — Calculated Columns and CASE Q56–70
Show daily percentage change: (close_p - prev_close) / prev_close * 100.
SELECT trade_date, prev_close, close_p,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc
ORDER BY trade_date;Find the top 10 days with the biggest percentage gain.
SELECT trade_date,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc
ORDER BY pct_change DESC
LIMIT 10;Find the top 10 days with the biggest percentage loss.
SELECT trade_date,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc
ORDER BY pct_change ASC
LIMIT 10;Show intraday volatility as (high_p - low_p) / open_p * 100 for each day.
SELECT trade_date,
ROUND((high_p - low_p) / open_p * 100, 2) AS intraday_volatility_pct
FROM hdfc
ORDER BY trade_date;Label each day as 'Gain', 'Loss' or 'Flat' using CASE, based on close vs previous close.
SELECT trade_date, prev_close, close_p,
CASE
WHEN close_p > prev_close THEN 'Gain'
WHEN close_p < prev_close THEN 'Loss'
ELSE 'Flat'
END AS day_type
FROM hdfc
ORDER BY trade_date;Count the number of Gain, Loss and Flat days using CASE with GROUP BY.
SELECT day_type, COUNT(*) AS days
FROM (
SELECT CASE
WHEN close_p > prev_close THEN 'Gain'
WHEN close_p < prev_close THEN 'Loss'
ELSE 'Flat'
END AS day_type
FROM hdfc
) t
GROUP BY day_type;Classify each day's volume as 'High' (> 2 crore), 'Medium' (1 to 2 crore) or 'Low' (< 1 crore).
SELECT trade_date, volume,
CASE
WHEN volume > 20000000 THEN 'High'
WHEN volume >= 10000000 THEN 'Medium'
ELSE 'Low'
END AS volume_category
FROM hdfc
ORDER BY trade_date;Count days in each volume category from the previous question.
SELECT volume_category, COUNT(*) AS days
FROM (
SELECT CASE
WHEN volume > 20000000 THEN 'High'
WHEN volume >= 10000000 THEN 'Medium'
ELSE 'Low'
END AS volume_category
FROM hdfc
) t
GROUP BY volume_category;Show the gap (open_p - prev_close) for each day and flag gap-ups greater than 1%.
SELECT trade_date, prev_close, open_p,
(open_p - prev_close) AS gap,
ROUND((open_p - prev_close) / prev_close * 100, 2) AS gap_pct,
CASE WHEN (open_p - prev_close) / prev_close * 100 > 1
THEN 'Gap-up > 1%' ELSE '' END AS flag
FROM hdfc
ORDER BY trade_date;Find days where the close was within 0.5% of the day's high.
SELECT trade_date, high_p, close_p
FROM hdfc
WHERE close_p >= high_p * 0.995
ORDER BY trade_date;Find days where the close was within 0.5% of the day's low.
SELECT trade_date, low_p, close_p
FROM hdfc
WHERE close_p <= low_p * 1.005
ORDER BY trade_date;Show how far the close is from the 52-week high, as a percentage, for each day.
SELECT trade_date, close_p, wk52_high,
ROUND((wk52_high - close_p) / wk52_high * 100, 2) AS pct_below_52w_high
FROM hdfc
ORDER BY trade_date;Find days when the closing price was within 5% of the 52-week low.
SELECT trade_date, close_p, wk52_low
FROM hdfc
WHERE close_p <= wk52_low * 1.05
ORDER BY trade_date;Calculate the 'body size' (ABS(close_p - open_p)) and show the 10 largest candle bodies.
SELECT trade_date, open_p, close_p,
ABS(close_p - open_p) AS body_size
FROM hdfc
ORDER BY body_size DESC
LIMIT 10;Find days where the close was above VWAP and calculate the percentage of such days.
-- Days where close was above VWAP
SELECT trade_date, close_p, vwap
FROM hdfc
WHERE close_p > vwap
ORDER BY trade_date;
-- Percentage of such days
SELECT ROUND(100 * SUM(close_p > vwap) / COUNT(*), 2) AS pct_days_close_above_vwap
FROM hdfc;Section E — Window Functions Q71–85
Add a row number to each day ordered by trade_date.
SELECT ROW_NUMBER() OVER (ORDER BY trade_date) AS rn, trade_date, close_p
FROM hdfc;Rank days by closing price (highest first) using RANK().
SELECT trade_date, close_p,
RANK() OVER (ORDER BY close_p DESC) AS price_rank
FROM hdfc;Rank days by volume using DENSE_RANK().
SELECT trade_date, volume,
DENSE_RANK() OVER (ORDER BY volume DESC) AS volume_rank
FROM hdfc;Show each day's close alongside the previous day's close using LAG().
SELECT trade_date, close_p,
LAG(close_p) OVER (ORDER BY trade_date) AS prev_day_close
FROM hdfc;Show each day's close alongside the next day's close using LEAD().
SELECT trade_date, close_p,
LEAD(close_p) OVER (ORDER BY trade_date) AS next_day_close
FROM hdfc;Calculate the day-over-day change in closing price using LAG().
SELECT trade_date, close_p,
close_p - LAG(close_p) OVER (ORDER BY trade_date) AS change_vs_prev_day
FROM hdfc;Calculate a 5-day moving average of the closing price.
SELECT trade_date, close_p,
ROUND(AVG(close_p) OVER (
ORDER BY trade_date
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
), 2) AS ma_5
FROM hdfc;Calculate a 20-day moving average of the closing price.
SELECT trade_date, close_p,
ROUND(AVG(close_p) OVER (
ORDER BY trade_date
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
), 2) AS ma_20
FROM hdfc;Calculate a 50-day moving average and show days where the close is above it.
WITH ma AS (
SELECT trade_date, close_p,
AVG(close_p) OVER (
ORDER BY trade_date
ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
) AS ma_50,
COUNT(*) OVER (
ORDER BY trade_date
ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
) AS n_days
FROM hdfc
)
SELECT trade_date, close_p, ROUND(ma_50, 2) AS ma_50
FROM ma
WHERE n_days = 50 -- only full 50-day windows
AND close_p > ma_50
ORDER BY trade_date;Calculate the running total of volume ordered by date.
SELECT trade_date, volume,
SUM(volume) OVER (ORDER BY trade_date) AS running_volume
FROM hdfc;Calculate a cumulative maximum of the closing price (highest close so far).
SELECT trade_date, close_p,
MAX(close_p) OVER (ORDER BY trade_date) AS highest_close_so_far
FROM hdfc;Calculate the drawdown from the running maximum close for each day.
SELECT trade_date, close_p,
MAX(close_p) OVER (ORDER BY trade_date) AS running_max,
ROUND((close_p - MAX(close_p) OVER (ORDER BY trade_date))
/ MAX(close_p) OVER (ORDER BY trade_date) * 100, 2) AS drawdown_pct
FROM hdfc;Find the maximum drawdown over the period.
WITH dd AS (
SELECT trade_date, close_p,
(close_p - MAX(close_p) OVER (ORDER BY trade_date))
/ MAX(close_p) OVER (ORDER BY trade_date) * 100 AS drawdown_pct
FROM hdfc
)
SELECT trade_date, ROUND(drawdown_pct, 2) AS max_drawdown_pct
FROM dd
ORDER BY drawdown_pct ASC
LIMIT 1;Within each month, rank days by volume and show the top 3 days per month.
SELECT ym, trade_date, volume, rn
FROM (
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, trade_date, volume,
ROW_NUMBER() OVER (
PARTITION BY DATE_FORMAT(trade_date, '%Y-%m')
ORDER BY volume DESC
) AS rn
FROM hdfc
) t
WHERE rn <= 3
ORDER BY ym, rn;Calculate a 7-day moving average of volume and flag days where volume is more than 2x that average.
-- Average of the previous 7 trading days (excluding today)
WITH v AS (
SELECT trade_date, volume,
AVG(volume) OVER (
ORDER BY trade_date
ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
) AS ma_7_volume
FROM hdfc
)
SELECT trade_date, volume, ROUND(ma_7_volume, 0) AS ma_7_volume,
CASE WHEN volume > 2 * ma_7_volume THEN 'Volume spike' ELSE '' END AS flag
FROM v
WHERE ma_7_volume IS NOT NULL
ORDER BY trade_date;Section F — Subqueries and CTEs Q86–95
Find days where the closing price was above the overall average closing price.
SELECT trade_date, close_p
FROM hdfc
WHERE close_p > (SELECT AVG(close_p) FROM hdfc)
ORDER BY trade_date;Find days where volume exceeded the average volume of the whole period.
SELECT trade_date, volume
FROM hdfc
WHERE volume > (SELECT AVG(volume) FROM hdfc)
ORDER BY trade_date;Find the date(s) when the closing price equals the maximum closing price (subquery).
SELECT trade_date, close_p
FROM hdfc
WHERE close_p = (SELECT MAX(close_p) FROM hdfc);Using a CTE, compute daily returns and then find the average daily return.
WITH r AS (
SELECT trade_date,
(close_p - prev_close) / prev_close * 100 AS ret_pct
FROM hdfc
)
SELECT ROUND(AVG(ret_pct), 4) AS avg_daily_return_pct
FROM r;Using a CTE, find the longest streak of consecutive days the stock closed higher than the previous close.
WITH flagged AS (
SELECT trade_date, (close_p > prev_close) AS is_up
FROM hdfc
),
grp AS (
SELECT trade_date, is_up,
ROW_NUMBER() OVER (ORDER BY trade_date)
- ROW_NUMBER() OVER (PARTITION BY is_up ORDER BY trade_date) AS g
FROM flagged
)
SELECT MIN(trade_date) AS streak_start,
MAX(trade_date) AS streak_end,
COUNT(*) AS streak_days
FROM grp
WHERE is_up = 1
GROUP BY g
ORDER BY streak_days DESC
LIMIT 1;Using a CTE, find the longest streak of consecutive down days.
WITH flagged AS (
SELECT trade_date, (close_p < prev_close) AS is_down
FROM hdfc
),
grp AS (
SELECT trade_date, is_down,
ROW_NUMBER() OVER (ORDER BY trade_date)
- ROW_NUMBER() OVER (PARTITION BY is_down ORDER BY trade_date) AS g
FROM flagged
)
SELECT MIN(trade_date) AS streak_start,
MAX(trade_date) AS streak_end,
COUNT(*) AS streak_days
FROM grp
WHERE is_down = 1
GROUP BY g
ORDER BY streak_days DESC
LIMIT 1;Find the month with the best return (last close minus first close of the month, as a percentage).
WITH m AS (
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, close_p,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date) AS rn_first,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date DESC) AS rn_last
FROM hdfc
)
SELECT ym,
ROUND((MAX(CASE WHEN rn_last = 1 THEN close_p END)
- MAX(CASE WHEN rn_first = 1 THEN close_p END))
/ MAX(CASE WHEN rn_first = 1 THEN close_p END) * 100, 2) AS month_return_pct
FROM m
GROUP BY ym
ORDER BY month_return_pct DESC
LIMIT 1;Find the month with the worst return using the same method.
WITH m AS (
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, close_p,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date) AS rn_first,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m') ORDER BY trade_date DESC) AS rn_last
FROM hdfc
)
SELECT ym,
ROUND((MAX(CASE WHEN rn_last = 1 THEN close_p END)
- MAX(CASE WHEN rn_first = 1 THEN close_p END))
/ MAX(CASE WHEN rn_first = 1 THEN close_p END) * 100, 2) AS month_return_pct
FROM m
GROUP BY ym
ORDER BY month_return_pct ASC
LIMIT 1;Find days on which the close was the highest in the trailing 30 trading days.
WITH t AS (
SELECT trade_date, close_p,
MAX(close_p) OVER (
ORDER BY trade_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS max_30d,
COUNT(*) OVER (
ORDER BY trade_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS n_days
FROM hdfc
)
SELECT trade_date, close_p
FROM t
WHERE n_days = 30 AND close_p = max_30d
ORDER BY trade_date;Find days on which the 52-week-high or 52-week-low recorded in the file changed compared with the previous day.
WITH x AS (
SELECT trade_date, wk52_high, wk52_low,
LAG(wk52_high) OVER (ORDER BY trade_date) AS prev_high,
LAG(wk52_low) OVER (ORDER BY trade_date) AS prev_low
FROM hdfc
)
SELECT trade_date,
CASE WHEN wk52_high > prev_high THEN 'New 52-week high'
WHEN wk52_low < prev_low THEN 'New 52-week low'
END AS event,
wk52_high, wk52_low
FROM x
WHERE wk52_high > prev_high OR wk52_low < prev_low
ORDER BY trade_date;Section G — Advanced / Analytics Q96–100
Create a view v_daily_returns containing trade_date, close_p and percentage change, then query it for days with returns above 2%.
CREATE OR REPLACE VIEW v_daily_returns AS
SELECT trade_date, close_p,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc;
SELECT *
FROM v_daily_returns
WHERE pct_change > 2
ORDER BY pct_change DESC;Show the average volume on days with a gain versus days with a loss.
SELECT CASE
WHEN close_p > prev_close THEN 'Gain'
WHEN close_p < prev_close THEN 'Loss'
ELSE 'Flat'
END AS day_type,
COUNT(*) AS days,
ROUND(AVG(volume), 0) AS avg_volume
FROM hdfc
GROUP BY day_type;Detect 'golden cross' dates, where the 50-day moving average crosses above the 200-day moving average (use CTEs and LAG()).
WITH ma AS (
SELECT trade_date, close_p,
AVG(close_p) OVER (ORDER BY trade_date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS ma_50,
AVG(close_p) OVER (ORDER BY trade_date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma_200,
ROW_NUMBER() OVER (ORDER BY trade_date) AS rn
FROM hdfc
),
x AS (
SELECT trade_date, ma_50, ma_200,
LAG(ma_50) OVER (ORDER BY trade_date) AS prev_ma_50,
LAG(ma_200) OVER (ORDER BY trade_date) AS prev_ma_200
FROM ma
WHERE rn >= 200 -- need a full 200-day window
)
SELECT trade_date, ROUND(ma_50, 2) AS ma_50, ROUND(ma_200, 2) AS ma_200
FROM x
WHERE prev_ma_50 <= prev_ma_200
AND ma_50 > ma_200
ORDER BY trade_date;Calculate the annualised volatility: standard deviation of daily returns multiplied by SQRT(252).
WITH r AS (
SELECT (close_p - prev_close) / prev_close AS ret
FROM hdfc
)
SELECT ROUND(STDDEV_SAMP(ret) * SQRT(252) * 100, 2) AS annualised_volatility_pct
FROM r;Create an index on trade_date / volume as appropriate, then write a stored procedure get_stats(start_date, end_date) that returns the average close, total volume, highest high and lowest low for the given range.
-- trade_date is already the PRIMARY KEY (indexed).
-- Add an index for volume-based queries:
CREATE INDEX idx_hdfc_volume ON hdfc (volume);
DELIMITER $$
CREATE PROCEDURE get_stats(IN start_date DATE, IN end_date DATE)
BEGIN
SELECT ROUND(AVG(close_p), 2) AS avg_close,
SUM(volume) AS total_volume,
MAX(high_p) AS highest_high,
MIN(low_p) AS lowest_low
FROM hdfc
WHERE trade_date BETWEEN start_date AND end_date;
END$$
DELIMITER ;
CALL get_stats('2026-01-01', '2026-03-31');
100 MySQL Questions with Answers — HDFC Bank (NSE) Daily Quotes
Source file: Quote-Equity-HDFCBANK-EQ-10-09-2025-10-09-2026 1.csv (248 trading days)
Requires MySQL 8.0+ (window functions and CTEs).
Setup (run once)
The CSV stores numbers as text with Indian-style commas ("1,020.50", "3,91,73,990") and dates like 09-Sep-2026, so load it raw and clean it.
CREATE DATABASE IF NOT EXISTS stocks;
USE stocks;
CREATE TABLE hdfc_raw (
trade_date VARCHAR(20),
series VARCHAR(5),
open_p VARCHAR(20),
high_p VARCHAR(20),
low_p VARCHAR(20),
prev_close VARCHAR(20),
ltp VARCHAR(20),
close_p VARCHAR(20),
vwap VARCHAR(20),
wk52_high VARCHAR(20),
wk52_low VARCHAR(20),
volume VARCHAR(30),
value_amt VARCHAR(40),
no_of_trades VARCHAR(30)
);
LOAD DATA LOCAL INFILE 'Quote-Equity-HDFCBANK-EQ-10-09-2025-10-09-2026 1.csv'
INTO TABLE hdfc_raw
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
CREATE TABLE hdfc (
trade_date DATE PRIMARY KEY,
series CHAR(2),
open_p DECIMAL(10,2),
high_p DECIMAL(10,2),
low_p DECIMAL(10,2),
prev_close DECIMAL(10,2),
ltp DECIMAL(10,2),
close_p DECIMAL(10,2),
vwap DECIMAL(10,2),
wk52_high DECIMAL(10,2),
wk52_low DECIMAL(10,2),
volume BIGINT,
value_amt DECIMAL(20,2),
no_of_trades INT
);
INSERT INTO hdfc
SELECT STR_TO_DATE(trade_date,'%d-%b-%Y'), series,
REPLACE(open_p,',',''), REPLACE(high_p,',',''), REPLACE(low_p,',',''),
REPLACE(prev_close,',',''), REPLACE(ltp,',',''), REPLACE(close_p,',',''),
REPLACE(vwap,',',''), REPLACE(wk52_high,',',''), REPLACE(wk52_low,',',''),
REPLACE(volume,',',''), REPLACE(value_amt,',',''), REPLACE(no_of_trades,',','')
FROM hdfc_raw;
Section A — Basics (Q1–Q20)
Q1. Display all records from the table.
SELECT * FROM hdfc;
Q2. Show only trade_date, open_p and close_p for all days.
SELECT trade_date, open_p, close_p FROM hdfc;
Q3. Count the total number of trading days.
SELECT COUNT(*) AS total_days FROM hdfc;
Q4. Find the earliest and latest trade_date.
SELECT MIN(trade_date) AS first_day, MAX(trade_date) AS last_day FROM hdfc;
Q5. List all days on which the closing price was above 1,000.
SELECT trade_date, close_p FROM hdfc WHERE close_p > 1000 ORDER BY trade_date;
Q6. List all days on which the closing price was below 700.
SELECT trade_date, close_p FROM hdfc WHERE close_p < 700 ORDER BY trade_date;
Q7. Show the 10 most recent trading days.
SELECT * FROM hdfc ORDER BY trade_date DESC LIMIT 10;
Q8. Show the 10 oldest trading days.
SELECT * FROM hdfc ORDER BY trade_date ASC LIMIT 10;
Q9. Find the day with the highest closing price.
SELECT trade_date, close_p FROM hdfc ORDER BY close_p DESC LIMIT 1;
Q10. Find the day with the lowest closing price.
SELECT trade_date, close_p FROM hdfc ORDER BY close_p ASC LIMIT 1;
Q11. Find the day with the highest intraday high.
SELECT trade_date, high_p FROM hdfc ORDER BY high_p DESC LIMIT 1;
Q12. Find the day with the lowest intraday low.
SELECT trade_date, low_p FROM hdfc ORDER BY low_p ASC LIMIT 1;
Q13. List the top 5 days by traded volume.
SELECT trade_date, volume FROM hdfc ORDER BY volume DESC LIMIT 5;
Q14. List the bottom 5 days by traded volume.
SELECT trade_date, volume FROM hdfc ORDER BY volume ASC LIMIT 5;
Q15. Show all trading days in January 2026.
SELECT * FROM hdfc
WHERE trade_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY trade_date;
Q16. Show all trading days between 1 March 2026 and 31 March 2026.
SELECT * FROM hdfc
WHERE trade_date BETWEEN '2026-03-01' AND '2026-03-31'
ORDER BY trade_date;
Q17. List days where the stock opened higher than the previous close.
SELECT trade_date, prev_close, open_p FROM hdfc WHERE open_p > prev_close;
Q18. List days where the stock opened lower than the previous close.
SELECT trade_date, prev_close, open_p FROM hdfc WHERE open_p < prev_close;
Q19. List days where the closing price equals the opening price.
SELECT trade_date, open_p, close_p FROM hdfc WHERE close_p = open_p;
Q20. Show distinct values of series.
SELECT DISTINCT series FROM hdfc;
Section B — Aggregate Functions (Q21–Q35)
Q21. Average closing price over the whole period.
SELECT ROUND(AVG(close_p), 2) AS avg_close FROM hdfc;
Q22. Maximum and minimum closing price in one query.
SELECT MAX(close_p) AS max_close, MIN(close_p) AS min_close FROM hdfc;
Q23. Total volume traded over the whole period.
SELECT SUM(volume) AS total_volume FROM hdfc;
Q24. Average daily volume.
SELECT ROUND(AVG(volume)) AS avg_volume FROM hdfc;
Q25. Total traded value over the whole period.
SELECT SUM(value_amt) AS total_value FROM hdfc;
Q26. Average number of trades per day.
SELECT ROUND(AVG(no_of_trades)) AS avg_trades FROM hdfc;
Q27. Highest number of trades in a single day and its date.
SELECT trade_date, no_of_trades FROM hdfc ORDER BY no_of_trades DESC LIMIT 1;
Q28. Average VWAP for the whole period.
SELECT ROUND(AVG(vwap), 2) AS avg_vwap FROM hdfc;
Q29. Count days the stock closed higher than it opened.
SELECT COUNT(*) AS up_days FROM hdfc WHERE close_p > open_p;
Q30. Count days the stock closed lower than it opened.
SELECT COUNT(*) AS down_days FROM hdfc WHERE close_p < open_p;
Q31. Count days the close was higher than the previous close.
SELECT COUNT(*) AS gain_days FROM hdfc WHERE close_p > prev_close;
Q32. Average daily range (high - low).
SELECT ROUND(AVG(high_p - low_p), 2) AS avg_range FROM hdfc;
Q33. Largest single-day range and its date.
SELECT trade_date, ROUND(high_p - low_p, 2) AS day_range
FROM hdfc
ORDER BY (high_p - low_p) DESC
LIMIT 1;
Q34. Smallest single-day range and its date.
SELECT trade_date, ROUND(high_p - low_p, 2) AS day_range
FROM hdfc
ORDER BY (high_p - low_p) ASC
LIMIT 1;
Q35. Standard deviation of the closing price.
SELECT ROUND(STDDEV_SAMP(close_p), 2) AS std_close FROM hdfc;
Section C — Date Functions and GROUP BY (Q36–Q55)
Q36. Average closing price for each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q37. Total volume for each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, SUM(volume) AS total_volume
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q38. Month with the highest total volume.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, SUM(volume) AS total_volume
FROM hdfc
GROUP BY ym
ORDER BY total_volume DESC
LIMIT 1;
Q39. Month with the highest average closing price.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY ym
ORDER BY avg_close DESC
LIMIT 1;
Q40. Month with the lowest average closing price.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY ym
ORDER BY avg_close ASC
LIMIT 1;
Q41. Number of trading days in each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, COUNT(*) AS trading_days
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q42. Average closing price for each quarter.
SELECT YEAR(trade_date) AS yr, QUARTER(trade_date) AS qtr,
ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY yr, qtr
ORDER BY yr, qtr;
Q43. Average volume for each day of the week.
SELECT DAYNAME(trade_date) AS weekday, ROUND(AVG(volume)) AS avg_volume
FROM hdfc
GROUP BY WEEKDAY(trade_date), DAYNAME(trade_date)
ORDER BY WEEKDAY(trade_date);
Q44. Weekday with the highest average closing price.
SELECT DAYNAME(trade_date) AS weekday, ROUND(AVG(close_p), 2) AS avg_close
FROM hdfc
GROUP BY DAYNAME(trade_date)
ORDER BY avg_close DESC
LIMIT 1;
Q45. Weekday with the highest average volume.
SELECT DAYNAME(trade_date) AS weekday, ROUND(AVG(volume)) AS avg_volume
FROM hdfc
GROUP BY DAYNAME(trade_date)
ORDER BY avg_volume DESC
LIMIT 1;
Q46. Count trading days on each weekday.
SELECT DAYNAME(trade_date) AS weekday, COUNT(*) AS days_count
FROM hdfc
GROUP BY WEEKDAY(trade_date), DAYNAME(trade_date)
ORDER BY WEEKDAY(trade_date);
Q47. For each month, the highest high and lowest low.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym,
MAX(high_p) AS month_high, MIN(low_p) AS month_low
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q48. For each month, the average daily range.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym,
ROUND(AVG(high_p - low_p), 2) AS avg_range
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q49. For each month, the number of days the stock closed higher than it opened.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym,
SUM(close_p > open_p) AS up_days
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q50. Average number of trades for each month.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(no_of_trades)) AS avg_trades
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q51. For each month, total traded value in crores.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym,
ROUND(SUM(value_amt) / 10000000, 2) AS value_crore
FROM hdfc
GROUP BY ym
ORDER BY ym;
Q52. Months that have more than 20 trading days.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, COUNT(*) AS trading_days
FROM hdfc
GROUP BY ym
HAVING COUNT(*) > 20;
Q53. Months where average volume exceeded the overall average volume.
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, ROUND(AVG(volume)) AS avg_volume
FROM hdfc
GROUP BY ym
HAVING AVG(volume) > (SELECT AVG(volume) FROM hdfc)
ORDER BY ym;
Q54. Week with the highest total volume.
SELECT YEARWEEK(trade_date, 1) AS year_week, SUM(volume) AS total_volume
FROM hdfc
GROUP BY year_week
ORDER BY total_volume DESC
LIMIT 1;
Q55. For each quarter, the maximum closing price and the date it occurred.
WITH ranked AS (
SELECT trade_date, close_p,
YEAR(trade_date) AS yr, QUARTER(trade_date) AS qtr,
RANK() OVER (PARTITION BY YEAR(trade_date), QUARTER(trade_date)
ORDER BY close_p DESC) AS rk
FROM hdfc
)
SELECT yr, qtr, trade_date, close_p
FROM ranked
WHERE rk = 1
ORDER BY yr, qtr;
Section D — Calculated Columns and CASE (Q56–Q70)
Q56. Daily percentage change: (close - prev_close) / prev_close * 100.
SELECT trade_date, close_p, prev_close,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc
ORDER BY trade_date;
Q57. Top 10 days with the biggest percentage gain.
SELECT trade_date,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc
ORDER BY pct_change DESC
LIMIT 10;
Q58. Top 10 days with the biggest percentage loss.
SELECT trade_date,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc
ORDER BY pct_change ASC
LIMIT 10;
Q59. Intraday volatility: (high - low) / open * 100.
SELECT trade_date,
ROUND((high_p - low_p) / open_p * 100, 2) AS intraday_vol_pct
FROM hdfc
ORDER BY trade_date;
Q60. Label each day as Gain, Loss or Flat using CASE.
SELECT trade_date, close_p, prev_close,
CASE
WHEN close_p > prev_close THEN 'Gain'
WHEN close_p < prev_close THEN 'Loss'
ELSE 'Flat'
END AS day_type
FROM hdfc
ORDER BY trade_date;
Q61. Count the number of Gain, Loss and Flat days.
SELECT day_type, COUNT(*) AS days_count
FROM (
SELECT CASE
WHEN close_p > prev_close THEN 'Gain'
WHEN close_p < prev_close THEN 'Loss'
ELSE 'Flat'
END AS day_type
FROM hdfc
) t
GROUP BY day_type;
Q62. Classify volume: High (> 2 crore), Medium (1–2 crore), Low (< 1 crore).
SELECT trade_date, volume,
CASE
WHEN volume > 20000000 THEN 'High'
WHEN volume >= 10000000 THEN 'Medium'
ELSE 'Low'
END AS volume_category
FROM hdfc
ORDER BY trade_date;
Q63. Count days in each volume category.
SELECT volume_category, COUNT(*) AS days_count
FROM (
SELECT CASE
WHEN volume > 20000000 THEN 'High'
WHEN volume >= 10000000 THEN 'Medium'
ELSE 'Low'
END AS volume_category
FROM hdfc
) t
GROUP BY volume_category;
Q64. Gap (open - prev_close) for each day, flagging gap-ups greater than 1%.
SELECT trade_date,
ROUND(open_p - prev_close, 2) AS gap,
ROUND((open_p - prev_close) / prev_close * 100, 2) AS gap_pct,
CASE WHEN (open_p - prev_close) / prev_close * 100 > 1
THEN 'Gap-up >1%' ELSE '' END AS flag
FROM hdfc
ORDER BY trade_date;
Q65. Days where the close was within 0.5% of the day's high.
SELECT trade_date, high_p, close_p
FROM hdfc
WHERE close_p >= high_p * 0.995;
Q66. Days where the close was within 0.5% of the day's low.
SELECT trade_date, low_p, close_p
FROM hdfc
WHERE close_p <= low_p * 1.005;
Q67. Distance of the close from the 52-week high, as a percentage.
SELECT trade_date, close_p, wk52_high,
ROUND((wk52_high - close_p) / wk52_high * 100, 2) AS pct_below_52w_high
FROM hdfc
ORDER BY trade_date;
Q68. Days when the close was within 5% of the 52-week low.
SELECT trade_date, close_p, wk52_low
FROM hdfc
WHERE close_p <= wk52_low * 1.05;
Q69. Ten largest candle bodies, ABS(close - open).
SELECT trade_date, open_p, close_p,
ROUND(ABS(close_p - open_p), 2) AS body_size
FROM hdfc
ORDER BY body_size DESC
LIMIT 10;
Q70. Percentage of days the close was above VWAP.
SELECT ROUND(100 * SUM(close_p > vwap) / COUNT(*), 2) AS pct_days_close_above_vwap
FROM hdfc;
Section E — Window Functions (Q71–Q85)
Q71. Add a row number to each day ordered by trade_date.
SELECT ROW_NUMBER() OVER (ORDER BY trade_date) AS rn, trade_date, close_p
FROM hdfc;
Q72. Rank days by closing price (highest first) using RANK().
SELECT trade_date, close_p,
RANK() OVER (ORDER BY close_p DESC) AS price_rank
FROM hdfc;
Q73. Rank days by volume using DENSE_RANK().
SELECT trade_date, volume,
DENSE_RANK() OVER (ORDER BY volume DESC) AS volume_rank
FROM hdfc;
Q74. Each day's close alongside the previous day's close using LAG().
SELECT trade_date, close_p,
LAG(close_p) OVER (ORDER BY trade_date) AS prev_day_close
FROM hdfc;
Q75. Each day's close alongside the next day's close using LEAD().
SELECT trade_date, close_p,
LEAD(close_p) OVER (ORDER BY trade_date) AS next_day_close
FROM hdfc;
Q76. Day-over-day change in closing price using LAG().
SELECT trade_date, close_p,
ROUND(close_p - LAG(close_p) OVER (ORDER BY trade_date), 2) AS change_vs_prev
FROM hdfc;
Q77. 5-day moving average of the closing price.
SELECT trade_date, close_p,
ROUND(AVG(close_p) OVER (ORDER BY trade_date
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW), 2) AS ma5
FROM hdfc;
Q78. 20-day moving average of the closing price.
SELECT trade_date, close_p,
ROUND(AVG(close_p) OVER (ORDER BY trade_date
ROWS BETWEEN 19 PRECEDING AND CURRENT ROW), 2) AS ma20
FROM hdfc;
Q79. 50-day moving average, showing days where the close is above it.
WITH t AS (
SELECT trade_date, close_p,
ROW_NUMBER() OVER (ORDER BY trade_date) AS rn,
AVG(close_p) OVER (ORDER BY trade_date
ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS ma50
FROM hdfc
)
SELECT trade_date, close_p, ROUND(ma50, 2) AS ma50
FROM t
WHERE rn >= 50 AND close_p > ma50;
Q80. Running total of volume ordered by date.
SELECT trade_date, volume,
SUM(volume) OVER (ORDER BY trade_date) AS running_volume
FROM hdfc;
Q81. Cumulative maximum closing price (highest close so far).
SELECT trade_date, close_p,
MAX(close_p) OVER (ORDER BY trade_date) AS highest_close_so_far
FROM hdfc;
Q82. Drawdown from the running maximum close for each day.
WITH t AS (
SELECT trade_date, close_p,
MAX(close_p) OVER (ORDER BY trade_date) AS run_max
FROM hdfc
)
SELECT trade_date, close_p, run_max,
ROUND((close_p - run_max) / run_max * 100, 2) AS drawdown_pct
FROM t;
Q83. Maximum drawdown over the period.
WITH t AS (
SELECT trade_date, close_p,
MAX(close_p) OVER (ORDER BY trade_date) AS run_max
FROM hdfc
)
SELECT ROUND(MIN((close_p - run_max) / run_max * 100), 2) AS max_drawdown_pct
FROM t;
Q84. Within each month, the top 3 days by volume.
WITH t AS (
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, trade_date, volume,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m')
ORDER BY volume DESC) AS rn
FROM hdfc
)
SELECT ym, trade_date, volume, rn
FROM t
WHERE rn <= 3
ORDER BY ym, rn;
Q85. 7-day moving average of volume; flag days where volume is more than 2x that average.
WITH t AS (
SELECT trade_date, volume,
AVG(volume) OVER (ORDER BY trade_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_volume
FROM hdfc
)
SELECT trade_date, volume, ROUND(ma7_volume) AS ma7_volume
FROM t
WHERE volume > 2 * ma7_volume;
Section F — Subqueries and CTEs (Q86–Q95)
Q86. Days where the close was above the overall average close.
SELECT trade_date, close_p
FROM hdfc
WHERE close_p > (SELECT AVG(close_p) FROM hdfc)
ORDER BY trade_date;
Q87. Days where volume exceeded the average volume of the whole period.
SELECT trade_date, volume
FROM hdfc
WHERE volume > (SELECT AVG(volume) FROM hdfc)
ORDER BY trade_date;
Q88. Date(s) when the close equals the maximum close (subquery).
SELECT trade_date, close_p
FROM hdfc
WHERE close_p = (SELECT MAX(close_p) FROM hdfc);
Q89. Using a CTE, compute daily returns and find the average daily return.
WITH r AS (
SELECT trade_date,
(close_p - prev_close) / prev_close * 100 AS ret
FROM hdfc
)
SELECT ROUND(AVG(ret), 4) AS avg_daily_return_pct FROM r;
Q90. Longest streak of consecutive days the stock closed higher than the previous close.
WITH flagged AS (
SELECT trade_date,
(close_p > prev_close) AS up,
ROW_NUMBER() OVER (ORDER BY trade_date) AS rn
FROM hdfc
),
grp AS (
SELECT trade_date, up,
rn - ROW_NUMBER() OVER (PARTITION BY up ORDER BY trade_date) AS g
FROM flagged
)
SELECT MIN(trade_date) AS streak_start,
MAX(trade_date) AS streak_end,
COUNT(*) AS streak_days
FROM grp
WHERE up = 1
GROUP BY g
ORDER BY streak_days DESC
LIMIT 1;
Q91. Longest streak of consecutive down days.
WITH flagged AS (
SELECT trade_date,
(close_p < prev_close) AS down,
ROW_NUMBER() OVER (ORDER BY trade_date) AS rn
FROM hdfc
),
grp AS (
SELECT trade_date, down,
rn - ROW_NUMBER() OVER (PARTITION BY down ORDER BY trade_date) AS g
FROM flagged
)
SELECT MIN(trade_date) AS streak_start,
MAX(trade_date) AS streak_end,
COUNT(*) AS streak_days
FROM grp
WHERE down = 1
GROUP BY g
ORDER BY streak_days DESC
LIMIT 1;
Q92. Month with the best return (last close vs first close of the month, in %).
WITH m AS (
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, close_p,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m')
ORDER BY trade_date ASC) AS rn_asc,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m')
ORDER BY trade_date DESC) AS rn_desc
FROM hdfc
)
SELECT ym, first_close, last_close,
ROUND((last_close - first_close) / first_close * 100, 2) AS month_return_pct
FROM (
SELECT ym,
MAX(CASE WHEN rn_asc = 1 THEN close_p END) AS first_close,
MAX(CASE WHEN rn_desc = 1 THEN close_p END) AS last_close
FROM m
GROUP BY ym
) x
ORDER BY month_return_pct DESC
LIMIT 1;
Q93. Month with the worst return (same method).
WITH m AS (
SELECT DATE_FORMAT(trade_date, '%Y-%m') AS ym, close_p,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m')
ORDER BY trade_date ASC) AS rn_asc,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(trade_date, '%Y-%m')
ORDER BY trade_date DESC) AS rn_desc
FROM hdfc
)
SELECT ym, first_close, last_close,
ROUND((last_close - first_close) / first_close * 100, 2) AS month_return_pct
FROM (
SELECT ym,
MAX(CASE WHEN rn_asc = 1 THEN close_p END) AS first_close,
MAX(CASE WHEN rn_desc = 1 THEN close_p END) AS last_close
FROM m
GROUP BY ym
) x
ORDER BY month_return_pct ASC
LIMIT 1;
Q94. Days on which the close was the highest in the trailing 30 trading days.
WITH t AS (
SELECT trade_date, close_p,
MAX(close_p) OVER (ORDER BY trade_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS max30
FROM hdfc
)
SELECT trade_date, close_p
FROM t
WHERE close_p = max30;
Q95. Days where the 52-week high rose or the 52-week low fell compared with the previous day.
WITH t AS (
SELECT trade_date, wk52_high, wk52_low,
LAG(wk52_high) OVER (ORDER BY trade_date) AS prev_high,
LAG(wk52_low) OVER (ORDER BY trade_date) AS prev_low
FROM hdfc
)
SELECT trade_date, wk52_high, prev_high, wk52_low, prev_low,
CASE
WHEN wk52_high > prev_high THEN 'New 52W High'
WHEN wk52_low < prev_low THEN 'New 52W Low'
END AS event
FROM t
WHERE wk52_high > prev_high OR wk52_low < prev_low;
Section G — Advanced / Analytics (Q96–Q100)
Q96. Create a view v_daily_returns (trade_date, close_p, % change) and query days with returns above 2%.
CREATE OR REPLACE VIEW v_daily_returns AS
SELECT trade_date, close_p,
ROUND((close_p - prev_close) / prev_close * 100, 2) AS pct_change
FROM hdfc;
SELECT * FROM v_daily_returns WHERE pct_change > 2 ORDER BY pct_change DESC;
Q97. Average volume on gain days vs. loss days.
SELECT CASE
WHEN close_p > prev_close THEN 'Gain'
WHEN close_p < prev_close THEN 'Loss'
ELSE 'Flat'
END AS day_type,
COUNT(*) AS days_count,
ROUND(AVG(volume)) AS avg_volume
FROM hdfc
GROUP BY day_type;
Q98. Golden cross dates: 50-day MA crosses above the 200-day MA.
WITH ma AS (
SELECT trade_date, close_p,
ROW_NUMBER() OVER (ORDER BY trade_date) AS rn,
AVG(close_p) OVER (ORDER BY trade_date
ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS ma50,
AVG(close_p) OVER (ORDER BY trade_date
ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma200
FROM hdfc
),
x AS (
SELECT trade_date, ma50, ma200,
LAG(ma50) OVER (ORDER BY trade_date) AS prev50,
LAG(ma200) OVER (ORDER BY trade_date) AS prev200
FROM ma
WHERE rn >= 200 -- a full 200-day window exists only from the 200th row
)
SELECT trade_date, ROUND(ma50, 2) AS ma50, ROUND(ma200, 2) AS ma200
FROM x
WHERE prev50 <= prev200 AND ma50 > ma200;
Note: the file has about 248 rows, so only the last ~49 days have a full 200-day average. An empty result is normal.
Q99. Annualised volatility: standard deviation of daily returns x SQRT(252).
WITH r AS (
SELECT (close_p - prev_close) / prev_close AS ret FROM hdfc
)
SELECT ROUND(STDDEV_SAMP(ret) * SQRT(252) * 100, 2) AS annualised_volatility_pct
FROM r;
Q100. Create an index and a stored procedure get_stats(start_date, end_date) returning average close, total volume, highest high and lowest low.
-- trade_date is already indexed (PRIMARY KEY); add an index on volume
CREATE INDEX idx_hdfc_volume ON hdfc (volume);
DELIMITER //
CREATE PROCEDURE get_stats(IN p_start DATE, IN p_end DATE)
BEGIN
SELECT ROUND(AVG(close_p), 2) AS avg_close,
SUM(volume) AS total_volume,
MAX(high_p) AS highest_high,
MIN(low_p) AS lowest_low
FROM hdfc
WHERE trade_date BETWEEN p_start AND p_end;
END //
DELIMITER ;
CALL get_stats('2026-01-01', '2026-03-31');
Tip: after loading, run SELECT COUNT(*) FROM hdfc; — you should see 248 rows. Note that prev_close for the very first day in the file refers to the day before the file's range, which is fine for percentage-change queries.
100 MySQL Practice Questions — HDFC Bank (NSE) Daily Quotes
Source file: Quote-Equity-HDFCBANK-EQ-10-09-2025-10-09-2026 1.csv (248 trading days)
Setup
The CSV stores numbers as text with Indian-style commas (e.g. "1,020.50", "3,91,73,990") and dates like 09-Sep-2026. Load it raw and clean it, or load it cleaned.
CREATE DATABASE IF NOT EXISTS stocks;
USE stocks;
-- Raw staging table (everything as text, as in the CSV)
CREATE TABLE hdfc_raw (
trade_date VARCHAR(20),
series VARCHAR(5),
open_p VARCHAR(20),
high_p VARCHAR(20),
low_p VARCHAR(20),
prev_close VARCHAR(20),
ltp VARCHAR(20),
close_p VARCHAR(20),
vwap VARCHAR(20),
wk52_high VARCHAR(20),
wk52_low VARCHAR(20),
volume VARCHAR(30),
value_amt VARCHAR(40),
no_of_trades VARCHAR(30)
);
LOAD DATA LOCAL INFILE 'Quote-Equity-HDFCBANK-EQ-10-09-2025-10-09-2026 1.csv'
INTO TABLE hdfc_raw
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
-- Clean table used by all questions below
CREATE TABLE hdfc (
trade_date DATE PRIMARY KEY,
series CHAR(2),
open_p DECIMAL(10,2),
high_p DECIMAL(10,2),
low_p DECIMAL(10,2),
prev_close DECIMAL(10,2),
ltp DECIMAL(10,2),
close_p DECIMAL(10,2),
vwap DECIMAL(10,2),
wk52_high DECIMAL(10,2),
wk52_low DECIMAL(10,2),
volume BIGINT,
value_amt DECIMAL(20,2),
no_of_trades INT
);
INSERT INTO hdfc
SELECT STR_TO_DATE(trade_date,'%d-%b-%Y'), series,
REPLACE(open_p,',',''), REPLACE(high_p,',',''), REPLACE(low_p,',',''),
REPLACE(prev_close,',',''), REPLACE(ltp,',',''), REPLACE(close_p,',',''),
REPLACE(vwap,',',''), REPLACE(wk52_high,',',''), REPLACE(wk52_low,',',''),
REPLACE(volume,',',''), REPLACE(value_amt,',',''), REPLACE(no_of_trades,',','')
FROM hdfc_raw;
Table used in questions: hdfc(trade_date, series, open_p, high_p, low_p, prev_close, ltp, close_p, vwap, wk52_high, wk52_low, volume, value_amt, no_of_trades)
Section A — Basics: SELECT, WHERE, ORDER BY, LIMIT (Q1–Q20)
- Display all records from the table.
- Show only
trade_date,open_p, andclose_pfor all days. - Count the total number of trading days in the data.
- Find the earliest and latest
trade_datein the table. - List all days on which the closing price was above 1,000.
- List all days on which the closing price was below 700.
- Show the 10 most recent trading days.
- Show the 10 oldest trading days.
- Find the day with the highest closing price.
- Find the day with the lowest closing price.
- Find the day with the highest intraday high (
high_p). - Find the day with the lowest intraday low (
low_p). - List the top 5 days by traded volume.
- List the bottom 5 days by traded volume.
- Show all trading days in the month of January 2026.
- Show all trading days between 1 March 2026 and 31 March 2026.
- List days where the stock opened higher than the previous close.
- List days where the stock opened lower than the previous close.
- List days where the closing price equals the opening price.
- Show distinct values of
seriesin the table.
Section B — Aggregate Functions (Q21–Q35)
- Find the average closing price over the whole period.
- Find the maximum and minimum closing price in a single query.
- Find the total volume traded over the whole period.
- Find the average daily volume.
- Find the total traded value (
value_amt) over the whole period. - Find the average number of trades per day.
- Find the highest number of trades in a single day and its date.
- Find the average VWAP for the whole period.
- Count how many days the stock closed higher than it opened.
- Count how many days the stock closed lower than it opened.
- Count how many days the close was higher than the previous close.
- Find the average daily range (
high_p - low_p). - Find the largest single-day range and the date it occurred.
- Find the smallest single-day range and the date it occurred.
- Find the standard deviation of the closing price.
Section C — Date Functions & GROUP BY (Q36–Q55)
- Find the average closing price for each month.
- Find the total volume for each month.
- Find the month with the highest total volume.
- Find the month with the highest average closing price.
- Find the month with the lowest average closing price.
- Find the number of trading days in each month.
- Find the average closing price for each quarter.
- Find the average volume for each day of the week (Monday–Friday).
- Find which weekday has the highest average closing price.
- Find which weekday has the highest average volume.
- Count how many trading days fall on each weekday.
- For each month, find the highest
high_pand lowestlow_p. - For each month, find the average daily range.
- For each year-month, count the number of days the stock closed higher than it opened.
- Find the average number of trades for each month.
- For each month, find the total traded value in crores (divide by 10,000,000).
- List months that have more than 20 trading days.
- List months where the average volume exceeded the overall average volume (use HAVING with a subquery).
- Find the week number with the highest total volume.
- For each quarter, find the maximum closing price and the date it occurred.
Section D — Calculated Columns & CASE (Q56–Q70)
- Show daily percentage change:
(close_p - prev_close) / prev_close * 100. - Find the top 10 days with the biggest percentage gain.
- Find the top 10 days with the biggest percentage loss.
- Show intraday volatility as
(high_p - low_p) / open_p * 100for each day. - Label each day as 'Gain', 'Loss', or 'Flat' using CASE, based on close vs previous close.
- Count the number of Gain, Loss, and Flat days using CASE with GROUP BY.
- Classify each day's volume as 'High' (> 2 crore), 'Medium' (1–2 crore) or 'Low' (< 1 crore).
- Count days in each volume category from Q62.
- Show the gap (
open_p - prev_close) for each day and flag gap-ups greater than 1%. - Find days where the close was within 0.5% of the day's high.
- Find days where the close was within 0.5% of the day's low.
- Show how far the close is from the 52-week high, as a percentage, for each day.
- Find days when the closing price was within 5% of the 52-week low.
- Calculate the 'body size' (
ABS(close_p - open_p)) and show the 10 largest candle bodies. - Find days where the close was above VWAP and calculate the percentage of such days.
Section E — Window Functions (Q71–Q85)
- Add a row number to each day ordered by
trade_date. - Rank days by closing price (highest first) using
RANK(). - Rank days by volume using
DENSE_RANK(). - Show each day's close alongside the previous day's close using
LAG(). - Show each day's close alongside the next day's close using
LEAD(). - Calculate the day-over-day change in closing price using
LAG(). - Calculate a 5-day moving average of the closing price.
- Calculate a 20-day moving average of the closing price.
- Calculate a 50-day moving average and show days where the close is above it.
- Calculate the running total of volume ordered by date.
- Calculate a cumulative maximum of the closing price (highest close so far).
- Calculate the drawdown from the running maximum close for each day.
- Find the maximum drawdown over the period.
- Within each month, rank days by volume and show the top 3 days per month.
- Calculate a 7-day moving average of volume and flag days where volume is more than 2x that average.
Section F — Subqueries & CTEs (Q86–Q95)
- Find days where the closing price was above the overall average closing price.
- Find days where volume exceeded the average volume of the whole period.
- Find the date(s) when the closing price equals the maximum closing price (subquery).
- Using a CTE, compute daily returns and then find the average daily return.
- Using a CTE, find the longest streak of consecutive days the stock closed higher than the previous close.
- Using a CTE, find the longest streak of consecutive down days.
- Find the month with the best return (last close minus first close of the month, as a percentage).
- Find the month with the worst return using the same method.
- Find days on which the close was the highest in the trailing 30 trading days.
- Find days on which the stock made a new 52-week-high or new 52-week-low compared with the previous day's recorded value.
Section G — Advanced / Analytics (Q96–Q100)
- Create a view
v_daily_returnscontainingtrade_date,close_p, and percentage change, then query it for days with returns above 2%. - Find the correlation-style relationship: show the average volume on days with a gain vs. days with a loss.
- Detect "golden cross" dates, where the 50-day moving average crosses above the 200-day moving average (use CTEs and
LAG()). - Calculate the annualised volatility: standard deviation of daily returns multiplied by
SQRT(252). - Create an index on
trade_date/volumeas appropriate, then write a stored procedureget_stats(start_date, end_date)that returns the average close, total volume, highest high and lowest low for the given range.
Tip: after loading, run SELECT COUNT(*) FROM hdfc; — you should see 248 rows.
0 Comments