/*
Title: SQL Queries by Ira Sharenow 
Author: Ira Sharenow
Creation Date: February 6, 2023

In order to demonstrate a wide variety of SQL query techniques, I created some data in Excel CSV format.
I then read the data into SQL Server using a database named web2023.
I explored T-SQL by writing a large number of queries.
Below I list the tables.
Then I display much of the data.
Then I write more complex queries. Often I display some or all of the output.
Sometimes I solve a problem in more than  one way, such as writing a subquery and also
using window functions.


 The tables
 1. employees
 2. products
 3. customers
 4. orderDetails
 5. orders
 */

 -- Displaying the data

 USE web2023;

-- Query 1A
SELECT *
FROM employees;
/*
empID	firstName	lastName
1	Al	Jones
2	Barb	Smith
3	Carol	Johnson
4	Dave	Adams
*/

-- Query 1B

SELECT *
FROM products;
/*
prodID	product
101	baseball
102	basketball
103	soccer ball
104	football
*/

-- Query 1C
SELECT *
FROM customers;
/*
custid	name	Country
51	Ellen	USA
52	Fran	USA
53	George	USA
54	Helen	USA
55	Ira	USA
56	John	Canada
57	Jack	Canada
58	Kim	Canada
59	James	Mexico
60	Lucy	Mexico
61	Mary	Mexico
*/



-- Query 1D
SELECT *
FROM orderDetails;

/*
Sometimes a customer bought multiple items in the same order
1020 rows of output
orderid	productid	unitprice	qty
1001	101	10.00	9
1002	101	10.00	10
1003	101	10.00	12
1004	101	10.00	15
1005	101	10.00	15
1006	101	10.00	10
1007	101	10.00	14
1008	101	10.00	13
1009	101	10.00	5
1010	101	10.00	14
1001	102	20.00	3
1002	102	20.00	9
*/

-- Query 1E
SELECT *
FROM orders;
/*
1000 rows of output
orderid	custid	empid	orderdate	shipcountry
1001	60	1	2021-04-14	USA
1002	59	1	2020-07-04	USA
1003	52	1	2021-09-30	USA
1004	51	2	2019-09-12	USA
1005	54	4	2019-11-19	USA
1006	60	3	2019-02-10	USA
1007	58	3	2019-10-08	USA
...
1599	56	1	2019-11-21	Canada
1600	56	1	2021-01-07	Canada
1601	55	3	2021-07-28	Mexico
1602	51	4	2021-03-17	Mexico
1603	58	2	2019-06-22	Mexico
*/



-- Query 2
-- Numbers of customers
-- Answer: 11
SELECT COUNT(custid) AS customerCount
FROM customers;

-- Query 3
-- Number of customers who have placed orders
-- Answer: 10
SELECT COUNT(DISTINCT c.custid) AS customerCount
FROM customers AS c
  LEFT JOIN orders AS o
    ON c.custid = o.custid
WHERE o.custid IS NOT NULL;

-- Query 4
-- ID and name of customer who has not yet ordered
-- Answer: Mary
SELECT c.custid, c.name
FROM customers AS c
  LEFT JOIN orders AS o
    ON c.custid = o.custid
WHERE o.custid IS NULL;


-- Query 5
-- Number of orders by year. 
-- Answer: 2019 331
--         2020 315
--         2021 354

SELECT YEAR(orderdate) AS year, COUNT(DISTINCT orderid) as totalSales
FROM orders
GROUP BY YEAR(orderdate);

-- Query 6A
-- Number of orders by year and ship country.
-- Note that there was some missing data.

SELECT shipcountry, YEAR(orderdate) AS year, COUNT(DISTINCT orderid) as totalSales
FROM orders
GROUP BY shipcountry, YEAR(orderdate);
/*
shipcountry	year	totalSales
NULL	2019	2
Canada	2019	32
Mexico	2019	142
USA	2019	155
NULL	2020	1
Canada	2020	32
Mexico	2020	127
USA	2020	155
NULL	2021	2
Canada	2021	36
Mexico	2021	131
USA	2021	185
*/

