Skip to content

Assignment two completed #2

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
Sunowlay wants to merge 1 commit into
base: main
Choose a base branch
from
Open

Assignment two completed #2

Show file tree
Hide file tree
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
34 changes: 31 additions & 3 deletions 02_activities/assignments/Assignment2.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -53,9 +53,37 @@ The store wants to keep customer addresses. Propose two architectures for the CU

**HINT:** search type 1 vs type 2 slowly changing dimensions.

```
Your answer...
```
Type 1 – Overwrite (Customer Address)
Table Name: Customer Address
Primary Key: customer_id
Description:
In this model, each customer has a single address record. When a customer’s address changes, the old address is overwritten with the new one.
Use Case:
Suitable when historical address data is not important, and only the most recent address is needed.
Pros:
Simple and efficient.
Saves storage space.
Cons:
No historical tracking of address changes.

Type 2 – Retain History (Customer_Address_History)
Table Name: Customer_Address_History
Primary Key: address_id (surrogate key)
Foreign Key: customer_id
Additional Columns:
start_date
end_date
is_current
Description:
This model retains historical address changes by inserting a new row each time the customer’s address changes. The is_current flag indicates the current active address.
Use Case:
Ideal when it’s important to maintain a full history of customer addresses, such as for auditing or trend analysis.
Pros:
Full historical tracking.
Allows for reporting and analysis over time.
Cons:
More complex queries.
Requires more storage.

***

Expand Down
79 changes: 79 additions & 0 deletions 02_activities/assignments/Logical model A2.drawio
View file
Open in desktop
Copy link
Copy Markdown

@monzchan monzchan Aug 19, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Please indicate the relationship

Original file line number Diff line number Diff line change
@@ -0,0 +1,79 @@
<mxfile host="app.diagrams.net" agent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36" version="28.0.9">
<diagram name="Page-1" id="lITlww-zogel_HXPsuFp">
<mxGraphModel dx="1426" dy="743" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" arrows="1" fold="1" page="1" pageScale="1" pageWidth="827" pageHeight="1169" math="0" shadow="0">
<root>
<mxCell id="0" />
<mxCell id="1" parent="0" />
<mxCell id="C9W4dIHI9Zb4U8phVIaL-2" value="&lt;b&gt;Employee&lt;/b&gt;&lt;div&gt;&lt;br&gt;&lt;/div&gt;&lt;div&gt;Employee_id (PK)&lt;/div&gt;&lt;div&gt;first_name&lt;/div&gt;&lt;div&gt;last_name&lt;/div&gt;&lt;div&gt;email&lt;/div&gt;&lt;div&gt;hire_date&lt;/div&gt;&lt;div&gt;position&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="30" y="60" width="160" height="160" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-3" value="&lt;b&gt;Shift&lt;/b&gt;&lt;div&gt;&lt;br&gt;&lt;/div&gt;&lt;div&gt;shift_id (PK)&lt;/div&gt;&lt;div&gt;employee_id (FK)&lt;/div&gt;&lt;div&gt;shift_date&lt;/div&gt;&lt;div&gt;Shift_type&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="310" y="60" width="160" height="160" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-4" value="&lt;b&gt;Date&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;date-id (PK)&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;date&lt;/b&gt;&lt;/div&gt;&lt;div&gt;day_of_week&lt;/div&gt;&lt;div&gt;month&lt;/div&gt;&lt;div&gt;quarter&lt;/div&gt;&lt;div&gt;year&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="610" y="60" width="160" height="160" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-5" value="&lt;b&gt;Customer&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;customer_id (PK)&lt;/div&gt;&lt;div&gt;first_name&lt;/div&gt;&lt;div&gt;last_name&lt;/div&gt;&lt;div&gt;email&lt;/div&gt;&lt;div&gt;phone&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="30" y="310" width="160" height="160" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-6" value="&lt;b&gt;Order&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;order_id (PK)&lt;/div&gt;&lt;div&gt;customer_id (FK)&lt;/div&gt;&lt;div&gt;order_date&lt;/div&gt;&lt;div&gt;total_amount&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="320" y="300" width="170" height="170" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-7" value="&lt;b&gt;Sales&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;sale_id (PK)&lt;/div&gt;&lt;div&gt;order_id (FK)&lt;/div&gt;&lt;div&gt;book_id (FK)&lt;/div&gt;&lt;div&gt;quantity&lt;/div&gt;&lt;div&gt;unit_price&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="620" y="300" width="170" height="170" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-8" value="&lt;b&gt;Customer Address (Type 1)&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;Customer_id (PK)&lt;br&gt;street&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;city&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;state&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;postal_code&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;country&lt;/b&gt;&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="30" y="550" width="160" height="160" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-9" value="&lt;b&gt;Customer_Address_history (Type 2)&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;address_id (PK)&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;customer_id (FK)&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;street&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;city&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;state&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;postal_code&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;country&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;start_date&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;end_date&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;is_current&lt;/b&gt;&lt;/div&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="320" y="550" width="200" height="200" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-10" value="&lt;b&gt;Book&lt;/b&gt;&lt;div&gt;&lt;b&gt;&lt;br&gt;&lt;/b&gt;&lt;/div&gt;&lt;div&gt;book_id (PK)&lt;/div&gt;&lt;div&gt;title&lt;/div&gt;&lt;div&gt;author&lt;/div&gt;&lt;div&gt;genre&lt;/div&gt;&lt;div&gt;price&lt;/div&gt;&lt;div&gt;publisher&lt;/div&gt;&lt;div&gt;publication_date&lt;/div&gt;" style="whiteSpace=wrap;html=1;aspect=fixed;" vertex="1" parent="1">
<mxGeometry x="630" y="545" width="170" height="170" as="geometry" />
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-12" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="110" y="310" as="sourcePoint" />
<mxPoint x="110" y="240" as="targetPoint" />
</mxGeometry>
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-13" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="111.52" y="470" as="sourcePoint" />
<mxPoint x="112" y="540" as="targetPoint" />
</mxGeometry>
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-14" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="310" y="150" as="sourcePoint" />
<mxPoint x="200" y="150" as="targetPoint" />
</mxGeometry>
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-15" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="470" y="150" as="sourcePoint" />
<mxPoint x="600" y="150" as="targetPoint" />
</mxGeometry>
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-16" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="190" y="389.5" as="sourcePoint" />
<mxPoint x="320" y="389.5" as="targetPoint" />
</mxGeometry>
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-17" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="490" y="389.5" as="sourcePoint" />
<mxPoint x="620" y="389.5" as="targetPoint" />
</mxGeometry>
</mxCell>
<mxCell id="C9W4dIHI9Zb4U8phVIaL-18" value="" style="endArrow=classic;html=1;rounded=0;" edge="1" parent="1">
<mxGeometry width="50" height="50" relative="1" as="geometry">
<mxPoint x="320" y="640" as="sourcePoint" />
<mxPoint x="210" y="640" as="targetPoint" />
</mxGeometry>
</mxCell>
</root>
</mxGraphModel>
</diagram>
</mxfile>
110 changes: 103 additions & 7 deletions 02_activities/assignments/assignment2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -20,7 +20,11 @@ The `||` values concatenate the columns into strings.
Edit the appropriate columns -- you're making two edits -- and the NULL rows will be fixed.
All the other rows will remain the same.) */


