/*
You are managing an e-commerce platform that tracks customer orders. One of the critical tasks is monitoring the processing times for orders that are still pending.
To ensure timely delivery and customer satisfaction, you need to analyze the time elapsed since each pending order was placed

Schema:
| Column Name      | Data Type     | Description                             |  
|------------------|---------------|-----------------------------------------|  
| order_id         | INT           | Unique ID for each order                |  
| order_date       | DATETIME      | Date and time when the order was placed |  
| order_status     | VARCHAR(20)   | Current status of the order             |  

Task:

Write a query to:

1. Display the order_id and order_date for all pending orders.
2. Display the current date and time.
3. Compute the number of hours that have passed since the order_date.
4. Display the following columns:
    • order_id: Unique identifier for the order.
    • order_date: Date and time when the order was placed.
    • current_time: The current date and time.
    • hours_since_order: Total hours elapsed since the order was placed.

This will help the manager identify delayed orders and take appropriate actions to expedite processing.

Expected Output:
| order_id | order_date          | current_time        | hours_since_order |  
|----------|---------------------|---------------------|-------------------|  
| 1        | 2024-12-25 10:00:00 | 2024-12-28 05:45:02 | 67                |  
| 3        | 2024-12-23 14:45:00 | 2024-12-28 05:45:02 | 111               |  

Note: The Expected Output can vary due to the change in time!!
*/

CREATE TABLE orders (
    order_id INT,
    order_date DATETIME,
    order_status VARCHAR(20)
);

INSERT INTO orders (order_id, order_date, order_status) VALUES
(1, '2024-12-25 10:00:00', 'Pending'),
(2, '2024-12-24 08:30:00', 'Completed'),
(3, '2024-12-23 14:45:00', 'Pending'),
(4, '2024-12-25 15:00:00', 'Shipped');

SELECT 
    order_id, 
    order_date, 
    CURRENT_TIMESTAMP AS 'current_time', 
    TIMESTAMPDIFF(HOUR, order_date, CURRENT_TIMESTAMP) AS hours_since_order 
FROM orders 
WHERE order_status = 'Pending';