---
# System prepended metadata

title: SQL Challenge for Intermediate Technical Interviewing

---

# SQL Challenge for Intermediate Technical Interviewing

https://www.db-fiddle.com/f/4LnFpNRSMUvTFqie33xFe4/1

```SQL
-- how many products has each customer purchased - total quantity of products purchased per customer

-- how many orders has each customer made?
-- what were in the orders for each customer
-- how many items in total for each order


-- get all the customers

-- select Customer_ID from Customer;

-- get all the orders

-- select * from Orders;

-- how many orders has each customer made?

-- select Customer_ID, count(Customer_ID) 
-- from Orders 
-- GROUP BY Customer_ID;




-- select Order_ID, SUM(Quantity)
-- from Order_Products 
-- GROUP BY Order_ID;

select Customer.Customer_ID, Customer.Name, SUM(Order_Quantity.Quantity) as Quantity from Orders
INNER JOIN Customer ON  Customer.Customer_ID = Orders.Customer_ID
INNER JOIN (select Order_ID as Order_ID, SUM(Quantity) as Quantity from Order_Products GROUP BY Order_ID) as Order_Quantity ON Order_Quantity.Order_ID = Orders.Order_ID
GROUP BY Customer.Customer_ID
ORDER BY Quantity;


-- First get individual pieces of data
-- Write joins between them
-- Write your aggregations and group by statements
-- order / filter the results

SELECT Customer.Name, Orders.Order_ID
FROM Customer
LEFT JOIN Orders on Customer.Customer_ID = Orders.Customer_ID;



-- Find Most Profitable Products
-- which products made us the most profit (not just the difference between cost and price)

-- Find Seasonal Products
-- which products are available in which seasons?
-- Jan-Mar Spring
-- Apr-July Summer
-- Aug-Oct Fall
-- Nov-Dec Winter

```