I want to display user numbers per week from Google Analytics in our warehouse. I receive raw data through our ETL per week and the iso week as a string: GaIsoYearIsoWeek:’201951′ with the corresponding user number as an integer. To further calculate with user number (for example calculate sessions per user) I need the start date of the iso week in a date format. I’ve tried to use ‘to_date’ functions and others but it breaks with trying to go the way from week number back to a date. Extracting the year works but not the week. Can anyone help? Thanks!
Advertisement
Answer
As I understand you want to get the start date from a year/date value. Maybe this could help:
SELECT SUBSTR( mydate, 1, 4) as target_year, SUBSTR( mydate, 5 ) as week,
IFF( SUBSTR( mydate, 5 ) = 1, to_date( SUBSTR( mydate, 1, 4), 'YYYY'),
dateadd( week, SUBSTR( mydate, 5 )-2, dateadd( day, 1, LAST_DAY(to_date( SUBSTR( mydate, 1, 4), 'YYYY'), week)))) AS firt_day_of_week
FROM VALUES ('20191'), ('20192'), ('201912'),('201951') t (mydate);
The first line is just to show the parsed values, the important part is the IFF block. It checks if the first week of the year is requested, and returns first day of the year. If we are not looking for the first week, it calculates the first day of the week based on last day of the week function 🙂
Here’s the output of the above:
New: I didn’t know that there is rule for first week. If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in W01. So I updated the query:
SELECT SUBSTR( mydate, 1, 4) as target_year, SUBSTR( mydate, 5 ) as week,
IFF( (SUBSTR( mydate, 5 ) = 1 and DAYOFWEEK(to_date(target_year,'YYYY')) NOT IN (0,5,6) ), to_date(target_year,'YYYY'),
DATEADD( week, SUBSTR( mydate, 5 )-2 + IFF(DAYOFWEEK(to_date(target_year,'YYYY')) IN (0,5,6) ,1 , 0) ,
DATEADD( day, 1, LAST_DAY(TO_DATE( SUBSTR( mydate, 1, 4), 'YYYY'), week )))) AS firt_day_of_week
FROM VALUES ('202101'), ('202001'), ('201701'),('200301'), ('201951') t (mydate);
+-------------+------+------------------+
| TARGET_YEAR | WEEK | FIRT_DAY_OF_WEEK |
+-------------+------+------------------+
| 2021 | 01 | 2021-01-04 |
| 2020 | 01 | 2020-01-01 |
| 2017 | 01 | 2017-01-02 |
| 2003 | 01 | 2003-01-01 |
| 2019 | 51 | 2019-12-16 |
+-------------+------+------------------+
Hope it’s ok now, cause I didn’t test it properly.