[달리기반] LV.5-1 가장 많이 팔린 품목은?

create table products (
	ProductID int, 
	ProductName varchar(255),
	Category varchar(255),
	Price int
);

create table orders (
	OrderID int,
	ProductID int,
	OrderDate date,
	Quantity int,
	CustomerID int
);

create table customers (
	CustomerID int,
	CustomerName varchar(255),
	Country varchar(255)
);

insert into products (ProductID, ProductName, Category,	Price)
values (1, 'Laptop', 'Electronics', 1000);

insert into products (ProductID, ProductName, Category,	Price)
values (2, 'Smartphone', 'Electronics', 800);

insert into products (ProductID, ProductName, Category,	Price)
values (3, 'Headphones', 'Electronics', 150);

insert into products (ProductID, ProductName, Category,	Price)
values (4, 'Coffee Maker', 'Home', 200);

insert into products (ProductID, ProductName, Category,	Price)
values (5, 'Blender', 'Home', 100);

insert into orders (OrderID, ProductID, OrderDate,	Quantity, CustomerID)
values (101, 1, '2024-02-01', 2, 1);

insert into orders (OrderID, ProductID, OrderDate,	Quantity, CustomerID)
values (102, 3, '2024-02-02', 1, 2);

insert into orders (OrderID, ProductID, OrderDate,	Quantity, CustomerID)
values (103, 2, '2024-02-03', 1, 1);

insert into orders (OrderID, ProductID, OrderDate,	Quantity, CustomerID)
values (104, 4, '2024-02-04', 3, 3);

insert into orders (OrderID, ProductID, OrderDate,	Quantity, CustomerID)
values (105, 1, '2024-02-05', 1, 2);

insert into orders (OrderID, ProductID, OrderDate,	Quantity, CustomerID)
values (106, 5, '2024-02-06', 2, 3);

insert into customers (CustomerID, CustomerName, Country)
values (1, 'Alice', 'USA');

insert into customers (CustomerID, CustomerName, Country)
values (2, 'Bob', 'UK');

insert into customers (CustomerID, CustomerName, Country)
values (3, 'Charlie', 'USA');

select c.customername, sum(b.a) as TotalAmount, sum(b.d) as OrderCount
-- 주문자별, 합산(값), 합산(횟수)
from (
	select o.customerid, p.productid, sum(o.quantity * p.price) as a, sum(o.quantity) as d
-- 		주문자 id, 상품 id / 수량*가격은 a , 수량은 d
	from orders o
	join products p on p.productid = o.productid
	group by o.customerid, p.productid
	) as b
join customers c on c.customerid = b.customerid
group by c.customerid, c.customername;

select p.category, p.productname as Top_product, sum(o.quantity) as Totalsold 
from products p
join orders o on o.productid = p.productid
group by p.category, p.productid, p.productname 
having sum(o.quantity) = (
	select max(total_pq)
	from (
		select p.category, p.productid, sum(o.quantity) as total_pq
		from products p
		join orders o on o.productid = p.productid
		group by p.category, p.productid
	) as sqa
	where sqa.category = p.category 
);