Introduction
CTEs with data modifying statements
13. Nested CTEs with data modifying statements
Summary

Instruction

Fantastic! So far, you have done exercises based on CTEs with data modifying statements. You may also use INSERT, UPDATE, and DELETE statements with nested CTEs. Imagine that you need to raise prices of all store products by 7 percent, but only when the products' average selling price is the lowest of all the stores. Look at the example below.

WITH AvgPrices AS (
  SELECT
    GroceryStoreId,
    AVG(Price) AS AvgPrice
  FROM Product
  GROUP BY GroceryStoreId
),
MinAvgPrices AS (
  SELECT
    MIN(AvgPrice) AS MinPrice
  FROM AvgPrices
)

UPDATE Product
SET Price = Price + Price * 0.07
WHERE GroceryStoreId IN (
    SELECT
      GroceryStoreId
    FROM AvgPrices AP
    INNER JOIN MinAvgPrices MAP
    ON AP.AvgPrice = MAP.MinPrice
  );

We constructed two nested CTEs. The first uses store IDs to identify and average product prices for each store. The second CTE selects the minimum average price from those returned by the first CTE.

In the UPDATE statement, we calculate and apply a new product price, increased by 7 percent, to grocery stores whose IDs are identified with the lowest average product price.

To obtain the IDs of grocery stores with the lowest average prices, we compare GroceryStoreIds to the IDs obtained from the subquery. The subquery compares minimal values from the second CTE and average values from the first CTE, getting the GroceryStoreId from the first CTE.

Exercise

Code a storewide (i.e. all-product) price change for the store with the most customer satisfaction points.

In the first CTE, select the sums of customer satisfaction points for all orders for each grocery store. In the second CTE, select the ID of the grocery store with the highest number of points, using the first CTE.

Update the product prices, increasing them by $0.05 only for the grocery store with the highest number of satisfaction points.

Stuck? Here's a hint!

In the first WITH, use the SUM aggregate function to select the sum of all points given to each grocery store.

In the second WITH clause, select only those records for the grocery stores with the highest sum of points. Use the MAX aggregate function with the value obtained from the first CTE.

In the UPDATE statement, use an IN clause with a subquery that selects the IDs of grocery stores from the second CTE.