Answer:
SELECT email_address,
SUM(item_price * Quantity) AS item_price_total,
SUM(discount_amount * Quantity) AS discount_amount_total
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Order_Items oi ON o.OrderID = oi.OrderID
GROUP BY email_address
ORDER BY item_price_total DESC
Explanation:
In this SQL statement the SELECT statement selects the following columns:
email_address
item_price
Quantity
discount_amount
There are two tables Customers and Order_Items
SUM aggregate function is used to add the values of the product of the columns discount_amount and Quantity. It is also used to get the sum of the product of two columns discount_amount and Quantity.
A new temporary column named item_price_total is used to name the sum of the product of two columns discount_amount and Quantity using AS which is ALIAS and it is used to give a name to some columns or a table.
Similarly discount_amount_total name is given to the column which calculate the sum of the product of two columns i.e. discount_amount and Quantity.
JOIN is used here to join the columns from the tables Order_items and Customers.
GROUP BY is used to group the result of rows and is used with functions like SUM. Here the rows are grouped by the email address.
ORDER BY is used to order the result. Here the result is ordered by item_price_total in descending order.
This SELECT statement can also be written as following:
SELECT c.email_address,
SUM(o.item_price * o.Quantity),
SUM(o.discount_amount * o.quantity)
FROM customer c
JOIN Order_Items o ON o.id = c.id
GROUP BY c.email_address
