You are working as a Data Engineer for an e-commerce platform. The business team wants to analyze how much revenue each product generates on a daily basis. This will help them identify sales trends and understand product performance over time. In this task, you will calculate the total revenue generated by each product for each day. Revenue is calculated as the product of quantity and price per unit . You will need to group the data correctly to ensure accurate daily aggregation. Schema Summary: orders table: - order_id (INT) - product_id (INT) - order_date (DATE) - quantity (INT) - price (NUMERIC) Requirements: Calculate total revenue for each product per day. Revenue = quantity * price. Group results by product_id and order_date. Return columns: product_id, order_date, total_revenue. Sort the result by order_date ascending, then product_id ascending. Hints: Remember to use the SUM() function to aggregate revenue. A common mistake is forgetting to multiply quantity by price before summing. Also, ensure that all non-aggregated columns in the SELECT clause are included in the GROUP BY clause.

Table Setup (SQL Schema)

CREATE TABLE orders (order_id INT, product_id INT, order_date DATE, quantity INT, price NUMERIC);

INSERT INTO orders (order_id, product_id, order_date, quantity, price) VALUES
(1, 101, '2023-01-01', 2, 50),
(2, 102, '2023-01-01', 1, 100),
(3, 101, '2023-01-01', 1, 50),
(4, 101, '2023-01-02', 3, 50),
(5, 102, '2023-01-02', 2, 100),
(6, 103, '2023-01-02', 1, 200),
(7, 101, '2023-01-03', 1, 50),
(8, 103, '2023-01-03', 2, 200);