Skip to content

assignment-two completed #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
AbrahimSA wants to merge 2 commits into
base: main
Choose a base branch
from
Open

assignment-two completed #3

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
a7ead9c
assignment-two completed
AbrahimSA Dec 22, 2024
4c15e4d
assignment-two completed reviewd
AbrahimSA Dec 23, 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
5 changes: 3 additions & 2 deletions 02_activities/assignments/Assignment2.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -55,8 +55,9 @@ The store wants to keep customer addresses. Propose two architectures for the CU

```
Your answer...
```

I created the Customer table with flag keep_old_address as boolean, and I create a Customer_address table with details of customer address and Customer_id aand boolead is_current that indicate that it is the current customer address.
If the flag keep_old_address is true will save the new customer address and set is_current flag as true and updated the old customer address information as is_current false;
If the flag keep_old_address is false the customer address will be updated.
***

## Section 2:
Expand Down
130 changes: 124 additions & 6 deletions 02_activities/assignments/assignment2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,3 +1,8 @@

/* ASSIGNMENT 2 */
/* SECTION 1 */
Diagram of bookstore you can find on the following path 02_activities\assignments\images\bookstore-diagram.jpg

/* ASSIGNMENT 2 */
/* SECTION 2 */

Expand All @@ -20,7 +25,8 @@ 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 COALESCE(product_name,'') || ', ' || COALESCE(product_size,'')|| ' (' || COALESCE(product_qty_type,'') || ')'
FROM product

--Windowed Functions
/* 1. Write a query that selects from the customer_purchases table and numbers each customer’s
Expand All @@ -32,17 +38,41 @@ 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 DISTINCT customer_id,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date) AS customer_visit,
market_date
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 DISTINCT customer_id,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date DESC) AS customer_visit,
market_date
FROM customer_purchases) x
WHERE customer_visit = 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. */

-- This query is returning how many different times that customer has purchased that product_id without inform the date and only one row per customer_id and product_id
SELECT distinct customer_id, product_id, count() OVER (PARTITION BY customer_id, product_id) times_purchased
FROM customer_purchases

-- Related of comment "Missing transaction time, output also not sorted", sorry I think this is what you are looking for.
-- This query is returning how many different times that customer has purchased one product showing all rows on the table showing also market_date
SELECT
customer_id,
product_id,
market_date,
COUNT(market_date) OVER (PARTITION BY customer_id, product_id) AS purchase_count
FROM
customer_purchases;


-- String manipulations
Expand All @@ -56,12 +86,22 @@ Remove any trailing or leading whitespaces. Don't just use a case statement for
| Habanero Peppers - Organic | Organic |

Hint: you might need to use INSTR(product_name,'-') to find the hyphens. INSTR will help split the column. */
SELECT product_name,
CASE WHEN trim(substr(product_name, INSTR(product_name,'-')+2)) IN ('Organic','Jar') THEN trim(substr(product_name, INSTR(product_name,'-')+2)) ELSE NULL END description
FROM product


-- Ernani, I am not usre if I understood your request(Try making it without the product names or partially product names. The question asks with a very specific), I am sharing a new query below
SELECT
product_name,
TRIM(SUBSTR(product_name, INSTR(product_name, '-') + 2)) AS product_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]' = 1

-- UNION
/* 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.
Expand All @@ -73,7 +113,18 @@ 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. */


SELECT * FROM (SELECT market_date, SUM(quantity * cost_to_customer_per_qty ) total_sales
FROM customer_purchases
GROUP BY market_date
ORDER BY total_sales
LIMIT 1) x
UNION
SELECT * FROM (
SELECT market_date, SUM(quantity * cost_to_customer_per_qty ) total_sales
FROM customer_purchases
GROUP BY market_date
ORDER BY total_sales DESC
LIMIT 1 ) y


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

-- Sorry, it is a litter confised this question, since you did not talked about which value from vendor_inventory I should consider I am using the most recent value

SELECT
v.vendor_name,
v.product_name,
v.price * 5 * c.customer_count AS total_price
FROM
-- Subquery for vendors and products
(SELECT v.vendor_name, p.product_name, i.original_price price
FROM vendor v
INNER JOIN vendor_inventory i ON (v.vendor_id = i.vendor_id)
INNER JOIN (SELECT product_id, vendor_id, max(market_date) market_date
FROM vendor_inventory
GROUP BY product_id, vendor_id) g ON ( i.product_id = g.product_id AND i.vendor_id = g.vendor_id AND i.market_date = g.market_date )
INNER JOIN product p ON (i.product_id = p.product_id)
GROUP BY v.vendor_name, p.product_name
) v
CROSS JOIN
-- Subquery for the number of customers
(SELECT COUNT(customer_id) AS customer_count
FROM customer) c
ORDER BY
v.vendor_name, v.product_name;

-- Ernani, related of "The result seems not resoanable. Based on your question about understanding the question you can try it without counting customers and grouping by after the CROSS JOIN. You ay think it visually.",
-- I am not sure if it is you are looking for, but this case I will calculate data from every single line from vendor_inventory table; The same same product will be considerer many times.
SELECT
v.vendor_name,
p.product_name,
SUM(5 * vi.original_price * c.customer_count ) AS price
FROM vendor v
JOIN vendor_inventory vi ON v.vendor_id = vi.vendor_id
JOIN product p ON vi.product_id = p.product_id
CROSS JOIN
(SELECT COUNT(customer_id) AS customer_count FROM customer) c
GROUP BY
v.vendor_name, p.product_name


-- INSERT
Expand All @@ -97,18 +185,33 @@ 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). */

-- Ernani, related your comment "Seens like you added a product that doesn't exist in the products table ("Pasture-raised eggs") and the DELETE clause won't work for the older same product. Please review it."
-- Yes I added a new product that does not exist because the information above is saying "can be any product you desire", it is not saying any product from product table
--

-- This query is related of one product from product that product_qty_type != 'unit'
SELECT product_id, product_name, product_size, product_category_id,product_qty_type, CURRENT_TIMESTAMP
FROM product
WHERE product_qty_type != 'unit'
LIMIT 1

-- This query is from one new product
INSERT INTO product_units
(product_id, product_name, product_size, product_category_id,product_qty_type, snapshot_timestamp)
Values (56, 'Pasture-raised eggs', '1 dozen',6,'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 = (SELECT product_id FROM product_units ORDER BY snapshot_timestamp LIMIT 1)


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

-- Note: Since the vendor_inventory could have more than one product_id from the last market_date because vendor_id is part of primary key, I am using MAX of quatity,
-- but I could use SUM function to get total of quatity from the last day for each product. I am not sure exaclty what you are looking for.

-- Ernani I did not include the ALTER TABLE product_units ADD current_quantity INT; because the command was informed on 02_activities\assignments\Assignment2.md file
-- Related of your comment: "you have two UPDATE clauses with some issues (check if ";" is important too) and check why your result is resulting in 0 (zero)"
-- Sorry, I am not sure what happened I shared the wrong query, please considere this one.

UPDATE product_units
SET current_quantity = coalesce ((
SELECT vi.quantity
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
ORDER BY vi.market_date DESC
LIMIT 1
),0);



Binary file added 02_activities/assignments/images/bookstore-diagram.jpg
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.