Skip to content

Update homework_5.sql #5

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
keyuancy wants to merge 1 commit into
base: main
Choose a base branch
from
Open

Update homework_5.sql #5

Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
41 changes: 41 additions & 0 deletions 02_activities/homework/homework_5.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -9,36 +9,77 @@ Think a bit about the row counts: how many distinct vendors, product names are t
How many customers are there (y).
Before your final group by you should have the product of those two queries (x*y). */

WITH customer_count AS (
SELECT COUNT(*) AS total_customers FROM customer
)
SELECT
v.vendor_name,
p.product_name,
(5 * vi.cost_per_unit * cc.total_customers) AS total_revenue_per_product
FROM vendor_inventory vi
JOIN vendor v ON vi.vendor_id = v.vendor_id
JOIN product p ON vi.product_id = p.product_id
CROSS JOIN customer_count cc;


-- INSERT
/*1. Create a new table "product_units".
This table will contain only products where the `product_qty_type = 'unit'`.
It should use all of the columns from the product table, as well as a new column for the `CURRENT_TIMESTAMP`.
Name the timestamp column `snapshot_timestamp`. */
CREATE TABLE product_units AS
SELECT *,
CURRENT_TIMESTAMP AS snapshot_timestamp
FROM product
WHERE product_qty_type = 'unit';



/*2. Using `INSERT`, add a new row to the product_units table (with an updated timestamp).
This can be any product you desire (e.g. add another record for Apple Pie). */
INSERT INTO product_units (product_id, product_name, product_size, product_qty_type, snapshot_timestamp)
VALUES (123, 'Apple Pie', 'Large', 'unit', CURRENT_TIMESTAMP);



-- DELETE
/* 1. Delete the older record for the whatever product you added.

HINT: If you don't specify a WHERE clause, you are going to have a bad time.*/
DELETE FROM product_units
WHERE product_id = 123
AND snapshot_timestamp < (SELECT MAX(snapshot_timestamp) FROM product_units WHERE product_id = 123);



-- UPDATE
/* 1.We want to add the current_quantity to the product_units table.
First, add a new column, current_quantity to the table using the following syntax.
ALTER TABLE product_units
ADD current_quantity INT;

ALTER TABLE product_units
ADD current_quantity INT;

Then, using UPDATE, change the current_quantity equal to the last quantity value from the vendor_inventory details.
WITH latest_quantity AS (
SELECT
product_id,
COALESCE(MAX(vi.quantity), 0) AS last_quantity
FROM vendor_inventory vi
GROUP BY product_id
)
UPDATE product_units pu
SET current_quantity = (
SELECT lq.last_quantity
FROM latest_quantity lq
WHERE lq.product_id = pu.product_id
)
WHERE EXISTS (
SELECT 1
FROM latest_quantity lq
WHERE lq.product_id = pu.product_id
);

HINT: This one is pretty hard.
First, determine how to get the "last" quantity per product.
Expand Down