Filter orders within a calendar year
Return order ID, amount, and date for orders in calendar year 2024. Dates are stored as fixed-width YYYY-MM-DD text. Return only the requested columns and follow the specified ordering. This question probes a half-open range includes the start and excludes the next year; iso text sorts chronologically. this avoids mysql year versus postgresql extract syntax differences.
Input
Setup script defining customers(id INT, name VARCHAR(30)); orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)).
Output
id, amount, order_date; ascending order_date then id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Ana'),(2,'Ben'),(3,'Cara'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,100,'2024-01-01'),(2,1,200,'2024-02-01'),(3,2,100,'2025-01-01'),(4,NULL,50,'2024-12-31');
Expected result
id | amount | order_date 1 | 100 | 2024-01-01 2 | 200 | 2024-02-01 4 | 50 | 2024-12-31
Another dataset (1)
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Solo'),(2,'None'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,0,'2023-12-31'),(2,NULL,10,'2024-01-01');
Expected result
id | amount | order_date 2 | 10 | 2024-01-01
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Use the start of 2024 as an inclusive lower bound.
- Use the start of 2025 as an exclusive upper bound.
Target complexity: time O(n log n) · space O(n)