Skip to content

UofT-DSI | SQL Module – Assignment 2 (Sections 1–3) #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

Merged
val-poon merged 5 commits into from
May 7, 2025
Merged

UofT-DSI | SQL Module – Assignment 2 (Sections 1–3) #2

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
7b29bcc
Add Logical Data Model diagram for Assignment 2
val-poon Apr 28, 2025
da37580
Add original ERD diagram for bookstore (Assignment 2 Section 1)
val-poon May 2, 2025
2711b85
Completed Assignment 2: SQL cross join, insert, update, delete
val-poon May 5, 2025
27fc192
Update farmersmarket.db after completing Assignment 2
val-poon May 5, 2025
1310007
Answering Section 3: Part 1– add X and Y count queries before CROSS JOIN
val-poon May 6, 2025
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
187 changes: 186 additions & 1 deletion 02_activities/assignments/assignment2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -21,6 +21,19 @@ Edit the appropriate columns -- you're making two edits -- and the NULL rows wil
All the other rows will remain the same.) */


-- 1a. Base version (shows NULLs)
SELECT
product_name || ', ' || product_size || ' (' || product_qty_type || ')' AS product_description
FROM product;

-- 1b. Fixed version using COALESCE (handles NULLs gracefully)
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
Expand All @@ -33,17 +46,64 @@ each new market date for each customer, or select only the unique market dates p
HINT: One of these approaches uses ROW_NUMBER() and one uses DENSE_RANK(). */


-- 1. Original Visit Numbering (Oldest = 1)
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. */


-- 2a. Reverse Visit Numbering (Most Recent = 1)

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

-- 2b. Most Recent Visit Only (using subquery)

SELECT *
FROM (
SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS recent_visit_rank
FROM customer_purchases
) AS ranked_visits
WHERE recent_visit_rank = 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. */


-- 3. COUNT of product purchases per customer

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".
Expand All @@ -58,10 +118,27 @@ 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. */


-- STRING MANIPULATION: Extract description after hyphen, from product_name
SELECT
product_name,
CASE
WHEN INSTR(product_name, '-') > 0 THEN
TRIM(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. */


-- STRING MANIPULATION: Filter product_size values with numbers
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.
Expand All @@ -73,7 +150,29 @@ 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. */


-- UNION: Show the market dates with the highest and lowest total sales
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_sales AS (
SELECT *,
RANK() OVER (ORDER BY total_sales DESC) AS sales_rank_high,
RANK() OVER (ORDER BY total_sales ASC) AS sales_rank_low
FROM daily_sales
)
SELECT market_date, total_sales, 'Highest Day' AS label
FROM ranked_sales
WHERE sales_rank_high = 1

UNION

SELECT market_date, total_sales, 'Lowest Day' AS label
FROM ranked_sales
WHERE sales_rank_low = 1;


/* SECTION 3 */
Expand All @@ -88,7 +187,33 @@ Remember, CROSS JOIN will explode your table rows, so CROSS JOIN should likely b
Think a bit about the row counts: how many distinct vendors, product names are there (x)?
How many customers are there (y).
Before your final group by you should have the product of those two queries (x*y). */
-- Step 1: Get the number of customers
-- Step 2: Get distinct vendor-product-price combinations
-- Step 3: Multiply to get total revenue


-- SECTION 3: Answering x and y explicitly before final revenue calc

-- Step 1: Count distinct vendor-product combinations
SELECT COUNT(DISTINCT vi.vendor_id || '-' || vi.product_id) AS vendor_product_combos
FROM vendor_inventory vi;

-- Step 2: Count distinct customers
SELECT COUNT(*) AS customer_count
FROM customer;


-- Step 3 CROSS JOIN: cross join customers with vendor inventory, assume 5 units sold per customer, calculate total revenue
SELECT
v.vendor_name,
p.product_name,
SUM(5 * vi.original_price) AS total_revenue
FROM vendor_inventory vi
CROSS JOIN customer c
JOIN vendor v ON vi.vendor_id = v.vendor_id
JOIN product p ON vi.product_id = p.product_id
GROUP BY v.vendor_name, p.product_name
ORDER BY v.vendor_name, p.product_name;


-- INSERT
Expand All @@ -97,18 +222,57 @@ 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 the product_units table with a timestamp column
DROP TABLE IF EXISTS product_units;

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 a new product row into product_units
INSERT INTO product_units (
product_id,
product_name,
product_size,
product_category_id,
product_qty_type,
snapshot_timestamp
)
VALUES (
999,
'Apple Pie - Extra Sweet',
'10 inch',
1,
'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, Remove older duplicate for 'Apple Pie - Extra Sweet'
DELETE FROM product_units
WHERE product_name = 'Apple Pie - Extra Sweet'
AND snapshot_timestamp < (
SELECT MAX(snapshot_timestamp)
FROM product_units
WHERE product_name = 'Apple Pie - Extra Sweet'
);

-- DELETE fallback: Remove a specific older copy if needed
DELETE FROM product_units
WHERE product_name = 'Apple Pie - Extra Sweet'
AND product_id != 999;


-- UPDATE
Expand All @@ -129,5 +293,26 @@ Finally, make sure you have a WHERE statement to update the right row,
When you have all of these components, you can run the update statement. */


-- Add new column to track latest quantity
ALTER TABLE product_units
ADD current_quantity INT;

-- UPDATE: Set current_quantity from the latest vendor_inventory entry
UPDATE product_units
SET current_quantity = (
SELECT COALESCE(quantity, 0)
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
ORDER BY market_date DESC
LIMIT 1
)
WHERE EXISTS (
SELECT 1
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
);





Binary file added 02_activities/assignments/assignment_2_logical_model_OG_valeriepoon.png
View file
Open in desktop
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Binary file added 02_activities/assignments/assignment_2_logical_model_valeriepoon.png
View file
Open in desktop
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Binary file modified 05_src/sql/farmersmarket.db
View file
Open in desktop
Binary file not shown.