Extract the year and month from the `order_date` column.
Problem Statement
Examples
Input: orders table: +----+------------+-------+ | id | order_date | total | +----+------------+-------+ | 1 | 2024-03-15 | 100 | | 2 | 2024-07-22 | 200 | | 3 | 2023-12-01 | 150 | +----+------------+-------+
Output: +--------------------------+------------+-------------+ | order_date | order_year | order_month | +--------------------------+------------+-------------+ | 2024-03-15T00:00:00.000Z | 2024 | 3 | | 2024-07-22T00:00:00.000Z | 2024 | 7 | | 2023-12-01T00:00:00.000Z | 2023 | 12 | +--------------------------+------------+-------------+
Explanation: The query retrieves the requested records satisfying all problem requirements.
Complexity
Time Complexity: -
Space Complexity: -
Hints
Editorial & Approach
Problem Overview & Intuition
To solve "EXTRACT Date Parts", we query the relational database engine using declarative SQL. The goal is to extract the year and month from the `order_date` column. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.
Step-by-Step Approach
- Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
- Construct Filtering & Logic: Apply row projections to isolate the requested data.
- Format & Order: Ensure columns match the expected project schema in order.
Optimal Implementation (SQL)
SELECT order_date, EXTRACT(YEAR FROM order_date) AS order_year, EXTRACT(MONTH FROM order_date) AS order_month FROM orders;Complexity Analysis
Key Considerations & Edge Cases
- Empty Tables: The query executes safely returning zero rows without syntax error.
- NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
- Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.