Jesscode-analyst · Jesscode-analyst · Aug 15, 2025
diff --git a/02_activities/assignment 2 part 1 .jpg b/02_activities/assignment 2 part 1 .jpg
diff --git a/02_activities/assignments/assignment 2 part 1 .jpg b/02_activities/assignments/assignment 2 part 1 .jpg
diff --git a/02_activities/assignments/assignment2.sql b/02_activities/assignments/assignment2.sql
@@ -19,6 +19,13 @@ HINT: keep the syntax the same, but edited the correct components with the strin
 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_display
+FROM product;
 
 
 
@@ -32,17 +39,54 @@ 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(). */
 
+DENSE_RANK
+SELECT *,
+	DENSE_RANK()OVER (PARTITION BY customer_id ORDER BY market_date) AS customer_visit_number
+FROM customer_purchases
+ORDER BY customer_id, market_date
 
+ROW_NUMBER
+SELECT *,
+ROW_NUMBER ()OVER (PARTITION BY customer_id ORDER BY market_date) AS customer_visits
+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. */
 
-
-
+DENSE_RANK
+SELECT *
+FROM (
+    SELECT *,
+           DENSE_RANK() OVER (
+               PARTITION BY customer_id
+               ORDER BY market_date DESC
+           ) AS customer_visit
+    FROM customer_purchases
+) AS recent_visit
+WHERE customer_visit = 1
+ORDER BY customer_id, market_date;
+
+ROW_NUMBER
+SELECT *
+FROM (
+    SELECT *,
+           ROW_NUMBER() OVER (
+               PARTITION BY customer_id
+               ORDER BY market_date DESC
+           ) AS customer_visit
+    FROM customer_purchases
+) AS recent_visit
+WHERE customer_visit = 1
+ORDER BY customer_id, market_date;
 /* 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 *,
+	COUNT(*)OVER (PARTITION BY product_id ORDER BY customer_id) AS products_ordered_count
+FROM customer_purchases
+ORDER BY customer_id, product_id, market_date;
 
 
 -- String manipulations
@@ -57,11 +101,19 @@ 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 *,
+    CASE 
+        WHEN INSTR(product_name, '-') > 0
+        THEN LTRIM(RTRIM(SUBSTR(product_name, INSTR(product_name, '-') + 1)))
+        ELSE NULL END AS description
+FROM product;
 
 
 /* 2. Filter the query to show any product_size value that contain a number with REGEXP. */
 
-
+SELECT *
+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,6 +125,31 @@ 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. */
 
+-- Step 1: 
+WITH Sales_per_date AS (
+    SELECT 
+        market_date, 
+        SUM(quantity * cost_to_customer_per_qty) AS total_sales
+    FROM customer_purchases
+    GROUP BY market_date
+),
+-- Step 2: 
+Ranked_Sales AS (
+    SELECT 
+        market_date,
+        total_sales,
+        RANK() OVER(ORDER BY total_sales DESC) AS rank_desc,  
+        RANK() OVER(ORDER BY total_sales ASC) AS rank_asc     
+    FROM Sales_per_date
+)
+-- Step 3: 
+SELECT market_date, total_sales
+FROM Ranked_Sales
+WHERE rank_desc = 1   -- highest total sales
+UNION
+SELECT market_date, total_sales
+FROM Ranked_Sales
+WHERE rank_asc = 1    -- lowest total sales;
 
 
 
@@ -89,6 +166,17 @@ 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 vendor_name, product_name, sum(price) 
+FROM ( 
+	SELECT DISTINCT vendor_name, 
+	product_name, 
+	5 * original_price as price 
+FROM vendor_inventory vi
+JOIN vendor v on v.vendor_id = vi.vendor_id 
+JOIN product p on p.product_id = vi.product_id ) x 
+CROSS JOIN 
+	(SELECT DISTINCT customer_id FROM customer) 
+GROUP BY vendor_name, product_name
 
 
 -- INSERT
@@ -97,18 +185,48 @@ 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_qty_type, 
+	product_size, 
+	snapshot_timestamp 
+) 
+
+VALUES 
+( 
+	101, 
+	'Pumpkin Pie', 
+	'unit', 
+	'Medium', 
+	 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 rowid IN (
+    SELECT rowid
+    FROM product_units
+    WHERE product_name = 'Pumpkin Pie'
+    ORDER BY snapshot_timestamp ASC
+    LIMIT 1
+);
 
 
 -- UPDATE
@@ -128,6 +246,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. */
 
-
+-- Step 1: Add the column
+ALTER TABLE product_units ADD COLUMN current_quantity INT;
+
+-- Step 2: Update current_quantity with latest quantity from vendor_inventory
+UPDATE product_units
+SET current_quantity = (
+    SELECT COALESCE(vi.quantity, 0)
+    FROM vendor_inventory vi
+    WHERE vi.product_id = product_units.product_id
+      AND vi.market_date = (
+          SELECT MAX(market_date)
+          FROM vendor_inventory
+          WHERE product_id = vi.product_id
+      )
+);