Conversation
Completed assignment 1 sql queries and write-up.
anjali-deshpande-hub
left a comment
There was a problem hiding this comment.
Very well done! I agree that the last few queries are challenging.
Can you also add DATE table for Section 1 (prompt 1)
|
|
||
| DELETE | ||
| FROM product_units | ||
| WHERE product_id = 3 AND timestamp < '2026-04-05 23:55:45' |
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
| FROM customer_purchases | ||
| WHERE market_date < '2022-04-29' | ||
| ; | ||
| --END QUERY |
There was a problem hiding this comment.
Add ORDER BY customer_id, product_id, market_date based on the requirement.
| SELECT customer_id, market_date, | ||
| ROW_NUMBER() OVER (PARTITION BY customer_id | ||
| ORDER BY market_date DESC) AS customer_visit_count | ||
| FROM (SELECT DISTINCT customer_id, market_date FROM customer_purchases WHERE market_date < '2022-04-29') |
There was a problem hiding this comment.
The requirement is to not use the dates filter. Simply replaceFROM (SELECT DISTINCT customer_id, market_date FROM customer_purchases WHERE market_date < '2022-04-29')
with
FROM customer_purchases GROUP BY customer_id, market_date
and you will get the right result
anjali-deshpande-hub
left a comment
There was a problem hiding this comment.
Approved. 65/70 (Only Section 1 Date table pending)
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
Completed assignment 2. includes code, ERD diagrams, and a written reflection.
What did you learn from the changes you have made?
I gained a deeper understanding of intermediate SQL. In particular, updating SQL tables was something new. I also learned a bit more about database design.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
I considered several approaches to the database design for the bookstore ERD. I considered adding a third "employee schedule" table that detailed precise days of the week and times that each shift was associated with.
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
Updating the table was challenging. I had to break down the query into modular steps.
How were these changes tested?
I tested the sql queries by querying the database.
A reference to a related issue in your repository (if applicable)
Checklist