Skip to content

Assignment 2 done #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

Open
rachfern wants to merge 4 commits into
base: main
Choose a base branch
from
Open

Assignment 2 done #2

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
4 commits
Select commit Hold shift + click to select a range
d1189cb
Finished Section 2
rachfern Dec 19, 2024
97e45a1
Section 3 done
rachfern Dec 20, 2024
d97e297
Update Assignment2.md with section 1 prompt 3 answer
rachfern Dec 21, 2024
83e552a
commit ERDs for section 1 prompts 1 and 2
rachfern Dec 21, 2024
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
4 changes: 3 additions & 1 deletion 02_activities/assignments/Assignment2.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -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...
Let Architecture 1 be the table where you retain changes (representing type 1). Each customer has one row in the table, and when the customer changes their address, the new address overwrites the old address.

Let Architecture 2 be the table where you retain changes (representing type 2). Each address change by a customer, creates a new row in the table with a start date and end date columns defining it's validity period.
```

***
Expand Down
Binary file added 02_activities/assignments/Assignment2_Section1.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.
146 changes: 146 additions & 0 deletions 02_activities/assignments/assignment2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -20,6 +20,9 @@ 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') || ')'
FROM product;


--Windowed Functions
Expand All @@ -32,17 +35,60 @@ 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(). */

/*ROW_NUMBER()*/
SELECT
customer_id,
market_date,
cost_to_customer_per_qty,
product_id,
quantity,
vendor_id,
transaction_time,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number
FROM customer_purchases;

/*DENSE_RANK()*/
SELECT
customer_id,
market_date,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number
FROM customer_purchases
GROUP 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. */

/*REVERSE*/
SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS reversed_visit_number
FROM customer_purchases;

/*REVERSE AND FILTER*/
WITH reversed_visits AS (
SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS reversed_visit_number
FROM customer_purchases
)
SELECT *
FROM reversed_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. */

SELECT
customer_id,
product_id,
market_date,
COUNT(*) OVER (PARTITION BY customer_id, product_id) AS purchase_count
FROM customer_purchases;


-- String manipulations
Expand All @@ -57,10 +103,20 @@ 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
Expand All @@ -73,7 +129,38 @@ 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 TotalSales AS (
SELECT
market_date,
SUM(cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date
),

RankedSales AS (
SELECT
market_date,
total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS best_day_rank,
RANK() OVER (ORDER BY total_sales ASC) AS worst_day_rank
FROM TotalSales
)

SELECT
market_date,
total_sales,
'Best Day' AS day_type
FROM RankedSales
WHERE best_day_rank = 1

UNION

SELECT
market_date,
total_sales,
'Worst Day' AS day_type
FROM RankedSales
WHERE worst_day_rank = 1;


/* SECTION 3 */
Expand All @@ -89,6 +176,20 @@ 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). */

WITH customercount AS (
SELECT COUNT(DISTINCT customer_id) AS customer_count
FROM customer_purchases
)
SELECT
v.vendor_name,
p.product_name,
5 * cc.customer_count AS total_quantity_sold,
5 * cc.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 customercount cc
ORDER BY v.vendor_name, p.product_name;


-- INSERT
Expand All @@ -97,18 +198,46 @@ 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 (
151,
'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_name = 'Apple Pie'
AND snapshot_timestamp = (
SELECT MIN(snapshot_timestamp)
FROM product_units
WHERE product_name = 'Apple Pie'
);


-- UPDATE
Expand All @@ -128,6 +257,23 @@ 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;
WITH LastQuantity AS (
SELECT vi.product_id, vi.quantity
FROM vendor_inventory vi
WHERE vi.market_date = (
SELECT MAX(market_date)
FROM vendor_inventory vi2
WHERE vi2.product_id = vi.product_id
)
)
UPDATE product_units
SET current_quantity = COALESCE((
SELECT quantity
FROM LastQuantity
WHERE product_id = product_units.product_id
), 0);