SQL Date Filtering & Monthly Returns

Date Filtering & Period Returns 📅

Scene: 8:55am on the trading floor. The PM drops a coffee on your desk: "Pull me AAPL's monthly average price for the last year - I want to see the trend before the 9am risk meeting." You have five minutes. In finance, "give me the numbers" almost always means two things at once: slice to a date window, then roll the daily ticks up into calendar periods (months, quarters, years). Master this and you can answer 80% of ad-hoc desk requests before the coffee goes cold.

🧠 The mental model: the filing cabinet and the anchor

Think of equity_prices as a filing cabinet with one card per trading day. Two moves get you the report:

1. The date filter (WHERE) decides which drawer of cards you pull - here, roughly the last 12 months.
2. GROUP BY strftime decides which folder each card lands in - all the January cards into the "2023-01" folder, then AVG/MIN/MAX summarise each folder into one line.

The subtle part is the window's anchor. A junior analyst hardcodes '2023-01-01'. But this dataset is regenerated every time the page loads, and the newest date drifts with your timezone - so a hardcoded date can silently return zero rows on a colleague's machine. The professional move: anchor the window to the data itself with MAX(trade_date) - "the last 12 months, whatever the newest day happens to be."

Filtering by date range

-- BETWEEN is inclusive on both ends
WHERE trade_date BETWEEN '2023-01-01' AND '2023-12-31'

-- Anchored, timezone-safe: everything on/after (latest date − 12 months)
WHERE trade_date >= DATE((SELECT MAX(trade_date) FROM equity_prices), '-12 months')

DATE(x, '-12 months') is SQLite date arithmetic: it shifts a date backward by a calendar year. Anchoring to MAX(trade_date) means the window slides to always end at the freshest bar - no magic constant to go stale.

Extracting month with strftime

SQLite formats date strings with strftime:

The monthly roll-up pattern

SELECT strftime('%Y-%m', trade_date) AS month,
       AVG(close_price)              AS avg_close,
       MIN(close_price)              AS monthly_low,
       MAX(close_price)              AS monthly_high
FROM   equity_prices
WHERE  ticker = 'AAPL'
  AND  trade_date >= DATE((SELECT MAX(trade_date) FROM equity_prices), '-12 months')
GROUP  BY strftime('%Y-%m', trade_date)
ORDER  BY month;

The golden rule: the expression you GROUP BY must match the one in SELECT. Group by the month string, and each output row is one month.

Career tip: Monthly OHLC roll-ups are the standard feed for fundamental-analysis dashboards. Every Bloomberg (BEQS) or FactSet screen is this exact SQL underneath.

Your Task

Build AAPL's trailing-12-month monthly report: month, average close, monthly low, monthly high - ordered oldest to newest. The query is complete except for the date filter, which is left as a stand-in that pulls the entire history. Replace it with the anchored, MAX(trade_date)-based window so only the last ~12 months survive.

Predict before you run: the full history spans two calendar years (Jan 2022 → Dec 2023). Once you restrict to a trailing 12 months, roughly how many month rows should the result have - 24, or about 13? Run it and check the "rows returned" count against your guess.