---------------------------------------------------------------------
-- What’s wrong with my T-SQL code
-- © Itzik Ben-Gan 
---------------------------------------------------------------------

SET NOCOUNT ON;
USE TSQLV6; -- https://itziktsql.com/r-downloads

---------------------------------------------------------------------
-- Task 1
---------------------------------------------------------------------

-- Return employees with last name starting with lowercase letter

-- Suggested solution
SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS LIKE N'[a-z]%';

-- Correct solutions
SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_CS_AS LIKE N'[abcdefghijklmnopqrstuvwxyz]%';

SELECT empid, lastname
FROM HR.Employees
WHERE lastname COLLATE Latin1_General_BIN LIKE N'[a-z]%';

-- Helper queries
-- The first 128 characters of Unicode are the same as the ASCII character set.
-- Following queries can use either CHAR or NCHAR function.
SELECT value, CHAR(value) COLLATE Latin1_General_CS_AS AS mychar
FROM GENERATE_SERIES(0, 127)
ORDER BY mychar;

SELECT value, CHAR(value) COLLATE Latin1_General_BIN AS mychar
FROM GENERATE_SERIES(0, 127)
ORDER BY mychar;

---------------------------------------------------------------------
-- Task 2
---------------------------------------------------------------------

-- Return daily total order values plus running totals

-- Suggested solution
SELECT orderdate, SUM(val) AS dailytotal,
  SUM(val) OVER(ORDER BY orderdate ROWS UNBOUNDED PRECEDING) AS runningtotal
FROM Sales.Ordervalues
GROUP BY orderdate;

-- Correct solution
SELECT orderdate, SUM(val) AS dailytotal,
  SUM(SUM(val)) OVER(ORDER BY orderdate ROWS UNBOUNDED PRECEDING) AS runningtotal
FROM Sales.Ordervalues
GROUP BY orderdate;

---------------------------------------------------------------------
-- Task 3
---------------------------------------------------------------------

-- Return difference between current product price and closest lower product price.
-- Assume $0.00 if no lower product price exists.

-- Suggested solution
SELECT P1.productid, P1.productname, P1.unitprice,
  P1.unitprice - COALESCE( (SELECT MAX(P2.unitprice)
                            FROM Production.Products AS P2
                            WHERE P2.unitprice < P1.unitprice),
                            CAST(0.0 AS MONEY) ) AS diff
FROM Production.Products AS P1;

-- More efficient solution
SELECT P1.productid, P1.productname, P1.unitprice,
  P1.unitprice
    - ISNULL(
        (SELECT MAX(P2.unitprice)
         FROM Production.Products AS P2
         WHERE P2.unitprice < P1.unitprice),
        CAST(0.0 AS MONEY)) AS diff
FROM Production.Products AS P1;

---------------------------------------------------------------------
-- Task 4
---------------------------------------------------------------------

-- Return customers who didn't place any orders

-- Suggested solution
SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN (SELECT custid FROM Sales.Orders);

-- Problem
-- If NULL possible in inner query result, query returns an empty set
-- Add a row to the Orders table with a NULL custid, and rerun above query
INSERT INTO Sales.Orders
  (custid, empid, orderdate, requireddate, shippeddate, shipperid,
   freight, shipname, shipaddress, shipcity, shipregion,
   shippostalcode, shipcountry)
  VALUES(NULL, 1, '20220212', '20220212',
         '20220212', 1, 123.00, N'abc', N'abc', N'abc',
         N'abc', N'abc', N'abc');

-- Correct solution

-- If NULLs are supposed to be supported
-- Option 1, remove NULLs explicitly
SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN (SELECT custid FROM Sales.Orders WHERE custid IS NOT NULL);

-- Option 2, use NOT EXISTS
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid);

-- Cleanup
DELETE FROM Sales.Orders WHERE custid IS NULL;

-- Option 3, define column as NOT NULL if NULLs not allowed
DROP TABLE IF EXISTS Sales.Orders2;
SELECT * INTO Sales.Orders2 FROM Sales.Orders;
ALTER TABLE Sales.Orders2 ALTER COLUMN custid INT NOT NULL;
CREATE INDEX idx_nc_custid ON Sales.Orders2(custid);

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN (SELECT custid FROM Sales.Orders2);

