Conversation
anjali-deshpande-hub
left a comment
There was a problem hiding this comment.
Well done!
I have added a few corrections. Kindly go over them.
| ORDER BY market_date | ||
| ) AS visit_number | ||
| FROM customer_purchases | ||
| WHERE market_date < '2022-04-29'; |
There was a problem hiding this comment.
Looks good so far. You also want to add "GROUP BY customer_id, market_date" so the result set will give you 89 rows instead of 170 rows
|
|
||
|
|
||
|
|
||
| --vi = vendor_inventory, pu = product_units |
There was a problem hiding this comment.
The following query will give syntax error because the aliases are not defined.
| quantity, | ||
| ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY market_date DESC) AS rn | ||
| FROM vendor_inventory | ||
| ) |
There was a problem hiding this comment.
The subquery should give the alias vi that is used in WHERE clause below.
| ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY market_date DESC) AS rn | ||
| FROM vendor_inventory | ||
| ) | ||
| WHERE vi.product_id = pu.product_id |
There was a problem hiding this comment.
There is no 'pu' alias. Instead use:
WHERE vi.product_id = product_units.product_id
| WHERE vi.product_id = pu.product_id | ||
| AND vi.rn = 1 | ||
| ) | ||
| WHERE pu.product_qty_type = 'unit'; |
There was a problem hiding this comment.
There is no 'pu' alias. Instead use:
WHERE product_units.product_qty_type = 'unit';
|
|
||
| DELETE FROM product_units | ||
| WHERE product_id = 1006057758 | ||
| AND snapshot_timestamp < CURRENT_TIMESTAMP; |
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.
| GROUP BY | ||
| vendor_id, | ||
| product_id; | ||
| --END QUERY |
There was a problem hiding this comment.
Your second subquery (customers for the CROSS JOIN) is correct. Try to first form distinct vendor - product combinations before applying the CROSS JOIN.
Remember that the output should show vendor_name and product_name, not IDs according to the requirement. So you have to join vendor and product tables to retrieve those distinct names.
| ) AS purchase_count | ||
| FROM customer_purchases | ||
| WHERE market_date < '2022-04-29'; | ||
| --END QUERY |
There was a problem hiding this comment.
Well done! Here also add ORDER BY customer_id, product_id, market_date
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
Adding a PDF file for the first part and modified some sql files.
What did you learn from the changes you have made?
More sql coding!
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
NA
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
The first part of the assignment took me very long
How were these changes tested?
I searched some sql coding online
A reference to a related issue in your repository (if applicable)
Checklist