Customer Order Analytics using SQL

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


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


--Select the customer account numbers for all the orders that were placed in February.
SELECT distinct acctnum 
FROM BIT_DB.customers c
INNER JOIN BIT_DB.FebSales fs
ON c.order_id=fs.orderID
WHERE orderID <> ''
AND orderID <> "Order ID";


--Which product was the cheapest one sold in January, and what was the price?
SELECT 
distinct Product
,MIN(price)
FROM BIT_DB.JanSales
GROUP BY Product, price
ORDER BY price ASC LIMIT 1;


--What is the total revenue for each product sold in January? 
--(Revenue can be calculated using the number of products sold and the price of the products).


SELECT SUM(Quantity)*Price AS revenue
, Product
FROM BIT_DB.JanSales
GROUP BY Product
ORDER BY revenue ASC;


--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) 
,SUM(quantity)*price AS Revenue
FROM BIT_DB.FebSales
WHERE location = "548 Lincoln St, Seattle, WA 98101"
GROUP BY product;


--How many customers ordered more than 2 products at a time in February, and what was the average amount spent for those customers?
SELECT 
avg(quantity)*price
,COUNT(distinct cust.acctnum)
FROM BIT_DB.FebSales feb
LEFT INNER JOIN BIT_DB.customers cust
ON feb.orderID = cust.order_id
WHERE feb.quantity >2
AND orderID <> ''
AND orderID <> "Order ID";


--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;


--Which locations in New York received at least 3 orders in January, and how many orders did they each receive?
SELECT distinct location
,COUNT(orderID)
FROM BIT_DB.JanSales
WHERE location like '%New York%'
GROUP BY location
HAVING COUNT(orderID) >2;


--How many of each type of headphone were sold in February?
SELECT product
, SUM(quantity)
FROM BIT_DB.FebSales
WHERE product like '%headphones%'
GROUP BY product;


--What was the average amount spent per account in February?
SELECT SUM(feb.quantity*feb.price)/ COUNT(cust.acctnum) AS avg_spent
FROM BIT_DB.FebSales feb
LEFT INNER JOIN BIT_DB.customers cust
ON feb.orderID = cust.order_id
WHERE orderID <> ''
AND orderID <> "Order ID";


--What was the average quantity of products purchased per account in February?
select SUM(feb.quantity)/COUNT(cust.acctnum)AS avg_quantity
FROM BIT_DB.FebSales feb


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


WHERE orderid <> '' 
AND orderid <> 'Order ID';


--Which product brought in the most revenue in January and how much revenue did it bring in total?
SELECT SUM(quantity)*price as Revenue
, product
FROM BIT_DB.JanSales
Group by product
Order by Revenue desc LIMIT 1;