// EcoFinLearn quiz questions: Advanced Finance SQL
// Source: https://ecofinlearning.com/learn/sql-finance
// (c) Sitraka Forler. Teachers may use these with a class, with a link back: https://ecofinlearning.com/terms#reuse
// Import in Moodle: Question bank > Import > GIFT format.

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/Date Filtering

// sf-m5-l1: SQL Date Filtering & Monthly Returns
::sf-m5-l1 Q1::[plain]Your desk reloads equity_prices every night. A year from now a teammate's "last 12 months" AAPL report, hardcoded to WHERE trade_date BETWEEN '2023-01-01' AND '2023-12-31', still shows 2023 while yours shows the latest year. What went wrong?{
	=The literal dates do not move when new data arrives, so the "trailing" window is frozen in 2023 - anchoring to MAX(trade_date) makes it slide with the table#Right. A fixed literal describes the data as it was on the day the query was written. DATE((SELECT MAX(trade_date)...), '-12 months') is re-evaluated on every run, so it always tracks the newest bar.
	~BETWEEN excludes its endpoints, so the boundary rows were dropped#BETWEEN is inclusive of both endpoints in SQLite - that is not the cause. The issue is the hardcoded dates not moving with the data.
	~strftime cannot be used in a WHERE clause#strftime works fine anywhere an expression is allowed, including WHERE. It is unrelated to the stale result here.
	~AVG(close_price) returns NULL when there are many rows#AVG ignores NULLs and returns a value whenever at least one non-NULL row exists. The stale result is a filtering problem, not an aggregation one.
}

// sf-m5-l1: SQL Date Filtering & Monthly Returns
::sf-m5-l1 Q2::[plain]In the roll-up query, why must the GROUP BY use strftime('%Y-%m', trade_date) rather than GROUP BY trade_date?{
	=trade_date is unique per day, so grouping by it makes one group per day; grouping by the year-month string collapses all days of a month into one row#Exactly. The grouping key defines the folder. The month string '2023-01' is shared by every January day, so AVG/MIN/MAX summarise the whole month into a single output row.
	~trade_date cannot appear in a GROUP BY clause in SQLite#It can - GROUP BY trade_date is perfectly legal. It just produces one row per distinct date, which is not a monthly summary.
	~strftime sorts the results, and GROUP BY requires sorted input#GROUP BY does not require pre-sorted input, and ordering is the job of ORDER BY. strftime here defines the grouping key, not the sort.
	~AVG only works on text columns like the strftime output#Backwards - AVG operates on numeric columns like close_price. strftime produces the text grouping key; it is not what gets averaged.
}

// sf-m5-l1: SQL Date Filtering & Monthly Returns
::sf-m5-l1 Q3::[plain]You want the last 12 months of data that always ends at the newest available bar. Which WHERE clause is both correct and robust to new data arriving?{
	=WHERE trade_date >\= DATE((SELECT MAX(trade_date) FROM equity_prices), '-12 months')#This anchors the window to the freshest date in the table and reaches back exactly one calendar year - it slides automatically and never goes stale.
	~WHERE trade_date >\= '2023-01-01'#A hardcoded literal. It ignores where the data actually ends and will return the wrong span (or nothing) once the table moves on.
	~WHERE strftime('%Y', trade_date) \= '2023'#This pins to a single calendar year, not a trailing 12 months, and hardcodes '2023' - it breaks the moment the newest data is in a different year.
	~WHERE trade_date >\= MAX(trade_date) - 12#Two problems\: an aggregate like MAX cannot sit bare in WHERE (it needs a subquery), and subtracting the integer 12 from a date string is not valid month arithmetic - use DATE(x, '-12 months').
}

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/Window Functions

// sf-m1-l1: SQL Window Functions: 20-Day Moving Average & Volatility
::sf-m1-l1 Q1::[plain]The starter's window frame is ROWS BETWEEN CURRENT ROW AND CURRENT ROW, and vol_20_ann_pct prints NULL for every ticker. Why?{
	=The window holds only 1 row, so n \= 1 and the sample-variance denominator n·(n−1) is 0 - the std-dev is mathematically undefined#Sample variance divides by n−1. With a single-row window that is a division by zero, which SQL returns as NULL. Widening the frame to 20 rows makes n \= 20 and the denominator well-defined.
	~sql.js cannot compute SQRT() inside a window query#SQRT works fine here - the SMA column computes normally. The NULL comes specifically from the vol formula's zero denominator when the window is one row wide.
	~close_price is NULL on the most recent trading day#close_price prints a real number in every row of the output; only vol_20_ann_pct is NULL. The cause is the window size, not missing prices.
	~The LAG() call has no previous row to look back to#LAG only returns NULL for the very first row of each ticker; the last trading day has 500+ prior rows. The vol NULL is caused by the 1-row frame, not LAG.
}

