Date Manipulation

🎬 Scenario: Analyzing Order and Delivery Dates

Your manager at Amazon, Sophia, needs insights on order processing times and delivery schedules.

“Can you extract key details from order dates and calculate estimated delivery dates?”

Your task is to use date functions like EXTRACT() to pull parts of a date and DATE_ADD() to manipulate delivery timelines. Use the following tables to answer the questions.

amazon_products table:

amazon_orders table:

1. Using EXTRACT() to Retrieve Parts of a Date

The EXTRACT() function retrieves specific components of a date (year, month, day, etc.).

Syntax

1SELECT EXTRACT(part FROM date_column) FROM table_name;

• part can be YEAR, MONTH, DAY, HOUR, etc.

Example Query: Extracting Order Year and Month

1SELECT order_id, order_date,  
2       EXTRACT(YEAR FROM order_date) AS order_year,  
3       EXTRACT(MONTH FROM order_date) AS order_month  
4FROM amazon_orders  
5ORDER BY order_date;

What’s Happening?

• EXTRACT(YEAR FROM order_date) pulls the year from each order date.
• EXTRACT(MONTH FROM order_date) pulls the month.

2. Using DATE_ADD() to Calculate Estimated Delivery Dates

The DATE_ADD() function adds a time interval to a date.

Syntax

1SELECT DATE_ADD(date_column, INTERVAL value unit) FROM table_name;

• value is the number of time units to add.
• unit can be DAY, MONTH, YEAR, etc.

Example Query: Adding Estimated Delivery Days

1SELECT order_id, order_date,  
2       DATE_ADD(order_date, INTERVAL 5 DAY) AS estimated_delivery  
3FROM amazon_orders  
4WHERE status = 'Shipped'  
5ORDER BY order_date;

What’s Happening?

• DATE_ADD(order_date, INTERVAL 5 DAY) adds 5 days to each shipped order’s date to estimate delivery.

✍️ SQL Exercises

Exercise 1: Extracting Order Year and Month

Sophia needs a report breaking down order trends by year and month.

Filter condition:

• Retrieve order ID, order date, order year, and order month.
• Sort by order_date in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Estimating Delivery Dates

Sophia wants to estimate delivery dates by adding 5 days to shipped orders.

Filter condition:

• Retrieve order ID, order date, and estimated delivery date.
• Only include shipped orders.
• Sort by order_date in ascending order.

Write an SQL query to return the requested data.