Extracting Date Components with SQL DATE Functions

Dates are fundamental to many datasets, often requiring us to analyze information based on specific time periods like days, months, or years. This challenge focuses on your ability to extract individual components from a date field using SQL's built-in date functions, a crucial skill for data manipulation and analysis.

Problem Description

Your task is to write a SQL query that extracts the day, month, and year from a given date column. You will be provided with a table containing various data, including a column that stores dates. You need to create a result set that shows the original date alongside its extracted day, month, and year components.

What needs to be achieved: For each record in the input table, identify the date value and then isolate its day, month, and year.

Key requirements:

• Use standard SQL date functions to extract the day.
• Use standard SQL date functions to extract the month.
• Use standard SQL date functions to extract the year.
• The output should include the original date, the extracted day, month, and year.

Expected behavior: The query should process all rows in the input table and return a result set with four columns: the original date, the day component, the month component, and the year component.

Important edge cases to consider:

• Dates in different formats (though for this challenge, we will assume a standard date format).
• Leap years will not affect the extraction of day, month, or year components themselves.

Examples

Example 1:

Input Table: Orders
Columns: order_id (INTEGER), order_date (DATE), amount (DECIMAL)

Sample Data:
| order_id | order_date | amount |
|----------|------------|--------|
| 1        | 2023-10-26 | 150.75 |
| 2        | 2024-01-15 | 75.20  |
| 3        | 2023-12-01 | 220.50 |

Output:
| order_date | day | month | year |
|------------|-----|-------|------|
| 2023-10-26 | 26  | 10    | 2023 |
| 2024-01-15 | 15  | 1     | 2024 |
| 2023-12-01 | 1   | 12    | 2023 |

Explanation: For the first row, 2023-10-26 is processed. The day is 26, the month is 10, and the year is 2023. This pattern is repeated for all rows.

Example 2:

Input Table: Events
Columns: event_id (INTEGER), event_timestamp (TIMESTAMP), event_name (VARCHAR)

Sample Data:
| event_id | event_timestamp      | event_name     |
|----------|----------------------|----------------|
| 101      | 2023-07-04 10:30:00  | Independence Day |
| 102      | 2024-02-29 23:59:59  | Leap Day Event |

Output:
| event_timestamp      | day | month | year |
|----------------------|-----|-------|------|
| 2023-07-04 10:30:00  | 4   | 7     | 2023 |
| 2024-02-29 23:59:59  | 29  | 2     | 2024 |

Explanation: Even though event_timestamp is a timestamp, the date part 2023-07-04 is used to extract day 4, month 7, and year 2023. Similarly for the leap year 2024-02-29.

Constraints

• The input table will contain at least one row.
• The date column will always contain valid dates in a format recognized by standard SQL date functions.
• The query should be performant enough to run on tables with up to 1,000,000 rows without significant delay.

Notes

• You are encouraged to use the most common and widely supported SQL date extraction functions. For instance, functions like EXTRACT(part FROM date) or DATE_PART('part', date) are often available. You might also encounter functions like DAY(date), MONTH(date), and YEAR(date) in specific SQL dialects.
• Ensure your column aliases for the extracted components are clear (e.g., day, month, year).
• Focus on the logic of extracting the components, not on complex date arithmetic or formatting.