Hi there, you perhaps forgot to include your diagram in HW1 submission.

HW1: (reviewed pdf submission) good job, 8/8

HW2: good job
8/8

HW3: good
AGGREGATE Q2 may use LEFT JOIN
8/8

HW4: good, 8/8

HW5: 8/8, good!

INSERT Q2
INSERT INTO product_units (product_id, product_name,
product_size, product_category_id, product_qty_type, snapshot_timestamp)
SELECT
product_id,
product_name,
product_size,
product_category_id,
product_qty_type,
CURRENT_TIMESTAMP
FROM product AS p
WHERE product_id = 7

DELETE Q1
WITH older_record AS
(SELECT product_id
,MIN(snapshot_timestamp) AS snapshot_timestamp
FROM product_units
WHERE product_id = 7)
DELETE FROM product_units
WHERE (product_id =
(SELECT product_id
FROM older_record
)
AND snapshot_timestamp =
(SELECT snapshot_timestamp
FROM older_record
))

UPDATE Q1
ALTER TABLE product_units
ADD current_quantity INT;
UPDATE product_units
SET current_quantity = (
SELECT current_quantity
FROM (
SELECT p.product_id, COALESCE(quantity,0) as current_quantity
FROM product_units p
LEFT JOIN (
SELECT *
,ROW_NUMBER() OVER( PARTITION BY vi.product_id ORDER BY market_date DESC) AS rn
FROM vendor_inventory vi
) vi ON p.product_id = vi.product_id
WHERE rn = 1
OR rn IS NULL
) p
WHERE product_units.product_id = p.product_id);

HW6: 8/8