// sf-m1-l1: SQL Window Functions: 20-Day Moving Average & Volatility
::sf-m1-l1 Q2::[plain]What does PARTITION BY ticker inside an OVER() clause do?{
	=Restarts the window calculation for each ticker independently, without collapsing rows#PARTITION BY divides the data into groups and applies the window function within each group - like GROUP BY but keeping every row. AAPL's rolling window never includes MSFT prices.
	~Sorts the results by ticker#Sorting is done with ORDER BY. PARTITION BY divides data into independent windows; it does not order the output.
	~Filters rows to only the specified ticker#Filtering is done with WHERE. PARTITION BY scopes the window per group - it removes no rows.
	~Groups rows by ticker and returns one row per ticker#That is GROUP BY. PARTITION BY inside OVER() keeps all rows; the single-row-per-ticker shape here comes from the WHERE trade_date \= MAX(...) filter, not from PARTITION BY.
}

// sf-m1-l1: SQL Window Functions: 20-Day Moving Average & Volatility
::sf-m1-l1 Q3::[plain]You want the previous day's closing price for each ticker. Which construct do you use?{
	=LAG(close_price, 1) OVER (PARTITION BY ticker ORDER BY trade_date)#LAG() looks back 1 row within each ticker's partition, ordered by date. It returns NULL for the first row of each ticker (no previous day exists) - which is why the first log return per ticker is NULL.
	~LEAD(close_price, 1) OVER (PARTITION BY ticker ORDER BY trade_date)#LEAD() looks forward, giving tomorrow's price, not yesterday's.
	~MIN(close_price) OVER (PARTITION BY ticker ORDER BY trade_date)#MIN() gives the smallest close seen so far, not the specific prior-day value.
	~ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY trade_date) - 1#ROW_NUMBER() returns an integer sequence (1, 2, 3…), not a price. You need LAG() to read the previous row's value.
}

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/CAPM Beta

// sf-m2-l1: Rolling CAPM Beta in SQL
::sf-m2-l1 Q1::[plain]Run as given, the starter returns an empty result set - but it does NOT raise an error. Why is it empty?{
	=The frame ROWS BETWEEN CURRENT ROW AND CURRENT ROW gives each window one row, so Var(SPY)\=0; NULLIF turns every beta into NULL, and WHERE beta_60d IS NOT NULL removes them all#Exactly. With a single observation the denominator n·Σy²−(Σy)² is zero, NULLIF(…,0) yields NULL, and the final filter drops every row - no crash, just an empty table. Widening the frame to 60 rows gives a real variance.
	~AAPL and JPM have no rows in equity_prices, so there is nothing to compute#Both tickers are present with hundreds of daily bars. The emptiness comes from the beta being NULL for every row, not from missing data.
	~The self-join to SPY fails to match any dates#The join on m.trade_date \= s.trade_date matches fine - SPY trades on the same dates. If the join produced nothing, later CTEs would still run; the real cause is the zero-variance denominator.
	~LN(close_price / LAG(...)) is NULL on the first row and that propagates to everything#Only the very first return per ticker is NULL, and it is explicitly filtered by log_ret IS NOT NULL. The remaining rows are fine; the window frame is what makes beta NULL.
}

// sf-m2-l1: Rolling CAPM Beta in SQL
::sf-m2-l1 Q2::[plain]What does ROWS BETWEEN 59 PRECEDING AND CURRENT ROW define for each row of the result?{
	=A trailing window of the current row plus the 59 rows before it - 60 rows within the same ticker partition, ordered by trade_date#Correct. PRECEDING counts rows backwards; CURRENT ROW is included; so the frame holds 60 consecutive daily returns, and it slides forward one row at a time - a rolling 60-day window per ticker.
	~The 60 rows centred on the current row (30 before, 29 after)#That would be a centred window and would use look-ahead data (future returns) - forbidden in a point-in-time risk calc. PRECEDING…CURRENT ROW is strictly backward-looking.
	~All rows from the start of the partition up to the current row (an expanding window)#That is UNBOUNDED PRECEDING AND CURRENT ROW, which grows without bound and gives an all-history beta, not a rolling one. 59 PRECEDING fixes the length at 60.
	~Exactly 59 rows\: the 59 days before today, excluding today#CURRENT ROW is part of the frame, so today is included - that is 60 rows in total, not 59.
}

