Conversation
confirmed with Thomas on Sunday as I was sick over the weekend and at start of week.
monzchan
left a comment
There was a problem hiding this comment.
The diagram look great!
you may have to answer prompt 3 (SCD type 1 and 2).
The script looks great! you are almost there :)
| SELECT | ||
| customer_id, | ||
| product_id, | ||
| COUNT() OVER (PARTITION BY customer_id ORDER BY product_id) AS product_purchase_count, |
There was a problem hiding this comment.
COUNT()requires a column or . So, you may consider COUNT() OVER (PARTITION BY.......)
There was a problem hiding this comment.
Can you please elaborate on this feedback?
The script above has COUNT() with OVER (PARTITION BY customer_id ORDER BY product_id) so I'm a bit unclear on the suggestion.
Thank you. :)
There was a problem hiding this comment.
Sorry for the unclarity. COUNT( ) may need to indicate a column like COUNT(*) instead of leaving it blank, to avoid error at SQL
| (SELECT product_id | ||
| FROM product_units | ||
| WHERE product_name='Churros' | ||
| ORDER BY snapshot_timestamp ASC LIMIT 1) |
There was a problem hiding this comment.
This is fail in some SQL engines. ORDER BY and LIMIT inside IN are not allowed. Consider MIN(snapshot_timestamp)
monzchan
left a comment
There was a problem hiding this comment.
The diagram look great!
you may have to answer prompt 3 (SCD type 1 and 2).
The script looks great! you are almost there :)
Late submission confirmed with Thomas on Sunday as I was sick over the weekend and at start of week.
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
Adding my assignment.
What did you learn from the changes you have made?
Got familiar with window functions, modifying tables, unions, and cross joins.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
I iterated a number of approaches for many of the problems, especially those with nested statements. I.e., to use CTEs, temporary tables, or sub-queries.
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
The Cross Join and final Delete problem were challenging. Conceptually simple, but executing the operations in the right order to get the correct result. It took a lot of iterations and almost 'unit testing' individual parts of the queries to ensure the output was accurate before pasting it into the larger query.
How were these changes tested?
Piecemeal by sub-query (and variations to test how functions would work to create a specific sub-query) before testing it altogether for the final submission.
A reference to a related issue in your repository (if applicable)
I initially pushed to the assignment-one repository, then branched assignment-two from main, and accidentally overwrote my entire local assignment at the final hour. Fortunately I the pull request to the assignment-one had all the code still accessible.
Checklist