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 Product = 'iPhone' AND 
       length(orderid) = 6 AND 
       orderid <> 'Order ID';


#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 cust.order_id = FEB.orderid
 WHERE length(orderid) = 6 AND 
       orderid <> 'Order ID';


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

SELECT DISTINCT product,
                price
  FROM BIT_DB.JanSales
 ORDER BY price ASC
 LIMIT 1;

#5. 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;


#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 sum(Quantity),
       product,
       sum(quantity) * price 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, and what was the average amount spent for those customers? 
SELECT count(DISTINCT cust.acctnum),
       avg(quantity * price) 
  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?
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 total_sold
  FROM FebSales
 WHERE Product LIKE '%headphone%'
 GROUP BY Product;

#11 What was the average amount spent per account in February?
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.FebSales Feb
       LEFT JOIN
       BIT_DB.customers cust 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 total_revenue
  FROM JanSales
 GROUP BY Product
 ORDER BY total_revenue DESC
 LIMIT 1;-- Get only the top result