Question

Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address column from the Customers table A count of the number of orders The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.) Return only those rows where the customer has more than 1 order. Sort the result set in descending sequence by the sum of the line item amounts

Answer 1

The statement is as follows:

Step-by-step explanation:

We had better avoid such correlated subqueries by instead using aggregation with GROUP BY:

SELECT

c.email_address,

COUNT(DISTINCT o.order_id) AS num_orders,

COALESCE(SUM(oi.quantity * (oi.item_price - oi.discount_amount)), 0) AS total_amount

FROM customers c

LEFT JOIN orders o

ON c.customer_id = o.customer_id

INNER JOIN order_items oi

ON o.order_id = oi.order_id

GROUP BY

c.customer_id,

c.email_address;