Conversation
There was a problem hiding this comment.
Excellent work!
Section 1 prompt 1: You also have to add Date table and its relationships with existing tables.
I have added some comments for Section 2 and 3. Section 4 looks good.
I agree that this assignment is the most challenging of all the ones you have had so far.
|
|
||
| DELETE FROM product_units | ||
| WHERE product_name = 'Sweet Corn' AND snapshot_timestamp = "2026-04-08 01:01:51"; | ||
|
|
There was a problem hiding this comment.
Your query correctly filters by product and uses a timestamp condition. Think about how to select the oldest row dynamically instead of hard coding the value. A better a way would be to find the oldest record (e.g., using MIN(snapshot_timestamp)) and delete only that row.
| ORDER BY | ||
| v.vendor_name, | ||
| p.product_name; | ||
| `` |
There was a problem hiding this comment.
You're very close. The main idea is right. Make a small subquery with one row per vendor_name , product_name and 5 * original_price, then CROSS JOIN that to the customer list, and finally SUM(price) grouped by vendor and product.
| ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date) AS visit_number | ||
| FROM customer_purchases | ||
| where market_date < "2022-04-29"; | ||
|
|
There was a problem hiding this comment.
You can add
GROUP BY customer_id, market_date
ORDER BY customer_id, market_date
to give unique market dates per customer
anjali-deshpande-hub
left a comment
There was a problem hiding this comment.
Approved. 64/70 (Section 1 Date table pending)
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
organize data tables and basic analysis
What did you learn from the changes you have made?
manipulate string, count, rank, insert, union, update etc.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
n/a
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
i find this assignment to be harder and more time-consuming than any of the assignments we did before. coming up with one solution was already difficult enough. creating the logical model was particularly challenging. it took me a long time to think through how these tables are connected. i consulted with peers and asked Google for code clarification.
How were these changes tested?
i ran the code in db browser first before committing.
A reference to a related issue in your repository (if applicable)
Checklist