// sf-m2-l1: Rolling CAPM Beta in SQL
::sf-m2-l1 Q3::[plain]Your desk wants to switch this exact query from a 60-day beta to a 250-day (one-year) beta. What is the minimal change?{
	=Change the frame to ROWS BETWEEN 249 PRECEDING AND CURRENT ROW (and, ideally, rename the alias to beta_250d)#The whole rolling horizon lives in that one number. 249 preceding + the current row \= 250 rows. Everything else - the covariance/variance algebra, the join, the ranking - is unchanged. That reusability is the point of the WINDOW alias.
	~Rewrite the covariance and variance formulas for a longer window#The formula β \= (n·Σxy − Σx·Σy)/(n·Σy² − (Σy)²) is horizon-agnostic - n is computed by COUNT(*) OVER w automatically. Only the frame length changes.
	~Add a second self-join to pull 250 days of SPY history#No extra join is needed; the single SPY self-join already supplies every matched date. The window frame, not the join, controls how many days each beta looks back over.
	~Switch from ROWS to RANGE and pass INTERVAL 250 DAY#INTERVAL 250 DAY counts calendar days (\~180 trading days after weekends/holidays), not 250 trading observations. For a clean daily bar table, ROWS BETWEEN 249 PRECEDING AND CURRENT ROW is the correct, direct change.
}

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/Macro Joins

// sf-m3-l1: Point-in-Time Macro Join
::sf-m3-l1 Q1::[plain]Run the starter as given. It returns a 2022-01 row showing CPI 7 and does NOT raise an error. Why is that row wrong, and why does fixing the join remove it?{
	=The buggy filter (release_date >\= first_date, newest-last) grabs the NEXT release, so January 2022 borrows the Jan-12 print it did not have on Jan 3; the point-in-time filter (release_date <\= first_date) finds no release on or before Jan 3, so cpi_pit is NULL and WHERE cpi_pit IS NOT NULL drops the row#Exactly. On the first January trade date no CPI in the table had been released yet (the earliest print is dated 2022-01-12). Look-ahead reaches forward to borrow it; point-in-time correctly returns NULL and the final filter removes the phantom month.
	~equity_prices has no SPY rows in January 2022, so the row is spurious#SPY has plenty of January 2022 bars - that is why a 2022-01 month exists at all. The row is wrong because of the macro value attached to it, not missing price data.
	~CPI of 7 is simply a data-entry error in macro_indicators#7.0% is the genuine December 2021 CPI print, released by the BLS on 2022-01-12. The problem is not the value; it is that the buggy join hands it to a trade date before it was published.
	~ROUND(cpi_pit, 1) rounds a NULL into 7#ROUND(NULL,1) is NULL, not 7. The 7.5 comes from the look-ahead subquery fetching a future release; rounding is innocent here.
}

// sf-m3-l1: Point-in-Time Macro Join
::sf-m3-l1 Q2::[plain]A colleague says "my join is fine, I match each trade to CPI by calendar month\: ref_period \= SUBSTR(trade_date,1,7)." Why is this still look-ahead bias?{
	=It attaches each month's CPI to EVERY day of that same month, yet that figure is only published in the middle of the NEXT month - so every trade sees a number from the future#Correct. June 2022 CPI (9.1%) describes June but was released on 2022-07-13; matching on ref_period hands it to every June trade. Month-matching backfills it anyway. The point-in-time fix keys on release_date <\= trade_date instead.
	~SUBSTR is not supported in SQLite, so the query errors out#SUBSTR works fine in SQLite (the starter itself uses it for month). The problem is semantic look-ahead, not a syntax error.
	~It is fine - matching by month is the industry-standard point-in-time join#The opposite\: month-matching is the classic anti-pattern. Point-in-time joins key on the release date, never on the period the data describes.
	~It double-counts CPI because two indicators share a month#CPI_YoY and FedFunds are filtered separately by indicator_name, so there is no cross-contamination. The bias comes from ignoring WHEN the value was released.
}

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/RSI & Signals