-- Query 6B
-- Number of orders by year and ship country. Results pivoted so each year has its own column
-- Nulls placed last

WITH first AS
(
SELECT shipcountry, YEAR(orderdate) AS year, COUNT(DISTINCT orderid) as totalSales
FROM orders
GROUP BY shipcountry, YEAR(orderdate)
)
SELECT shipcountry,
  MAX(CASE WHEN year = 2019 THEN totalSales END) AS [2019],
  MAX(CASE WHEN year = 2020 THEN totalSales END) AS [2020],
  MAX(CASE WHEN year = 2021 THEN totalSales END) AS [2021]
FROM first
GROUP BY shipcountry
ORDER BY CASE 
    WHEN shipcountry IS NULL THEN 2 
	ELSE 1 
	END,  
	shipcountry;


-- Query 6C
-- Number of orders by year and ship country. Results pivoted so each year has its own column
-- Using T-SQL pivot operator
-- Nulls placed last

WITH first AS
(
SELECT shipcountry, YEAR(orderdate) AS year, COUNT( orderid) as totalSales
FROM orders
GROUP BY shipcountry, YEAR(orderdate)
)
SELECT shipcountry, [2019], [2020], [2021]
FROM 
   (SELECT shipcountry, year, totalSales
    FROM first) AS D
  PIVOT(MAX(totalSales) FOR year IN ([2019], [2020], [2021])) AS P
ORDER BY CASE 
    WHEN shipcountry IS NULL THEN 2 
	ELSE 1 
	END,  
	shipcountry;

-- Query 6D
-- Number of orders by year and ship country. All 4 groupings in one query
SELECT shipcountry, YEAR(orderdate) AS year, COUNT( orderid) as totalSales
FROM orders
GROUP BY
   GROUPING SETS 
    (
	  ((shipcountry), YEAR(orderdate)),
	  (shipcountry), (YEAR(orderdate)), ()
	);


-- Query 6E
-- Number of orders by year and ship country. All 4 groupings in one query using CUBE syntax
SELECT shipcountry, YEAR(orderdate) AS year, COUNT( orderid) as totalSales
FROM orders
GROUP BY CUBE (shipcountry, YEAR(orderdate));
	  

-- Query 7A
-- Top 5 orders by qty. Break ties by selecting most recent

SELECT TOP(5) od.orderid,	od.productid,	od.unitprice,	od.qty, o.orderDate
FROM orderDetails AS od
  INNER JOIN orders AS o
    ON od.orderid = o.orderid
ORDER BY qty DESC, orderDate DESC;
/*
orderid	productid	unitprice	qty	orderDate
1658	104	40.00	15	2021-12-16
1618	103	30.00	15	2021-11-12
1106	102	20.00	15	2021-11-02
1383	104	40.00	15	2021-10-09
1257	101	10.00	15	2021-10-06
*/

-- Query 7B
-- Top 5 orders by qty. Break ties by selecting most recent. Now add customer and employee

SELECT TOP(5) c.name AS custName, e.firstname AS empFirst, e.lastname AS empLast, 
              od.orderid,	productid,	unitprice,	qty, o.orderDate
FROM orderDetails AS od
  INNER JOIN orders AS o
    ON od.orderid = o.orderid
  INNER JOIN customers AS c
    on o.custid = c.custid
  INNER JOIN employees AS e
    ON o.empid = e.empid
ORDER BY qty DESC, orderDate DESC;

/*
custName	empFirst	empLast	orderid	productid	unitprice	qty	orderDate
Helen	Barb	Smith	1658	104	40.00	15	2021-12-16
John	Al	Jones	1618	103	30.00	15	2021-11-12
Kim	Barb	Smith	1106	102	20.00	15	2021-11-02
James	Al	Jones	1383	104	40.00	15	2021-10-09
Lucy	Al	Jones	1257	101	10.00	15	2021-10-06

*/


