It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Using CTEs with data-modifying statements
13. Nested CTEs with data modifying statements
Summary

Instruction

Fantastic! So far, you have done exercises based on using a single CTE with data-modifying statements. You may also use INSERT, UPDATE, and DELETE statements with nested CTEs.

Imagine you need to raise the prices of all store products by 7 percent, but only if the average selling price of all products in the store is the lowest of all the stores. (Note: Usually, this will apply to just one store, but it's possible that two or more stores will have identical prices and will be the lowest.) Look at the example below.

WITH avg_prices AS (
  SELECT
    grocery_store_id,
    avg(price) AS avg_price
  FROM product
  GROUP BY grocery_store_id
),
min_avg_prices AS (
  SELECT
    MIN(avg_price) AS min_price
  FROM avg_prices
)

UPDATE product
SET price = price + price * 0.07
WHERE grocery_store_id IN (
    SELECT
      grocery_store_id
    FROM avg_prices ap
    JOIN min_avg_prices map
      ON ap.avg_price = map.min_price
  );

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 the averages returned by the first CTE.

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

To obtain the IDs of grocery store(s) with the lowest average prices, we compare grocery_store_ids to the IDs obtained from the subquery. The subquery compares the smallest value from the second CTE and the average value return by the first CTE, getting the grocery_store_id 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(s) with the highest number of points, using the results from the first CTE.

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

Stuck? Here's a hint!

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

In the second CTE, select the records for the grocery stores with the most points. Use the MAX aggregate function with the value obtained from the first CTE.

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