SELECT
product_name || ', ' ||
COALESCE(product_size, '') || ' (' ||
COALESCE(product_qty_type, 'unit') || ')'
FROM product;

--Windowed Functions
/* 1. Write a query that selects from the customer_purchases table and numbers each customer’s
Expand All @@ -32,18 +36,53 @@ each new market date for each customer, or select only the unique market dates p
(without purchase details) and number those visits.
HINT: One of these approaches uses ROW_NUMBER() and one uses DENSE_RANK(). */

SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number
FROM customer_purchases;

SELECT
customer_id,
market_date,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
);


/* 2. Reverse the numbering of the query from a part so each customer’s most recent visit is labeled 1,
then write another query that uses this one as a subquery (or temp table) and filters the results to
only the customer’s most recent visit. */


SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS visit_number
FROM customer_purchases;

SELECT *
FROM (
SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS visit_number
FROM customer_purchases
) t
WHERE visit_number = 1;

/* 3. Using a COUNT() window function, include a value along with each row of the
customer_purchases table that indicates how many different times that customer has purchased that product_id. */


SELECT
customer_id,
product_id,
COUNT(*) OVER (PARTITION BY customer_id, product_id) AS product_purchase_count,
market_date,
quantity,
cost_to_customer_per_qty
FROM customer_purchases;

-- String manipulations
/* 1. Some product names in the product table have descriptions like "Jar" or "Organic".
Expand Down Expand Up @@ -73,7 +112,28 @@ HINT: There are a possibly a few ways to do this query, but if you're struggling
3) Query the second temp table twice, once for the best day, once for the worst day,
with a UNION binding them. */


WITH daily_sales AS (
SELECT
market_date,
SUM(quantity * cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date
),
ranked_days AS (
SELECT
market_date,
total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS best_rank,
RANK() OVER (ORDER BY total_sales ASC) AS worst_rank
FROM daily_sales
)
SELECT market_date, total_sales, 'Best Day' AS type
FROM ranked_days
WHERE best_rank = 1
UNION
SELECT market_date, total_sales, 'Worst Day' AS type
FROM ranked_days
WHERE worst_rank = 1;


/* SECTION 3 */
Expand All @@ -89,26 +149,48 @@ 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). */

SELECT
Copy link
Copy Markdown

@monzchan monzchan Aug 19, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

You may consider using DISTINCT query to avoid duplicate rows if customer_id has multiple purchases on the same day

v.vendor_name,
p.product_name,
SUM(5 * vi.original_price) AS total_revenue
FROM vendor_inventory vi
CROSS JOIN customer c
JOIN product p ON vi.product_id = p.product_id
JOIN vendor v ON vi.vendor_id = v.vendor_id
GROUP BY v.vendor_name, p.product_name;


-- 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_category_id, product_qty_type, snapshot_timestamp)
VALUES (999, 'Apple Pie', '500g', 5, 'unit', CURRENT_TIMESTAMP);
Copy link
Copy Markdown

@monzchan monzchan Aug 19, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

here inserts product_id = 999 manually. Could consider auto-increment / calculating MAX(product_id)+1 to avoid conflicts



-- 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 = 999
AND snapshot_timestamp < (
SELECT MAX(snapshot_timestamp)
FROM product_units
WHERE product_id = 999
);


-- UPDATE
Expand All @@ -128,6 +210,20 @@ Finally, make sure you have a WHERE statement to update the right row,
you'll need to use product_units.product_id to refer to the correct row within the product_units table.
When you have all of these components, you can run the update statement. */

ALTER TABLE product_units
ADD current_quantity INT;

UPDATE product_units
SET current_quantity = (
SELECT COALESCE(quantity, 0)
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
ORDER BY vi.inventory_date DESC
LIMIT 1
)
WHERE EXISTS (
SELECT 1 FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
);


1 change: 1 addition & 0 deletions sql
Open in desktop
Submodule sql added at b5eb49