At LSEG, a software engineer is reviewing a query that a teammate wrote to summarize the previous day's trading activity per instrument. The relevant tables are trades (trade_id, instrument_id, trade_ts, quantity, price, venue), instruments (instrument_id, symbol, currency), and corporate_actions (action_id, instrument_id, action_type, effective_date), where an instrument can have zero, one, or several corporate actions on record.
SELECT i.symbol,
DATE(t.trade_ts) AS trade_date,
SUM(t.quantity) AS total_volume,
AVG(t.price) AS avg_price
FROM trades t
JOIN instruments i ON i.instrument_id = t.instrument_id
LEFT JOIN corporate_actions ca ON ca.instrument_id = t.instrument_id
WHERE t.trade_ts >= CURRENT_DATE - 1
GROUP BY i.symbol, DATE(t.trade_ts)
ORDER BY total_volume DESC;
Walk through what this query does, and explain how you would verify whether its output is actually correct.
Output
- Return one row per instrument symbol and previous calendar date, with
symbol, trade_date, total_volume, and avg_price.
- Include only trades from the previous calendar day, exclude instruments without a matching instrument record, and order by
total_volume descending.
