reearmed · reearmed · May 2, 2025 · May 2, 2025 · May 2, 2025 · May 2, 2025
diff --git a/02_activities/assignments/Assignment2.md b/02_activities/assignments/Assignment2.md
@@ -54,7 +54,9 @@ 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 is the owerwrite method (most recent address remains)
+--Type 2 is the history method (stores all past addresses with timestamps)
+
 ```
 
 ***

diff --git a/02_activities/assignments/Logic model - Sql.png b/02_activities/assignments/Logic model - Sql.png
diff --git a/02_activities/assignments/assignment2.sql b/02_activities/assignments/assignment2.sql
@@ -1,4 +1,9 @@
 /* ASSIGNMENT 2 */
+
+/*SECTION 1 - PROMPT 3 */
+--Type 1 is the owerwrite method (most recent address remains)
+--Type 2 is the history method (stores all past addresses with timestamps)
+
 /* SECTION 2 */
 
 -- COALESCE
@@ -20,6 +25,13 @@ 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, '') || ')'
+FROM product;
+
+SELECT 
+product_name || ', ' || coalesce(product_size, 'unit') || ' (' || coalesce(product_qty_type, 'unit') || ')'
+FROM product;
 
 
 --Windowed Functions
@@ -32,18 +44,43 @@ 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. */
 
-
+--Reverse numbering
+SELECT
+customer_id,
+market_date,
+row_number () OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS reversed_visit_number
+FROM customer_purchases;
+
+--Most recent visit
+SELECT *
+FROM (SELECT
+customer_id,
+market_date,
+row_number() OVER 
+(PARTITION BY customer_id ORDER BY market_date DESC) AS reversed_visit_number FROM customer_purchases) 
+AS recent_visits
+WHERE reversed_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. */
 
-
+--Count Purchases of Product
+SELECT 
+customer_id,
+product_id,
+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 +94,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
+substr(product_name, instr(product_name, ' - ') + 2) AS description
+FROM product
+WHERE instr(product_name, ' - ') > 0;
 
 
 /* 2. Filter the query to show any product_size value that contain a number with REGEXP. */
 
-
+SELECT 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,7 +116,27 @@ 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. */
 
-
+--1) Group Sales by Market Date
+WITH sales_by_date AS
+(SELECT market_date, sum(cost_to_customer_per_qty) AS total_sales
+FROM customer_purchases
+GROUP BY market_date),
+
+--2) Rank Market Dates by Sales
+ranked_dates AS 
+(SELECT market_date, total_sales, 
+rank() OVER(ORDER BY total_sales DESC) AS rank_high,
+rank() OVER(ORDER BY total_sales ASC) AS rank_low
+FROM sales_by_date)
+
+--3) Query Best and Worst Days using UNION
+SELECT market_date, total_sales
+FROM ranked_dates
+WHERE rank_high = 1
+UNION
+SELECT market_date, total_sales
+FROM ranked_dates
+WHERE rank_low = 1;
 
 
 /* SECTION 3 */
@@ -89,27 +152,53 @@ 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).  */
 
+--Cross Join: 
+--1) Vendor Product Pairs
+WITH vendor_products AS
+(SELECT v.vendor_name, p.product_name, vi.original_price
+FROM vendor_inventory vi
+JOIN vendor v ON	vi.vendor_id = v.vendor_id
+JOIN product p ON vi.product_id = p.product_id),
+
+--2) Total Customers
+total_customers AS ( SELECT count(*) AS customer_count FROM customer)
 
+--3) Calculate Total Revenue
+SELECT vp.vendor_name, vp.product_name, tc.customer_count * 5 * vp.original_price AS total_revenue
+FROM vendor_products vp
+CROSS JOIN total_customers tc
+ORDER BY total_revenue DESC;
 
 -- 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 (101, '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 = 101
+AND snapshot_timestamp < (
+SELECT max(snapshot_timestamp) 
+FROM product_units
+WHERE product_id = 101);
 
 -- UPDATE
 /* 1.We want to add the current_quantity to the product_units table. 
@@ -128,6 +217,28 @@ 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. */
 
+--1) ALTER - Add Column
+ALTER TABLE product_units
+ADD current_quantity INT;
 
-
-
+--2) Last Quantity Per Product
+SELECT
+product_id,
+coalesce(quantity, 0) AS last_quantity
+FROM vendor_inventory
+WHERE market_date = (SELECT max(market_date)
+FROM vendor_inventory AS vi_sub
+WHERE vi_sub.product_id = vendor_inventory.product_id);
+
+--3) Update with WHERE statement
+
+UPDATE product_units
+SET current_quantity = (SELECT coalesce(quantity, 0)
+FROM vendor_inventory
+WHERE vendor_inventory.product_id = product_units.product_id
+AND vendor_inventory.market_date = (SELECT max(market_date)
+FROM vendor_inventory
+WHERE vendor_inventory.product_id = product_units.product_id))
+WHERE EXISTS
+(SELECT 1 FROM vendor_inventory
+WHERE vendor_inventory.product_id = product_units.product_id);