text: -- Challenge 1: Retrieve Product Information
  
  -- 1. Retrieve the name and approximate weight of each product
  SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight
  FROM SalesLT.Product;
  
  -- 2. Retrieve the year and month in which products were first sold
  SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
         YEAR(SellStartDate) as SellStartYear,
         DATENAME(mm, SellStartDate) as SellStartMonth
  FROM SalesLT.Product;
  
  -- 3. Extract product types from product numbers
  SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
         YEAR(SellStartDate) as SellStartYear,
         DATENAME(m, SellStartDate) as SellStartMonth,
         LEFT(ProductNumber, 2) AS ProductType
  FROM SalesLT.Product;
  
  -- 4. Retrieve only products with a numeric size
  SELECT ProductID, UPPER(Name) AS ProductName, ROUND(Weight, 0) AS ApproxWeight,
         YEAR(SellStartDate) as SellStartYear,
         DATENAME(m, SellStartDate) as SellStartMonth,
         LEFT(ProductNumber, 2) AS ProductType
  FROM SalesLT.Product
  WHERE ISNUMERIC(Size) = 1;
  
  
  -- Challenge 2: Rank Customers by Revenue
  
  -- 1. Retrieve companies ranked by sales totals
  SELECT C.CompanyName, SOH.TotalDue AS Revenue,
         RANK() OVER (ORDER BY TotalDue DESC) AS RankByRevenue
  FROM SalesLT.SalesOrderHeader AS SOH
  JOIN SalesLT.Customer AS C
  ON SOH.CustomerID = C.CustomerID;
  
  
  -- Challenge 3: Aggregate Product Sales
  
  -- 1. Retrieve total sales by product
  SELECT P.Name, SUM(SOD.LineTotal) AS TotalRevenue
  FROM SalesLT.SalesOrderDetail AS SOD
  JOIN SalesLT.Product AS P
  ON SOD.ProductID = P.ProductID
  GROUP BY P.Name
  ORDER BY TotalRevenue DESC;
  
  -- 2. Filter the product sales list to include only products that cost over $1,000
  SELECT Name, SUM(LineTotal) AS TotalRevenue
  FROM SalesLT.SalesOrderDetail AS SOD
  JOIN SalesLT.Product AS P
  ON SOD.ProductID = P.ProductID
  WHERE ListPrice > 1000
  GROUP BY P.Name
  ORDER BY TotalRevenue DESC;
  
  -- 3. Filter the product sales groups to include only total sales over $20,000
  SELECT Name, SUM(LineTotal) AS TotalRevenue
  FROM SalesLT.SalesOrderDetail AS SOD
  JOIN SalesLT.Product AS P
  ON SOD.ProductID = P.ProductID
  WHERE P.ListPrice > 1000
  GROUP BY P.Name
  HAVING SUM(LineTotal) > 20000
  ORDER BY TotalRevenue DESC;
  