Data Mart: Data Cleansing, Step 2.

[Lesego-Serobatse]

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)

[praveen555]

I am not sure I quite get it. Which sql are you using ? I am using mysql https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_weekofyear and used weekofyear() function which gave correct results.

For part C analysis instead of using week_number to select the rows i used date_sub, date_add and interval function in mysql.

[Lesego-Serobatse]

This is what I mean in counting weeks then, check out this:

Schema (MySQL v5.7)

---

Query #1

SELECT WEEKOFYEAR('2018-01-07');

WEEKOFYEAR('2018-01-07')
1

---

Query #2

SELECT WEEKOFYEAR('2018-01-14');

WEEKOFYEAR('2018-01-14')
2

---

Query #3

SELECT WEEKOFYEAR('2019-01-07');

WEEKOFYEAR('2019-01-07')
2

---

Query #4

SELECT WEEKOFYEAR('2019-01-14');

WEEKOFYEAR('2019-01-14')
3

---

Query #5

SELECT WEEKOFYEAR('2020-01-07');

WEEKOFYEAR('2020-01-07')
2

---

Query #6

SELECT WEEKOFYEAR('2020-01-14');

WEEKOFYEAR('2020-01-14')
3

---

[Lesego-Serobatse]

Well I'm using sql server 2022, but the above code is for mysql, this may need a little adjustment, not sure if the function above has a 'mode' argument that which you can easily specify the start of the week day, I managed to delve deeper into Katies code, and the numbers are good, that means the point I raised had no impact whatsoever, lol, Katie is fantastic at this sql stuff. She is wow, fantastic, Danko!!!!(thank you)