Customer & Order Analytics

#In this SQL, I'm querying a database with multiple tables in it to quantify statistics about customer and order data. 

-- 1. How many orders were placed in January?
SELECT COUNT(orderID)
FROM BIT_DB.JanSales
WHERE length(orderID) = 6
AND orderID <> 'Order ID';

-- 2. How many of those orders were for an iPhone?
SELECT COUNT(orderID)
FROM BIT_DB.JanSales
WHERE length(orderID) = 6
AND orderID <> 'Order ID'
AND Product = 'iPhone';

-- 3. Select the customer account numbers for all the orders that were placed in February.
SELECT distinct acctnum
FROM BIT_DB.customers cust
INNER JOIN BIT_DB.FebSales Feb
ON Feb.orderID = cust.order_id
WHERE length(order_id) = 6
AND order_id <> 'Order ID';

-- 4. Which product was the cheapest one sold in January, and what was the price?

SELECT distinct product, MIN(price)
FROM BIT_DB.JanSales Jan
GROUP BY product, price
ORDER BY price ASC LIMIT 1;
#OR
SELECT distinct Product, price
FROM BIT_DB.JanSales
WHERE price in (SELECT min(price) FROM BIT_DB.JanSales);

-- 5. What is the total revenue for each product sold in January?
SELECT product, ROUND((SUM(Quantity))*price) AS revenue
FROM BIT_DB.JanSales
GROUP BY product
ORDER BY revenue desc;

-- 6. Which products were sold in February at 548 Lincoln St, Seattle, WA 98101, 
-- how many of each were sold, and what was the total revenue?
SELECT Product, SUM(quantity) as total_sold, ROUND((SUM(Quantity))*price,2) AS revenue
FROM BIT_DB.FebSales
WHERE location = '548 Lincoln St, Seattle, WA 98101'
GROUP BY Product;

-- 7. How many customers ordered more than 2 products at a time in February, 
-- and what was the average amount spent for those customers?
SELECT COUNT(distinct cust.acctnum),
ROUND(AVG(quantity*price),2) AS average_spent
FROM BIT_DB.FebSales Feb
LEFT JOIN BIT_DB.customers cust
ON FEB.orderID = cust.order_id
WHERE Feb.Quantity > 2
AND length(orderid) = 6
AND orderid <> 'Order ID';

-- 8. List all the products sold in Los Angeles in February, and include how many of each were sold.
SELECT Product, SUM(Quantity)
FROM BIT_DB.FebSales
WHERE location LIKE '%Los Angeles%'
GROUP BY Product;

-- 9. Which locations in New York received at least 3 orders in January, and how many orders did they each receive? (Hint: use HAVING).
SELECT distinct location, COUNT(orderID)
FROM BIT_DB.JanSales
WHERE location LIKE '%NY%'
AND LENGTH(orderID) = 6
AND orderID <> 'Order ID'
GROUP BY location
HAVING COUNT(orderID) > 2;

-- 10. How many of each type of headphone were sold in February?
SELECT Product, SUM(Quantity) as quantity
FROM BIT_DB.FebSales
WHERE Product LIKE '%headphone%'
AND LENGTH(orderID) = 6
AND orderID <> 'Order ID'
GROUP BY Product;

-- 11. What was the average amount spent per account in February? 
SELECT SUM(Quantity*price)/COUNT(acctnum)
FROM BIT_DB.customers as cust

LEFT JOIN BIT_DB.FebSales as Feb
ON Feb.orderID = cust.order_id

WHERE LENGTH(orderID) = 6
AND orderID <> 'Order ID';

##OR

SELECT avg(quantity*price)
FROM BIT_DB.FebSales Feb

LEFT JOIN BIT_DB.customers cust
ON FEB.orderid=cust.order_id

WHERE length(orderID) = 6 
AND orderID <> 'Order ID';

-- 12. What was the average quantity of products purchased per account in February?
SELECT SUM(Quantity)/COUNT(cust.acctnum)
FROM BIT_DB.customers as cust

LEFT JOIN BIT_DB.FebSales as Feb
ON Feb.orderID = cust.order_id

WHERE length(orderID) = 6
AND orderID <> 'Order ID';

-- 13. Which product brought in the most revenue in January and how much revenue did it bring in total?
SELECT Product, SUM(quantity*price) as revenue
FROM BIT_DB.JanSales
GROUP BY Product
ORDER BY revenue desc
LIMIT 1;