ezzaali47 · ezzaali47 · Aug 17, 2025 · monzchan · Aug 19, 2025 · monzchan
diff --git a/02_activities/assignments/Assignment2.md b/02_activities/assignments/Assignment2.md
@@ -54,7 +54,7 @@ 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...
+If we capture customer addresses with Type 1 SCD, the address is simply overwritten when it changes, easy to manage, but no history is kept. With Type 2 SCD, each new address is stored as a new row with dates, so we keep full history. Type 1 is good if we only care about the current address, while Type 2 is better if we need to track past addresses (e.g., old shipping locations).
 ```
 
 ***

diff --git a/02_activities/assignments/Bookstore Logical Data Model - prompt 1.png b/02_activities/assignments/Bookstore Logical Data Model - prompt 1.png
diff --git a/02_activities/assignments/Bookstore Logical Data Model - prompt 2.png b/02_activities/assignments/Bookstore Logical Data Model - prompt 2.png
diff --git a/02_activities/assignments/assignment2.sql b/02_activities/assignments/assignment2.sql
@@ -1,4 +1,4 @@
-/* ASSIGNMENT 2 */
+/* ASSIGNMENT 2 - By Syeda Ali */
 /* SECTION 2 */
 
 -- COALESCE
@@ -20,7 +20,12 @@ 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 +37,33 @@ 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;
 
 /* 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 *
+FROM (
+    SELECT customer_id,
+           market_date,
+           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS visit_number
+    FROM customer_purchases
+) AS ranked_visits
+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,
+market_date,
+COUNT(*) OVER (PARTITION BY customer_id, product_id) AS product_purchase_count
+FROM customer_purchases;
 
 -- String manipulations
 /* 1. Some product names in the product table have descriptions like "Jar" or "Organic". 
@@ -57,11 +77,17 @@ 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_name,
+TRIM(SUBSTR(product_name, INSTR(product_name, '-') + 1)) AS description
+FROM product;
 
 /* 2. Filter the query to show any product_size value that contain a number with REGEXP. */
 
-
+SELECT product_id,
+product_name,
+product_size
+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 +99,26 @@ 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 sales_by_date AS (
+    SELECT market_date,
+           SUM(quantity * cost_to_customer_per_qty) AS total_sales
+    FROM customer_purchases
+    GROUP BY market_date),
+ranked_sales 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 sales_by_date)
+SELECT market_date, total_sales, 'Best Day' AS label
+FROM ranked_sales
+WHERE best_rank = 1
+
+UNION
+
+SELECT market_date, total_sales, 'Worst Day' AS label
+FROM ranked_sales
+WHERE worst_rank = 1;
 
 /* SECTION 3 */
 
@@ -89,27 +133,57 @@ 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,
+SUM(5 * vi.original_price) AS total_revenue
+FROM vendor_inventory vi
+JOIN vendor v
+ON v.vendor_id = vi.vendor_id
+JOIN product p
+ON p.product_id = vi.product_id
+CROSS JOIN customer 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`. */
 
-
+CREATE TABLE product_units AS
+SELECT p.*,
+CURRENT_TIMESTAMP AS snapshot_timestamp
+FROM product p
+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
+SELECT *,
+CURRENT_TIMESTAMP AS snapshot_timestamp
+FROM product
+WHERE product_name = 'Apple Pie';
 
 -- 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 = (
+    SELECT product_id
+    FROM product
+    WHERE product_name = 'Apple Pie')
+
+AND snapshot_timestamp = (
+    SELECT MIN(snapshot_timestamp)
+    FROM product_units
+    WHERE product_id = (
+        SELECT product_id
+        FROM product
+        WHERE product_name = 'Apple Pie')
+);
 
 -- UPDATE
 /* 1.We want to add the current_quantity to the product_units table.
-Original file line number
+Diff line change
@@ Expand Up @@
     **HINT:** search type 1 vs type 2 slowly changing dimensions.
     ```
-    Your answer...
+    If we capture customer addresses with Type 1 SCD, the address is simply overwritten when it changes, easy to manage, but no history is kept. With Type 2 SCD, each new address is stored as a new row with dates, so we keep full history. Type 1 is good if we only care about the current address, while Type 2 is better if we need to track past addresses (e.g., old shipping locations).
     ```
     ***
@@ Expand Down @@