How would you write SQL queries involving ordering and joins to extract the right records under time constraints?
Use the provided orders, order_events, instruments, and fills tables. Return the latest January 2025 order per instrument, keeping only orders whose latest non-null event is FILLED or PARTIALLY_FILLED. Rows with NULL submitted_at or NULL event_time are ignored.
Output
- One row per qualifying instrument and order, with the columns listed below.
- Include the instrument symbol, order ID, latest event type, submission time, filled quantity, average fill price, and latest fill time.
- Sort by submission time descending, instrument symbol ascending, and order ID ascending.
Output details
- Return columns in this exact order:
instrument_symbol, order_id, latest_event_type, submitted_at, filled_quantity, average_fill_price, latest_fill_at.
- Break tied latest orders by
order_id descending within each instrument.
- Break tied latest events by
event_id descending within each order.
- Treat January 2025 as
submitted_at >= TIMESTAMP '2025-01-01 00:00:00' and < TIMESTAMP '2025-02-01 00:00:00'.
- Return NULL fill aggregates when no fills exist, and ignore NULL values in
SUM, AVG, and MAX calculations.