// sf-m4-l1: RSI-14 in Pure SQL
::sf-m4-l1 Q1::[plain]You run the starter unchanged and get 0 rows. A colleague insists the SQL "looks complete." What is actually wrong?{
	=Both AVGs span the entire history (no rolling frame), so every RSI sits near 50 (Neutral) and nothing crosses the 70/30 thresholds the WHERE clause filters on#Averaging gains and losses over two full years makes RS ≈ 1 and RSI ≈ 50 on every row. The overbought/oversold filter then matches nothing. Adding ROWS BETWEEN 13 PRECEDING AND CURRENT ROW restores the 14-day lookback and the extremes reappear.
	~NULLIF is dividing by zero and silently dropping every row#NULLIF(avg_loss, 0) only yields NULL when avg_loss is exactly 0, which is vanishingly rare over a long window. It is a safety guard, not the cause of an all-empty result.
	~The WHERE ticker \= 'AAPL' filter removed all the data#AAPL has roughly 500+ daily rows in the sample; that filter leaves plenty of data. The emptiness comes from RSI never leaving the Neutral band, not from missing rows.
	~LIMIT 15 is too small to return any rows#LIMIT caps the maximum returned, it never forces zero. If qualifying rows existed you would see up to 15 of them.
}

// sf-m4-l1: RSI-14 in Pure SQL
::sf-m4-l1 Q2::[plain]Why is the window frame written as ROWS BETWEEN 13 PRECEDING AND CURRENT ROW rather than 14 PRECEDING?{
	=The current row is one of the 14, so 13 prior rows + today \= 14 rows total#CURRENT ROW is included in the frame, so you need only 13 rows before it to reach a 14-row lookback. Writing 14 PRECEDING would average 15 rows - an RSI-15.
	~SQL frames are 1-indexed, so 13 actually counts 14 rows automatically#There is no hidden off-by-one. PRECEDING counts exactly the number of rows you state; the extra row is CURRENT ROW, which the frame also includes.
	~Because the first row has no LAG, you must drop one from the count#The missing first LAG is already handled upstream by WHERE price_change IS NOT NULL; it has nothing to do with choosing 13 vs 14 in the frame.
	~RSI is defined over 13 days, not 14#Wilder's RSI uses a 14-period lookback. The 13 in the frame plus the current row is precisely how you express 14 periods with ROWS BETWEEN.
}

// sf-m4-l1: RSI-14 in Pure SQL
::sf-m4-l1 Q3::[plain]For a 14-*bar* indicator like RSI, why use ROWS BETWEEN rather than RANGE BETWEEN in the frame?{
	=ROWS counts a fixed number of physical rows (14 trading bars); RANGE groups by the ORDER BY value and could pull in a different number of rows around ties or gaps#RSI-14 means 14 bars regardless of calendar gaps (weekends, holidays). ROWS gives exactly 14 rows; RANGE operates on value distances and peer-groups equal keys, which is not what a bar-count indicator wants.
	~RANGE is not supported in window functions, so ROWS is the only option#RANGE is valid SQL and is in fact the default frame when you specify ORDER BY without ROWS. It is simply the wrong semantics for a fixed bar count.
	~ROWS is faster because it never reads the ORDER BY column#Both need the ORDER BY to define sequence. The choice is about correctness of the frame size, not a performance shortcut.
	~They are interchangeable here; the result is identical either way#They diverge whenever ordering values tie or have gaps. Defaulting to RANGE is exactly the silent-growing-window bug that makes rolling metrics wrong.
}

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/Multi-CTE Pipelines

// sf-m6-l1: Multi-CTE SQL Pipeline: Portfolio Analytics
::sf-m6-l1 Q1::[plain]You run the starter with the YOUR TURN line still blank. It does NOT error - it returns a single row whose trading_days reads far larger than any one ticker has. What happened?{
	=With no GROUP BY, SQLite treated the whole daily_returns table as one group, so the aggregates ran over every ticker at once and returned one row with an arbitrary ticker attached#Exactly. That is the silent-collapse trap\: the query is legal in SQLite, the aggregates span all tickers, COUNT(*) sums every row, and ticker is filled in arbitrarily. The giveaway is one row instead of five.
	~The LAG window function failed because it needs a GROUP BY to define partitions#LAG uses its own PARTITION BY clause in daily_returns and works fine without any GROUP BY. The missing clause only affects the aggregation in ticker_stats, not the window function.
	~RANK() collapsed the rows because there was nothing to rank#RANK simply numbers whatever rows it receives. It received a single row because ticker_stats already collapsed to one - the cause is upstream, in the missing GROUP BY.
	~The WHERE log_ret IS NOT NULL filter removed all but one ticker#That filter only drops the first NULL return per ticker (there is no prior day to diff against). Every ticker survives it; the single-row result comes from the absent GROUP BY.
}

