/*Replace the amount with the average of amount for the 8 rows
where amount is null and status = 'Shipped - Delivered to Buyer'
*/

// Calculate the average values for amount and qty
WITH avg_values AS (
  SELECT
    AVG(amount) AS avg_amount,
    AVG(qty) AS avg_qty
  FROM amazon
  WHERE amount IS NOT NULL -- Exclude rows where amount is NULL
    AND qty > 0 -- Exclude rows where qty is 0
)
// Update the rows with NULL amount and qty
UPDATE amazon_updated AS a
SET
  amount = CASE WHEN a.amount IS NULL THEN avg_amount ELSE a.amount END,
  qty = CASE WHEN a.amount IS NULL THEN avg_qty ELSE a.qty END
FROM avg_values
WHERE
  a.fulfilment = 'Merchant'
  AND a.status = 'Shipped - Delivered to Buyer'
  AND a.amount IS NULL;