abeerkhe · abeerkhe · Aug 21, 2025 · Aug 21, 2025 · Aug 23, 2025 · monzchan
diff --git a/02_activities/assignments/Assignment2.md b/02_activities/assignments/Assignment2.md
@@ -51,6 +51,12 @@ We want to create employee shifts, splitting up the day into morning and evening
 #### Prompt 3
 The store wants to keep customer addresses. Propose two architectures for the CUSTOMER_ADDRESS table, one that will retain changes, and another that will overwrite. Which is type 1, which is type 2? 
 
+The left-hand side table is Type 1 and the right-hand side table is Type 2. 
+
+Type 1 (LHS) contains only individual fields for address and no flag to differentiate multiple address_id values for a single customer_id. Any address updates should replace existing values.  
+
+Conversely, Type 2 (RHS) contains a current_flag field to identify the latest address_id from multilpe address_id values for a single customer_id. Additional helper fields (such as address_decomission_date) help retain a record of when address changes were made and when a specific address may have been used historically. 
+
 **HINT:** search type 1 vs type 2 slowly changing dimensions. 
 
 ```

diff --git a/02_activities/assignments/assignment-two-1.png b/02_activities/assignments/assignment-two-1.png
diff --git a/02_activities/assignments/assignment-two-2.png b/02_activities/assignments/assignment-two-2.png
diff --git a/02_activities/assignments/assignment2.sql b/02_activities/assignments/assignment2.sql
@@ -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') || ')' 
+  AS product_description
+FROM 
+product;
 
 --Windowed Functions
 /* 1. Write a query that selects from the customer_purchases table and numbers each customer’s  
@@ -32,18 +36,61 @@ 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, 
+  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number, 
+  market_date
+FROM
+  customer_purchases
+ORDER BY
+  customer_id, 
+  market_date;
 
 /* 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, visit_number, market_date
+FROM 
+(
+    SELECT
+    customer_id,
+    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS visit_number,
+    market_date
+  FROM
+    customer_purchases
+  ORDER BY
+    customer_id,
+    market_date DESC
+)
+WHERE 
+  visit_number LIKE '1'
+GROUP BY 
+  customer_id, 
+  visit_number; 
 
 /* 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. */
 
+/*The function below adds a "product_purchase_count" value to each row of the customer_purchases table, such
+that each unique purchase shows the total count of how many times that item has been purchased.
+For an aggregate view of product and number of times purchased with one row per product, 
+the SELECt statement would exclude unique purchase identifiers (i.e., datetime and vendor).*/
 
+SELECT 
+  customer_id, 
+  product_id,
+  COUNT() OVER (PARTITION BY customer_id ORDER BY product_id) AS product_purchase_count, 
+  market_date, 
+  vendor_id, 
+  quantity, 
+  cost_to_customer_per_qty, 
+  transaction_time
+FROM 
+  customer_purchases 
+ORDER BY 
+  customer_id, 
+  product_id;
 
 -- String manipulations
 /* 1. Some product names in the product table have descriptions like "Jar" or "Organic". 
@@ -57,11 +104,34 @@ Remove any trailing or leading whitespaces. Don't just use a case statement for
 
 Hint: you might need to use INSTR(product_name,'-') to find the hyphens. INSTR will help split the column. */
 
-
+SELECT product_id, product_name, 
+	CASE 
+	WHEN INSTR(product_name, '-') <> 0
+		THEN REPLACE(SUBSTR(product_name,  INSTR(product_name, '-')+1, LENGTH(product_name) - INSTR(product_name, '-')), ' ', '') 
+	ELSE NULL
+	END product_type, 
+	product_size, 
+	product_category_id, 
+	product_qty_type
+FROM 
+	product
 
 /* 2. Filter the query to show any product_size value that contain a number with REGEXP. */
 
-
+SELECT 
+product_id, 
+product_name, 
+	CASE 
+	WHEN INSTR(product_name, '-') <> 0
+		THEN REPLACE(SUBSTR(product_name,  INSTR(product_name, '-')+1, LENGTH(product_name) - INSTR(product_name, '-')), ' ', '') 
+	ELSE NULL
+	END product_type, 
+	product_size, 
+	product_category_id, 
+	product_qty_type
+
+FROM product
+WHERE product_size REGEXP '[0-9]'
 
 -- UNION
 /* 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.
@@ -73,8 +143,30 @@ 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. */
 
+/* Note: This assumes multiple multiple market days did not tie for highest or lowest total
+sales due to the "LIMIT 1" cuase in the FROM statements. */
 
-
+SELECT 
+	market_date, 
+	total_sales
+FROM (
+	SELECT market_date, SUM(cost_to_customer_per_qty*quantity) AS total_sales
+	FROM customer_purchases
+	GROUP BY market_date 
+	ORDER BY total_sales DESC 
+	LIMIT 1
+)
+UNION 
+SELECT 
+	market_date, 
+	total_sales 
+FROM (
+	SELECT market_date, SUM(cost_to_customer_per_qty*quantity) AS total_sales
+	FROM customer_purchases 
+	GROUP BY market_date 
+	ORDER BY total_sales ASC 
+	LIMIT 1
+)
 
 /* SECTION 3 */
 
@@ -89,27 +181,66 @@ 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 
+	v.vendor_name,
+	p.product_name, 
+	COUNT(c.customer_id) * v_inv.original_price * 5 
+	AS sales_by_product
+FROM 
+	vendor_inventory AS v_inv 
+LEFT JOIN 
+	vendor as v 
+	ON v_inv.vendor_id = v.vendor_id 
+LEFT JOIN 
+	product AS p 
+	ON v_inv.product_id = p.product_id 
+CROSS JOIN 
+	customer AS c 
+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`. */
 
+--Delete table if exists 
+DROP TABLE IF EXISTS product_units;
+
+--Create table, as per question 
+CREATE TABLE product_units AS 
+SELECT 
+	*, 
+	CURRENT_TIMESTAMP  AS snapshot_timestamp
+FROM product
+WHERE product_qty_type LIKE 'unit'; 
 
+--Check to ensure table works properly 
+SELECT * FROM product_units
 
 /*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 ('24', 'Churros', 'Medium', '3', '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.*/
 
+/*I created a new product altogether in the question above, and so created a 
+generalized query below to apply for any product by selecting the oldest record 
+by its product_id.*/
 
+DELETE FROM product_units
+WHERE 
+  product_id IN 
+    (SELECT MIN(snapshot_timestamp)
+    FROM product_units 
+    WHERE product_name='Churros') 
 
 -- UPDATE
 /* 1.We want to add the current_quantity to the product_units table. 
@@ -128,6 +259,11 @@ 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. */
 
-
-
-
+UPDATE product_units 
+SET current_quantity = COALESCE((
+	SELECT v_inv.quantity 
+	FROM vendor_inventory AS v_inv  
+	WHERE v_inv.product_id = product_units.product_id 
+	ORDER BY v_inv.market_date DESC LIMIT 1), 0);
+
+SELECT * FROM product_units