SQL Ordering and Joins

MediumSQL · PostgreSQL

Problem

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.

Schema

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
instrument_id	INTEGER	Referenced instrument identifier
trader	VARCHAR(80)	Trader who submitted the order
side	VARCHAR(30)	Order side and position action
submitted_at	TIMESTAMP	Order submission timestamp

order_events

Column	Type	Description
event_idPK	INTEGER	Unique event identifier
order_id	INTEGER	Referenced order identifier
event_type	VARCHAR(40)	Lifecycle event type
event_time	TIMESTAMP	Event timestamp

instruments

Column	Type	Description
instrument_idPK	INTEGER	Unique instrument identifier
instrument_symbol	VARCHAR(40)	Instrument symbol

fills

Column	Type	Description
fill_idPK	INTEGER	Unique fill identifier
order_id	INTEGER	Referenced order identifier
quantity	NUMERIC(14,2)	Executed quantity
price	NUMERIC(14,4)	Execution price
fill_time	TIMESTAMP	Execution timestamp

Tablesordersorder_eventsinstrumentsfills