// sf-m6-l1: Multi-CTE SQL Pipeline: Portfolio Analytics
::sf-m6-l1 Q2::[plain]Why is the per-ticker aggregation placed in its own CTE (ticker_stats) rather than computed in the final SELECT alongside the RANK()?{
	=RANK() must order by avg_return_pct, and a window function cannot see an aggregate computed in the same SELECT - the stats have to be finished in an earlier stage first#Correct. Aggregation and window functions evaluate in different phases; you cannot RANK() over an AVG() produced in the very same SELECT. Materialising the per-ticker stats in ticker_stats first gives ranked a clean column to order by. Separating the stages is the whole point of the pipeline.
	~CTEs run faster than doing the work in one SELECT#CTEs are usually inlined by the optimiser and rarely change performance. The reason here is correctness and clarity - the ordering dependency between aggregation and ranking - not speed.
	~SQLite forbids more than three columns in a single SELECT#There is no such limit. The split into stages is a design choice driven by the aggregate-then-rank dependency, not a column-count rule.
	~Only CTEs are allowed to use ROUND() and STDDEV()#ROUND and STDDEV work in any SELECT. Nothing about those functions requires a CTE; the staging is about the order of aggregation vs ranking.
}

$CATEGORY: $course$/EcoFinLearn/Advanced Finance SQL/Interview Prep

// sf-m7-l1: SQL Interview Questions for Finance Analysts
::sf-m7-l1 Q1::[plain]Three days tie for the worst return of a ticker. What do ROW_NUMBER, RANK and DENSE_RANK assign to those three days and to the next-worst day?{
	=ROW_NUMBER 1, 2, 3, 4; RANK 1, 1, 1, 4; DENSE_RANK 1, 1, 1, 2#ROW_NUMBER always gives distinct numbers. RANK gives ties the same rank and then skips (1, 1, 1, 4). DENSE_RANK gives ties the same rank without gaps (1, 1, 1, 2).
	~All three give 1, 1, 1, 2#Only DENSE_RANK does that. ROW_NUMBER never repeats a number, and RANK skips after a tie.
	~ROW_NUMBER 1, 1, 1, 2; RANK 1, 2, 3, 4; DENSE_RANK 1, 1, 1, 4#This swaps the behaviours. ROW_NUMBER is the one that never ties; RANK is the one that leaves gaps.
	~All three give 1, 2, 3, 4#That is ROW_NUMBER only. RANK and DENSE_RANK both give tied rows the same value.
}

// sf-m7-l1: SQL Interview Questions for Finance Analysts
::sf-m7-l1 Q2::[plain]Why does the top-N query compute worst_rank in a CTE and filter on it in the outer query, rather than writing WHERE ROW_NUMBER() OVER (...) <\= 3 directly?{
	=Window functions are evaluated after WHERE, so they cannot be used in the WHERE of the same query#SQL filters rows with WHERE before it computes windows over them. To filter on a window result, compute it in one query (a CTE or subquery) and filter in the next.
	~CTEs make the query run faster#Performance is not the reason. The direct version is simply not allowed, whatever its speed.
	~ROW_NUMBER only works inside a WITH clause#ROW_NUMBER works in any SELECT list. The restriction is on where its RESULT can be filtered.
	~WHERE cannot compare numbers#WHERE compares numbers all the time. It just cannot see window results, which do not exist yet when it runs.
}

// sf-m7-l1: SQL Interview Questions for Finance Analysts
::sf-m7-l1 Q3::[plain]A ledger for one ticker reads\: BUY 500, SELL 200, SELL 400, BUY 100, in date order. What net_position does the running-total query show on each row?{
	=500, 300, -100, 0#Buys add and sells subtract\: 500, 500 - 200 \= 300, 300 - 400 \= -100 (the book is now short), -100 + 100 \= 0.
	~500, 700, 1100, 1200#That adds every quantity regardless of side. The CASE expression must turn sells into negative quantities.
	~500, 300, 100, 200#Check the third step\: 300 - 400 is -100, not 100. A running position can go below zero.
	~500, -200, -400, 100#Those are the signed quantities of each row, not their running sum. The window frame accumulates them from the first row to the current one.
}