Ensure the ORDER BY market_date DESC is inside the ROW_NUMBER() so that the most recent visit is labeled 1.

Hi there,

I've tried and it produces the exact same result as without the ORDER BY market_date DESC is inside the ROW_NUMBER(). Is my submission acceptable? Please advise. Thank you!

SELECT customer_id, market_date as most_recent_visit

FROM (

WITH visits AS (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
ORDER BY customer_id, market_date DESC)

SELECT * , ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY market_date DESC) as visit
FROM visits
ORDER BY customer_id, visit) mrv

WHERE mrv.visit = 1

Wonderful!

Using DISTINCT here is unnecessary and may hide duplciates

Hi again,

I've tried removing DISTINCT and it duplicate the resulting rows.

I got 9 rows for customer_id 1, product_1 times_purchased 9;
13 rows for customer_id 1, product_id 2, times_purchased 13, etc.

Please let me know if my code is acceptable. Thank you!

Just DISTINCT is unnecessary, but your script is acceptable :)

Please create a separate 'Date' table.
And please further explain prompt 3 (type 1&2 SCD)

Hi there,

Please see the revised diagram attached.
Thank you for your feedback!

Anna
SQL_Assignment2_Prompt3.pdf

The question actually requires you to put the date table with the other 6 tables that you have created and show the relationship. But since you have demonstrated that you are mastering the knowledge and skills, I think you deserve a pass in here! :)