katiehuangx / 8-week-sql-challenge Goto Github PK

Hi there Katie, I have been using your solutions as my reference, thank you for the effort you invested in all of this challenge.
I'd like to remind you that you may have forgotten to be inclusive of the fact that in step 2 of the cleansing process, you ought to make sure that each year week 1 must contain the dates 1st Jan - 7th Jan, week 2 must contain the dates 8th Jan - 14th Jan, and so on. With the code as it is, with the default start weekday in sql as 7 (Sunday), your week 1 in 2018 will contain dates 1st Jan - 6th Jan, and week 2 will contain dates 7th Jan - 13th Jan, and the rest of the week numbers to be misrepresented and inaccurate, which may mess up the entire analysis. Therefore you'll have to adjust for that in your code and everything will be good, BE mindful of the fact that 2018 has 53 weeks in it, the other years 52 weeks. You can adjust it with something like the code below in the attached file, replace 'date_missing' with 'week_date'(already converted into the proper datatype)

There is no solution provided for the below question under "Ingredient Optimizations" for the Pizza Runner Case Study.

1. What are the standard ingredients for each pizza?

@katiehuangx --3. What was the first item from the menu purchased by each customer?
WITH ordered_sales_cte AS
##(
SELECT
customer_id,
order_date,
product_name,
row_number() OVER(PARTITION BY s.customer_id ORDER BY s.order_date) AS rank
FROM dbo.sales AS s
JOIN dbo.menu AS m
ON s.product_id = m.product_id
)

SELECT
customer_id,
product_name
FROM ordered_sales_cte
WHERE rank = 1
GROUP BY customer_id, product_name;

--Using dense_rank:

--Using row_number(correct one):

Using the week_number to filter out the dates would result in incorrect analysis. A simple query will show that week_number shift in 2020.

Hence it is better to use date_add, date_sub and interval to filter out the correct dates.

I think your code was wrong, simply look up 01/01/2020 was Wednesday and your result doesn't have it.