-- Cleanup
DROP TABLE IF EXISTS Sales.Orders2;
DELETE FROM Sales.Orders WHERE custid IS NULL;

---------------------------------------------------------------------
-- Task 5
---------------------------------------------------------------------

-- Write a query that produces 5 unique random values in the range 1 and 69

-- Suggested solution
SELECT TOP (5) N.value
FROM GENERATE_SERIES(1, 69) AS N
ORDER BY RAND();

-- Correct solution
SELECT TOP (5) N.value
FROM GENERATE_SERIES(1, 69) AS N
ORDER BY CHECKSUM(NEWID());

---------------------------------------------------------------------
-- Task 6
---------------------------------------------------------------------

-- Write a query that produces 100 sets of 5 unique random values in the range 1 and 69

-- Suggested solution
SELECT S.value AS setnum, A.value
FROM GENERATE_SERIES(1, 100) AS S
  CROSS APPLY ( SELECT TOP (5) N.value
                FROM GENERATE_SERIES(1, 69) AS N
                ORDER BY CHECKSUM(NEWID()) ) AS A
ORDER BY setnum, value;

-- Correct solution
SELECT S.value AS setnum, A.value
FROM GENERATE_SERIES(1, 100) AS S
  CROSS APPLY ( SELECT TOP (5) N.value
                FROM GENERATE_SERIES(1, 69) AS N
                WHERE S.value > 0
                ORDER BY CHECKSUM(NEWID()) ) AS A
ORDER BY setnum, value;

---------------------------------------------------------------------
-- Task 7
---------------------------------------------------------------------

-- Return yearly order counts

-- Suggested solution
SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);

-- More efficient solution

-- Pre-2022, can create computed column and index it
-- In 2022+, can use DATETRUNC function instead
SELECT YEAR(DATETRUNC(year, orderdate)) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY DATETRUNC(year, orderdate);

---------------------------------------------------------------------
-- Task 8
---------------------------------------------------------------------

-- Return pivoted data with employee IDs on rows, shipper IDs on cols, and count of orders in data

-- Suggested solution
SELECT empid, [1] AS shipper1, [2] AS shipper2, [3] AS shipper3
FROM Sales.Orders
  PIVOT(COUNT(orderid) FOR shipperid IN ([1], [2], [3])) AS P;

-- Correct solution
WITH C AS
(
  SELECT empid, shipperid, orderid FROM Sales.Orders
)
SELECT empid, [1] AS shipper1, [2] AS shipper2, [3] AS shipper3
FROM C
  PIVOT(COUNT(orderid) FOR shipperid IN ([1], [2], [3])) AS P;

---------------------------------------------------------------------
-- Task 9
---------------------------------------------------------------------

-- Return customer, supplier pairs that had joint activity, keeping all customers even if they have no orders.

-- Suggested solution
SELECT DISTINCT C.companyname AS customer, S.companyname AS supplier
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON O.custid = C.custid
  INNER JOIN Sales.OrderDetails AS OD
    ON OD.orderid = O.orderid
  INNER JOIN Production.Products AS P
    ON P.productid = OD.productid
  INNER JOIN Production.Suppliers AS S
    ON S.supplierid = P.supplierid;

-- Correct solution
SELECT DISTINCT C.companyname AS customer, S.companyname AS supplier
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
  INNER JOIN Sales.OrderDetails AS OD
    ON OD.orderid = O.orderid
  INNER JOIN Production.Products AS P
    ON P.productid = OD.productid
  INNER JOIN Production.Suppliers AS S
    ON S.supplierid = P.supplierid
    ON O.custid = C.custid;

---------------------------------------------------------------------
-- Task 10
---------------------------------------------------------------------

-- Assign row numbers to orders just for uniqueness in no specific order. Avoid sorting/ordering penalty.

-- Suggested solution
SELECT orderid, ROW_NUMBER() OVER(ORDER BY 1/0) AS rn
FROM Sales.Orders;

-- For similar reasons, following results in sort: ORDER BY @i + col1 - 1, and following doesn't: ORDER BY @i - 1 + col1