-- Query 7C
-- Top 5 orders by qty. Break ties by selecting most recent. Now add customer and employee
-- Alternative: use OFFSETFETCH
SELECT c.name AS custName, e.firstname AS empFirst, e.lastname AS empLast, 
              od.orderid,	productid,	unitprice,	qty, o.orderDate
FROM orderDetails AS od
  INNER JOIN orders AS o
    ON od.orderid = o.orderid
  INNER JOIN customers AS c
    on o.custid = c.custid
  INNER JOIN employees AS e
    ON o.empid = e.empid
ORDER BY qty DESC, orderDate DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

-- Query 7D
-- Top 5 orders by qty. Break ties by selecting most recent. Now add customer and employee
-- Alternative: use Window functions


WITH first AS
(
SELECT c.name AS custName, e.firstname AS empFirst, e.lastname AS empLast, 
              od.orderid,	productid,	unitprice,	qty, o.orderDate,
			  ROW_NUMBER() OVER(ORDER BY qty DESC, orderDate DESC) AS rn
FROM orderDetails AS od
  INNER JOIN orders AS o
    ON od.orderid = o.orderid
  INNER JOIN customers AS c
    on o.custid = c.custid
  INNER JOIN employees AS e
    ON o.empid = e.empid

)
SELECT *
FROM first
WHERE rn <= 5
ORDER BY qty DESC, orderDate DESC;


-- Query 8
-- Skip top 200 orders by qty and now get the orders that rank 201-210. Break ties by selecting most recent. 

SELECT c.name AS custName, e.firstname AS empFirst, e.lastname AS empLast, 
              od.orderid,	productid,	unitprice,	qty, o.orderDate
FROM orderDetails AS od
  INNER JOIN orders AS o
    ON od.orderid = o.orderid
  INNER JOIN customers AS c
    on o.custid = c.custid
  INNER JOIN employees AS e
    ON o.empid = e.empid
ORDER BY qty DESC, orderDate DESC
OFFSET 200 ROWS FETCH NEXT 10 ROWS ONLY;


/*
custName	empFirst	empLast	orderid	productid	unitprice	qty	orderDate
Lucy	Barb	Smith	1700	103	30.00	12	2021-03-01
Kim	Carol	Johnson	1918	103	30.00	12	2021-02-19
Ellen	Carol	Johnson	1079	102	20.00	12	2021-02-18
Lucy	Barb	Smith	1763	102	20.00	12	2021-01-11
Kim	Barb	Smith	1285	101	10.00	12	2021-01-03
Ellen	Barb	Smith	1476	103	30.00	12	2020-12-28
Lucy	Al	Jones	1771	102	20.00	12	2020-12-01
John	Carol	Johnson	1842	102	20.00	12	2020-10-30
Fran	Carol	Johnson	1395	104	40.00	12	2020-10-21
George	Carol	Johnson	1808	104	40.00	12	2020-10-15
*/

-- Query 9
-- GET names of customers with at least 100 orders

 SELECT c.custid, c.name AS custName, COUNT(orderid) AS numorders
 FROM orders AS o
   INNER JOIN customers AS c
     ON o.custid = c.custid
 GROUP BY c.custid, c.name
 HAVING  COUNT(orderid) >= 100
 ORDER BY numorders DESC;

-- Query 10
-- Get basic data on all sales that were 1, 2, 3, 4, 5 days before December 25, 2021
-- case when 1 then 'one day early' etc
SELECT o.orderid, o.custid,	o.empid, o.orderdate, o.shipcountry,
       CASE 
	         WHEN DATEDIFF(day, o.orderdate, '20211225') = 1 THEN '1 day before'
			 WHEN DATEDIFF(day, o.orderdate, '20211225') = 2 THEN '2 days before'
			 WHEN DATEDIFF(day, o.orderdate, '20211225') = 3 THEN '3 days before'
			 WHEN DATEDIFF(day, o.orderdate, '20211225') = 4 THEN '4 days before'
			 WHEN DATEDIFF(day, o.orderdate, '20211225') = 5 THEN '5 days before'
			 END AS daysBefore
FROM orders AS o
WHERE DATEDIFF(day, o.orderdate, '20211225') <= 5 AND  DATEDIFF(day, orderdate, '20211225') > 0
ORDER BY o.orderdate DESC;


