Customer & Order Analytics Project

#This project for querying was fueled by data from a 'Custumer & Sales' dataset:

#Finding how many many unique orders were placed in January.

SELECT COUNT(DISTINCT orderID) FROM BIT_DB.JanSales
WHERE length(orderID) = 6
AND orderID <> 'Order ID';


#Finding how many of those orders were for an iPhone.

SELECT COUNT(DISTINCT orderID) FROM BIT_DB.JanSales
WHERE length(orderID) = 6
AND orderID <> 'Order ID'
AND Product LIKE '%Iphone%';


#Selecting the customer account numbers for all the orders that were placed in February.

SELECT DISTINCT cus.acctnum, feb.orderdate FROM BIT_DB.customers cus
JOIN BIT_DB.FebSALES feb
ON cus.order_id = feb.orderID
WHERE length(orderID) = 6
AND orderID <> 'Order ID';


#Finding which product was the cheapest one sold in January, and what was the price.

SELECT Product, price FROM BIT_DB.JanSales
WHERE length(orderID) = 6
AND orderID <> 'Order ID'
GROUP BY price
ORDER BY price ASC
LIMIT 1;


#Finding what is the total revenue for each product sold in January.

SELECT SUM(quantity)*price as Revenue, product
FROM BIT_DB.JanSales
GROUP BY product; 


#Finding 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 DISTINCT product, location, SUM(quantity) as TotalSold, SUM(Quantity)*price as revenue 
FROM BIT_DB.FebSales
WHERE location = '548 Lincoln St, Seattle, WA 98101'
GROUP BY Product;


#Finding 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 cus.acctnum) AS TotalCusts, AVG(feb.price * feb.quantity) AS AvgAmountSpent 
FROM BIT_DB.customers as cus JOIN BIT_DB.FebSales as feb 
ON cus.order_id = feb.orderID
WHERE feb.quantity > 2 AND length(feb.orderID) = 6
AND feb.orderID <> 'Order ID'
AND cus.order_id <> 'Order ID';

#Listing all the products sold in Los Angeles in February, and including how many of each were sold.

SELECT location, product, SUM(quantity) as TotalSold
FROM BIT_DB.FebSales
WHERE location LIKE '%Los Angeles%'
GROUP BY product;

#Retrieving which locations in New York received at least 3 orders in January, and how many orders that they each receive.

SELECT location, COUNT(orderID) AS total_quantity
FROM BIT_DB.JanSales
WHERE orderID <> 'Order ID'
AND LENGTH(orderID) = 6
AND location LIKE '%New York%'
GROUP BY location
HAVING COUNT(orderID) >= 3;


#Querying how many of each type of headphone were sold in February.

SELECT product, SUM(quantity) TotalSold FROM BIT_DB.FebSales
WHERE product LIKE '%headphone%'
GROUP BY product;


#Finding what was the average amount spent per account in February.

SELECT SUM(feb.price*feb.quantity)/COUNT(cus.acctnum)
FROM BIT_DB.customers cus RIGHT JOIN BIT_DB.FebSales feb
ON cus.order_id = feb.orderID
WHERE length(feb.orderID) = 6 
AND feb.orderID <> 'Order ID';


#Querying what was the average quantity of products purchased per account in February.

SELECT sum(feb.quantity)/count(cus.acctnum)
FROM BIT_DB.FebSales feb
LEFT JOIN BIT_DB.customers cus
ON feb.orderid=cus.order_id
WHERE length(orderid) = 6 
AND orderid <> 'Order ID';


#Finding which product brought in the most revenue in January and how much revenue did it bring in total.

SELECT product, SUM(quantity) as TotalSold, SUM(Quantity)*price as TotalRevenue 
FROM BIT_DB.JanSales
GROUP BY Product
ORDER BY TotalRevenue DESC
LIMIT 1;