Skip to content

Complete Assignment 2: Logical Model and Advanced SQL #3

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

Open
andreero0 wants to merge 1 commit into
base: main
Choose a base branch
from
Open

Complete Assignment 2: Logical Model and Advanced SQL #3

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
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
Binary file added 02_activities/assignments/Assignment 2a_logical model - Eromosele Ikhalo.pdf
View file
Open in desktop
Binary file not shown.
Binary file added 02_activities/assignments/Assignment 2b_logical model - Eromosele Ikhalo.pdf
View file
Open in desktop
Binary file not shown.
27 changes: 27 additions & 0 deletions 02_activities/assignments/Assignment2.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -55,6 +55,33 @@ The store wants to keep customer addresses. Propose two architectures for the CU

```
Your answer...
Two architectures for customer_address:

1. Type 2 SCD (Retaining Changes):
customer_address_id | customer_id | address_line | city | state | postal_code | valid_from | valid_to | is_current
-------------------|-------------|--------------|------|-------|-------------|------------|----------|------------
1 | 100 | 123 Main St | Toronto | ON | M5V 2T6 | 2023-01-01 | 2023-06-30 | 0
2 | 100 | 456 Queen St | Toronto | ON | M5H 2N2 | 2023-07-01 | NULL | 1

In this Type 2 Slowly Changing Dimension approach:
- Each address change creates a new row
- Historical address information is preserved
- `valid_from` and `valid_to` track the time period of each address
- `is_current` flag indicates the most recent address
- Allows tracking of customer address changes over time

2. Type 1 SCD (Overwriting Changes):
customer_id | address_line | city | state | postal_code
------------|--------------|------|-------|-------------
100 | 456 Queen St | Toronto | ON | M5H 2N2

In this Type 1 Slowly Changing Dimension approach:
- Only the most recent address is stored
- Previous address information is completely overwritten
- No historical tracking of address changes
- Simpler design that always reflects the current state

The key difference is that Type 2 preserves historical data, while Type 1 always reflects the current address by overwriting previous information.
```

***
Expand Down
136 changes: 131 additions & 5 deletions 02_activities/assignments/assignment2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -20,7 +20,15 @@ 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.) */

--Replace NULL with a Blank (''):
SELECT
product_name || ', ' || COALESCE(product_size, '') || ' (' || COALESCE(product_qty_type, '') || ')' AS product_details
FROM product;

--Replace NULL with 'unit':
SELECT
product_name || ', ' || COALESCE(product_size, '') || ' (' || COALESCE(product_qty_type, 'unit') || ')' AS product_details
FROM product;

--Windowed Functions
/* 1. Write a query that selects from the customer_purchases table and numbers each customer’s
Expand All @@ -32,18 +40,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,
DENSE_RANK() 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 customer_id,
market_date
FROM (
SELECT customer_id,
market_date,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS visit_number
FROM customer_purchases
) sub
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 purchase_count
FROM customer_purchases;

-- String manipulations
/* 1. Some product names in the product table have descriptions like "Jar" or "Organic".
Expand All @@ -57,11 +90,16 @@ 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
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.
Expand All @@ -73,7 +111,35 @@ 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: Calculate Total Sales per Market Date
DROP TABLE IF EXISTS sales_by_date;

CREATE TEMP TABLE sales_by_date AS
SELECT market_date,
SUM(quantity * cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date;

-- 2: Rank Market Dates by Total Sales
DROP TABLE IF EXISTS ranked_sales;

CREATE TEMP TABLE ranked_sales AS
SELECT market_date,
total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS sales_rank_desc,
RANK() OVER (ORDER BY total_sales ASC) AS sales_rank_asc
FROM sales_by_date;

-- 3: Extract Best and Worst Sales Days
SELECT market_date, total_sales, 'Highest Sales' AS sales_type
FROM ranked_sales
WHERE sales_rank_desc = 1

UNION

SELECT market_date, total_sales, 'Lowest Sales' AS sales_type
FROM ranked_sales
WHERE sales_rank_asc = 1;


/* SECTION 3 */
Expand All @@ -89,6 +155,18 @@ 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,
5 * c.customer_count * vi.original_price AS total_revenue
FROM vendor_inventory vi
JOIN vendor v
ON vi.vendor_id = v.vendor_id
JOIN product p
ON vi.product_id = p.product_id
CROSS JOIN (
SELECT COUNT(*) AS customer_count
FROM customer
) c;


-- INSERT
Expand All @@ -97,18 +175,40 @@ 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';

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_qty_type, snapshot_timestamp)
VALUES (110, 'Banana Bread', 'Large', 'unit', CURRENT_TIMESTAMP);

SELECT * FROM product_units
WHERE product_id = 110;


-- 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_name = 'Banana Bread'
AND snapshot_timestamp < (
SELECT MAX(snapshot_timestamp)
FROM product_units
WHERE product_name = 'Banana Bread'
);

SELECT * FROM product_units
WHERE product_name = 'Banana Bread';


-- UPDATE
Expand All @@ -128,6 +228,32 @@ 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. */

ALTER TABLE product_units
ADD current_quantity INT;



SELECT vi.product_id,
COALESCE(vi.quantity, 0) AS last_quantity
FROM vendor_inventory vi
WHERE vi.market_date = (
SELECT MAX(market_date)
FROM vendor_inventory
WHERE vendor_inventory.product_id = vi.product_id
);

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 vendor_inventory.product_id = vi.product_id
)
)
WHERE product_id IN (
SELECT product_id FROM vendor_inventory
);


SELECT * FROM product_units;
Binary file modified 05_src/sql/farmersmarket.db
View file
Open in desktop
Binary file not shown.