Conversation
| All the other rows will remain the same.) */ | ||
|
|
||
| SELECT * | ||
| ,ifnull (product_name, 'unknown') |
There was a problem hiding this comment.
product_name replacement is not required by the question. But, you may need to handle product_qty_type
|
|
||
| SELECT * | ||
| ,ifnull (product_name, 'unknown') | ||
| ,coalesce (product_size, 'unit') |
There was a problem hiding this comment.
please replace product_size with ' ' (blank) instead of 'unit' as required
| ORDER BY market_date ASC | ||
| ) AS visit | ||
| FROM customer_purchases; | ||
|
|
There was a problem hiding this comment.
please also answer the question using ROW_NUMBER()
| ORDER BY market_date DESC | ||
| ) AS visit | ||
| FROM customer_purchases; | ||
|
|
There was a problem hiding this comment.
please filter the result to the most recent visit. Consider using WHERE
| SELECT product_name, | ||
| CASE | ||
| WHEN INSTR (product_name, '-') THEN | ||
| SUBSTR (product_name, RTRIM(LTRIM(INSTR(product_name, '-') + 1))) |
There was a problem hiding this comment.
INSTR() returns position, not a string. Consider LTRIM(RTRIM(SUBSTR(product_name,INSTR(product_name,'-')+1)))
| RANK () OVER (ORDER BY sales DESC) as rank_desc | ||
| FROM sales_table | ||
| ) | ||
| SELECT market_date, sales FROM days_ranked_table WHERE rank_desc == 1 |
| Before your final group by you should have the product of those two queries (x*y). */ | ||
|
|
||
| /* | ||
| sorry this qeustion was too ambigous for me :( I dont know how to find out: |
There was a problem hiding this comment.
First, count how many customers there are (y). Then, think about how many vendor-product combinations exist (x). The total revenue per product per vendor for all customers is like multiplying x * y before you do any grouping :)
There was a problem hiding this comment.
A 'date' table is required as per question
Please also complete prompt3 (type 1 & 2 SCD)
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
adding
What did you learn from the changes you have made?
making changes
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
How were these changes tested?
A reference to a related issue in your repository (if applicable)
Checklist