Conversation
Dmytro-Bonislavskyi
left a comment
There was a problem hiding this comment.
Hi @Caoming1582 Almost there, just a couple of small issues:
Q2,4 no filters by date,
And double check for the syntax error in Q3
|
Hi! I made the following changes to the windowed functions section. Thank you! --Windowed Functions --new version. I use dense rank this time and number each customer's visit dates in chronological order. dense rank gives the same visit number to identical dates for each customer. /* 2. Reverse the numbering of the query from a part so each customer’s most recent visit is labeled 1, --i added order by market_date desc to sort from most recent date to the oldest. |
What changes are you trying to make? (e.g., adding or removing code, refactoring existing code, adding reports)
This assignment has 4 components. In the first section, I made a data model for a hypothetical bookstore. In the second and third sections, I practiced with coalesce, windowed functions, string manipulation, union, cross join, insert, and delete.
In the last section, I read an interesting article and reflected on the 'invisible' human contributions to AI model building and training.
What did you learn from the changes you have made?
I became more comfortable with making data models and working with SQL commands.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
For the windowed function section, dense rank or row number can be used to number each customer's visits. This can depend on whether every purchase row is counted separately (row number) or if visits should be counted based on individual market dates (dense rank).
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
The last question was quite challenging. I need to determine how to update the current quantity using the most recent inventory record for each product. Since vendor inventory can have multiple rows for the same product, I first need to select the latest market date by sorting in descending order by date and using limit 1.
I also struggled with the coalesce function, where I want to replace the missing null values with 0. It took me a few tries to figure out where the coalesce() in the query so that it would return the correct output.
How were these changes tested?
part 2 and 3 are tested on SQLite
A reference to a related issue in your repository (if applicable)
Checklist