Question

List each category, the name of the customer who has spent the most on items in that category, and the total amount of money that customer spent in that category.please give me an sql query for this question and i have tables as follows customer(customer_name,address,zipcode,customer_id,state),office_supplies(item_name,item_id,cost,category),orders(order_id,date,customer_id) order_details(quantity,order_id,item_id)

Answer 1

List each category, the name of the customer who has spent the most on items in that category, and the total amount of money that customer spent in that category.please give me an sql query for this question and i have tables as follows

customer(customer_name,address,zipcode,customer_id,state),office_supplies(item_name,item_id,cost,category),

orders(order_id,date,customer_id) order_details(quantity,order_id,item_id)

Query

select category, customer_name, cost * quantity as total_amount
from office_supplies
inner join customer on orders.customer_id = customer.customer_id
inner join order_details on order_details.item_id = office_supplies.item_id
group by category
having max(cost);

Explanation:

inner join -> retrieves data by combining more than one table using the primary key and foreign key from more than tables

group by - sort the table by column given in it

having - get data from the table with the condition given in the having.