-- Query 11
-- orders placed on last days of months in 2021
SELECT o.orderid, o.custid,	o.empid, o.orderdate, o.shipcountry
FROM orders AS o
WHERE o.orderdate = EOMONTH(o.orderdate)
   AND o.orderdate >= '20210101'
   AND o.orderdate < '20220101'
ORDER BY o.orderdate, o.orderid;

-- Query 12
-- Get max order as measured by value. Note a sale may appear in several rows if multiple items were purchased
WITH first AS
(
 SELECT orderid, SUM(unitprice*qty) AS totalSale
 FROM orderDetails
 GROUP BY orderid
 )
 SELECT MAX(totalSale) AS maxSale
 FROM first AS f;


-- Query 13 
-- Get max order for each customer as measured by value.

WITH first AS
(
 SELECT DISTINCT o.custid, c.name AS custName, od.orderid, 
        SUM(unitprice*qty) OVER(PARTITION BY o.custid, od.orderid) AS totalSale,
		ROW_NUMBER() OVER(PARTITION BY o.custid, od.orderid ORDER BY unitprice*qty DESC) AS rnk
 FROM orderDetails AS od
   INNER JOIN orders AS o
   ON od.orderid = o.orderid
   INNER JOIN customers AS c
   ON o.custid = c.custid
WHERE o.custid IS NOT NULL
)
SELECT custid, custName, MAX(totalSale) maxSale
FROM first
WHERE rnk = 1
GROUP BY custid, custName
ORDER BY custid;


-- Query 14 
-- For each customer compute the amount the order is out of the total for that customer
-- Use subquery


SELECT c1.custid, od1.orderid, od1.productid, od1.unitprice*od1.qty AS saleValue,
    CAST(100. * unitprice*od1.qty / (SELECT SUM(od2.unitprice*od2.qty)
                                     FROM orderDetails AS od2 
									    INNER JOIN orders AS o2
										  ON od2.orderid = o2.orderid
										  INNER JOIN customers AS c2
										    ON o2.custid = c2.custid
									 WHERE c2.custid = c1.custid)
									    AS NUMERIC(5,2)) AS pct
FROM orderdetails AS od1
  INNER JOIN orders AS o1
    ON od1.orderid = o1.orderid
	  INNER JOIN customers AS c1
	    ON o1.custid = c1.custid
ORDER BY o1.custid;

-- Query 15A
-- Customers from Mexico who placed orders
-- Recall that Mary is from Mexico but she did not place any orders

SELECT c.name
FROM customers AS c
WHERE c.country = 'Mexico'
  AND c.custid IN
   (SELECT o.custid
    FROM orders AS o
   )

-- Query 15B
-- Using EXIST

SELECT c.name
FROM customers AS c
WHERE c.country = 'Mexico'
  AND EXISTS
   (SELECT *
    FROM orders AS o
	WHERE o.custid = c.custid);
   

/*
name
James
Lucy
*/





-- Query 16A
-- Get the running totals for sales values for all orders when ordered by date with
-- orderid and productID as tie breakers
-- Solution using subqueries
SELECT o.custid, o.orderid, od.productid, o.orderdate, od.unitprice*od.qty AS total,
  (SELECT SUM(od2.unitprice*od2.qty)  
   FROM orderDetails AS od2
     INNER JOIN orders AS o2
   ON od2.orderid = o2.orderid
   WHERE o2.orderdate <= o.orderdate
   ) AS runningTotal
FROM orders AS o
  INNER JOIN orderDetails AS od
    ON o.orderid = od.orderid
ORDER BY o.orderdate, o.orderid, od.productid;

