Conversation
There was a problem hiding this comment.
HW5: 8/8
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);
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
What did you learn from the changes you have made?
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
How were these changes tested?
A reference to a related issue in your repository (if applicable)
Checklist