Question

Write a SELECT statement that uses aggregate window functions to calculate the order total for each customer and the order total for each customer by date. Return these columns: The customer_id column from the Orders table The order_date column from the Orders table The total amount for each order item in the Order_Items table The sum of the order totals for each customer The sum of the order totals for each customer by date (Hint: You can create a peer group to get these values)

Answer 1

The SQL SELECT statement provided calculates order totals using aggregate window functions. It returns the total amount for each customer and for each customer by date by joining the Orders and Order_Items tables and partitioning the sum across appropriate peer groups.

Step-by-step explanation:

To achieve the task of calculating the order total for each customer and the order total for each customer by date using aggregate window functions, you can use the following SQL SELECT statement:

SELECT
o.customer_id,
o.order_date,
SUM(oi.amount) OVER(PARTITION BY o.customer_id) AS customer_total,
SUM(oi.amount) OVER(PARTITION BY o.customer_id, o.order_date) AS customer_daily_total
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_date;

This statement joins the Orders table with the Order_Items table on the order_id, and then calculates two sums using the OVER() clause partitioned by the appropriate columns to create peer groups. 'customer_total' column will hold the sum of the order totals for each customer across all dates, while 'customer_daily_total' will hold the sum of the order totals for each customer by date.

Answer 2

1. SELECT Order.customer_id, SUM(order_total) AS 'Total_order'

FROM Order JOIN Order_item

WHERE Order.customer_id = Order_item.customer_id

GROUP BY Order.customer_id

2. SELECT Order.customer_id, SUM(order_total) AS 'Total_order' , order_date

FROM Order JOIN Order_item

WHERE Order.customer_id = Order_item.customer_id

GROUP BY Order.customer_id, Order_item.order_date

ORDER BY Order_item.order_date

Step-by-step explanation:

Both SQL queries return the total order from the joint order and order_item tables, but the second query returns the total order and their data grouped by customer and order date and also ordered by the order date.