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