Quiz: CTEs, Subqueries, Derived Tables, Oh My!

Quiz: CTEs, Subqueries, Derived Tables, Oh My!

For each question, choose the most specific correct answer 👍
  • WITH WhatAmI AS (
     SELECT TOP 3
      OrderId,
      SUM(UnitPrice * Quantity) as OrderTotal
      FROM Sales.OrderLines
      GROUP BY OrderId
      ORDER BY OrderTotal DESC
    )

    SELECT c.CustomerName, w.OrderID, w.OrderTotal
    FROM Sales.Orders as o
    JOIN WhatAmI as w on o.OrderID=w.OrderID
    JOIN Sales.Customers as c on o.CustomerID = c.CustomerID
    ORDER BY OrderTotal DESC;
  • SELECT c.CustomerName, WhatAmI.OrderID, WhatAmI.OrderTotal
    FROM Sales.Orders as o
    JOIN (
     SELECT TOP 3
      OrderId,
      SUM(UnitPrice * Quantity) as OrderTotal
     FROM Sales.OrderLines
     GROUP BY OrderId
     ORDER BY OrderTotal DESC
    )   as WhatAmI
    on o.OrderID=WhatAmI.OrderID
    JOIN Sales.Customers as c on o.CustomerID = c.CustomerID
    ORDER BY OrderTotal DESC;
  • WITH something AS (
     SELECT c.CustomerName, ol.OrderId,
      SUM(UnitPrice * Quantity) as OrderTotal,
      DENSE_RANK() OVER(ORDER BY SUM(UnitPrice * Quantity) DESC) AS WhatAmI

     FROM Sales.OrderLines ol
     JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
     GROUP BY ol.OrderId, c.CustomerName
    )
    SELECT s.CustomerName, s.OrderID, s.OrderTotal
    FROM something AS s
    WHERE WhatAmI <= 3
    ORDER BY OrderTotal DESC;
  • WITH something AS (
     SELECT ol.OrderId,
      SUM(UnitPrice * Quantity) as OrderTotal,
      DENSE_RANK() OVER(ORDER BY SUM(UnitPrice * Quantity) DESC) AS other
     FROM Sales.OrderLines ol
     GROUP BY ol.OrderId
    )
    SELECT
    (SELECT c.CustomerName
      from Sales.Customers AS c
      JOIN Sales.Orders as o on c.CustomerID=o.CustomerID
       and o.OrderID=s.OrderID) as WhatAmI,

     s.OrderID,
     s.OrderTotal
    FROM something as s
    WHERE other <= 3
    ORDER BY OrderTotal DESC;