-- Query 16B
-- Solution using window functions 
SELECT o.custid, o.orderid, od.productid, o.orderdate, od.unitprice*od.qty AS total,
  SUM(od.unitprice*od.qty) OVER(ORDER BY o.orderdate, o.orderid, od.productid
      ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runTotal
FROM orders AS o
  INNER JOIN orderDetails AS od
    ON o.orderid = od.orderid
ORDER BY o.orderdate, o.orderid, od.productid;

-- Query 16C
-- Running totals for each customer
-- Solution using window functions 
-- Do not include data where custid is missing
SELECT o.custid, o.orderid, od.productid, o.orderdate, od.unitprice*od.qty AS total,
  SUM(od.unitprice*od.qty) OVER(PARTITION BY o.custid ORDER BY o.orderdate, o.orderid, od.productid
      ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runTotal
FROM orders AS o
  INNER JOIN orderDetails AS od
    ON o.orderid = od.orderid
WHERE o.custid IS NOT NULL
ORDER BY o.custid, o.orderdate, o.orderid, od.productid;


-- Query 17
SELECT DISTINCT o.custid, c.name
FROM orders AS o
  INNER JOIN customers AS c
    ON o.custid = c.custid
WHERE o.custid NOT IN
(
  SELECT o2.custid
  FROM orders AS o2
  WHERE o2.orderdate >= '20211201' 
    AND o2.orderdate <= '20211231' 
)
ORDER BY c.name;
/*
custid	name
58	Kim
*/



-- Query 18
-- Number of days since previous order
SELECT orderdate, empid, custid,orderid,
	   LAG(orderdate)  OVER(PARTITION BY custid
	   ORDER BY orderdate) AS prevorderdate,
	   DATEDIFF(day, LAG(orderdate)  OVER(PARTITION BY custid
	   ORDER BY orderdate), orderdate) AS day_diff
FROM Orders
WHERE custid IS NOT NULL
ORDER BY custid, orderdate;

-- Query 19A
-- Previous and next order using subqueries
-- Please note that subqueries are much slower than window functions
SELECT orderdate, empid, custid,orderid,
  (SELECT MAX(o2.orderid)
   FROM orders AS O2
   WHERE o2.orderid < o1.orderid
     AND o2.custid = o1.custid
	 AND o2.custid IS NOT NULL) AS prevorderid,
  (SELECT MIN(o3.orderid)
   FROM orders AS o3
   WHERE o3.orderid > o1.orderid
     AND o3.custid = o1.custid
	 AND o3.custid IS NOT NULL) AS nextorderid
FROM Orders AS o1
WHERE o1.custid IS NOT NULL
ORDER BY o1.custid, o1.orderid;

-- Query 19B
-- Previous and next order using window functions
SELECT orderdate, empid, custid,orderid,
       LAG(orderid)  OVER(PARTITION BY custid
	   ORDER BY orderid) AS prevorderid,
	   LEAD(orderid)  OVER(PARTITION BY custid
	   ORDER BY orderid) AS nextorderid
FROM Orders
WHERE custid IS NOT NULL
ORDER BY custid, orderid;


-- Query 20
-- Get the employee name for orders missing custid or shipcountry

SELECT o.orderid, e.firstName + ' ' + e.lastName AS employeeName
FROM orders AS o
  INNER JOIN employees AS e
    ON o.empid = e.empID
WHERE o.custid IS NULL
  OR o.shipcountry IS NULL
ORDER BY o.orderid;



-- Query 21
-- The details for every order
-- Full outer join

SELECT o.orderid, o.custid,	o.empid, 
       od.productid,
       o.orderdate, o.shipcountry,
       c.name AS custName, c.Country AS custCountry,
	   e.firstName + ' ' + e.lastName AS empName,
	   p.product, p.price,
	   od.qty
FROM orders AS o
  FULL OUTER JOIN orderDetails AS od
     ON o.orderid = od.orderid
  FULL OUTER JOIN customers AS c
     ON o.custid = c.custid
  FULL OUTER JOIN products AS p
     ON od.productid = p.prodID
  FULL OUTER JOIN employees AS e
     ON o.empid = e.empID;


-- Query 22
-- number of balls sold by type

SELECT p.prodID, p.product, SUM(qty) AS numSold
FROM orderDetails AS od
  INNER JOIN products AS p
    ON od.productid = p.prodID
GROUP BY p.prodID, p.product