Skip to content

Homework-4,5,6 #4

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
dkrishnm wants to merge 8 commits into
base: main
Choose a base branch
from
Open

Homework-4,5,6 #4

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
8 commits
Select commit Hold shift + click to select a range
f554ea1
Update homework_4.sql
dkrishnm May 23, 2024
68205ce
clear merge conflicts
dkrishnm May 23, 2024
0764e6e
Updates for homework4
dkrishnm May 24, 2024
31e4384
Update homework_5.sql
dkrishnm May 24, 2024
189acc3
Update homework_5.sql
dkrishnm May 24, 2024
49ccfa3
Update homework_5.sql
dkrishnm May 25, 2024
e4de378
Update homework_4.sql UNION
dkrishnm May 25, 2024
36441c4
Update homework_6.md
dkrishnm May 25, 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
59 changes: 56 additions & 3 deletions 03_homework/homework_4.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -18,6 +18,10 @@ Edit the appropriate columns -- you're making two edits -- and the NULL rows wil
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 @@ -30,16 +34,34 @@ 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. */

with ordered_visits as (
SELECT
customer_id,
market_date,
DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY market_date desc) AS visit_number
FROM
customer_purchases )
select customer_id, market_date, visit_number from ordered_visits 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, COUNT(*) AS purchase_count
FROM customer_purchases
GROUP BY customer_id, product_id;


-- String manipulations
Expand All @@ -54,11 +76,26 @@ 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_id,
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. */


SELECT
product_id,
product_name,
product_size,
product_category_id,
product_qty_type,
TRIM(SUBSTR(product_name, INSTR(product_name, '-') + 1)) AS description
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 @@ -70,6 +107,22 @@ 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 rankList as (
select market_date, cost,
RANK() OVER (PARTITION by cost order by cost asc) as CostRank
from (select market_date, SUM(quantity*cost_to_customer_per_qty) as cost from customer_purchases group by market_date)
)
select rl.market_date, rl.cost
from rankList rl
where rl.cost = (select MAX(cost) from rankList)
UNION ALL
select rl.market_date, rl.cost
from rankList rl
where rl.cost = (select MIN(cost) from rankList)







41 changes: 39 additions & 2 deletions 03_homework/homework_5.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -9,26 +9,44 @@ 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 sum_price as
(select sum(original_price*5) as totalvalue , product_id, vendor_id from (
SELECT DISTINCT(product_id), vendor_id, original_price, customer_first_name, customer_id
FROM vendor_inventory cross join customer) group by product_id, vendor_id)
select p.product_name, v.vendor_name, sum_price.totalvalue from sum_price
inner join product p on p.product_id = sum_price.product_id
inner join vendor v on v.vendor_id = sum_price.vendor_id

-- INSERT
/*1. Create a new table "product_units".
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
SELECT *, CURRENT_TIMESTAMP AS snapshot_timestamp
FROM product
WHERE product_name = 'Apple Pie';


-- 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 snapshot_timestamp = (
SELECT MIN(snapshot_timestamp)
FROM product_units where product_name = 'Apple Pie'
) and product_name = 'Apple Pie'


-- UPDATE
Expand All @@ -49,3 +67,22 @@ 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. */


WITH inventory AS (
SELECT product_id, SUM(quantity) AS i_qty
FROM vendor_inventory
GROUP BY product_id
),
bought AS (
SELECT product_id, SUM(quantity) AS b_qty
FROM customer_purchases
GROUP BY product_id
),
prodcurrqty AS (
SELECT inventory.product_id, (inventory.i_qty - COALESCE(bought.b_qty, 0)) AS difference
FROM inventory
LEFT JOIN bought ON inventory.product_id = bought.product_id
)
update product_units as pu
set current_quantity = pcq.difference
from prodcurrqty pcq
where pcq.product_id = pu.product_id;
17 changes: 17 additions & 0 deletions 03_homework/homework_6.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6,3 +6,20 @@
<br>

**Write**: Reflect on your previous work and how you would adjust to include ethics and inequity components. Total length should be a few paragraphs, no more than one page.



With my previous experience as a software engineer with mental wellness industry, data ethics has been crucial to protect users privacy and giving them control over their data. This has been extremely important in maintaining trust and create a safe space for their mental health needs.

Use of personal data : As a user interface designer, it’s important to create web pages that collect minimal data while still delivering effective support. Also we should identify data points which could reveal sensitive information

User accessibility: Design more features which give users more control on their data. They should have access to easily view and edit their personal information which is being shared. This would enable them to make more informed decisions about their well being. Also the system would be more transparent

User Recommendations: While building personalized recommendations it should reflect their needs and reflect their diversity and mitigate any bias. This could be tailored to consider factors like race, ethnicity, and socioeconomic background

Language support: Its important to support the website with content in multiple languages ensuring access to a wider user base.

The above adjustments helped build a valuable tool that promotes mental wellness in an ethical and equitable manner.