Day of week last year

0
votes

Im trying to calculate the date of the equivalent current day last year same week in standard SQL BigQuery.

For instance if the current date 2019-07-01 is a monday of week 27. I want to know the date of monday of week 27 last year (2018).

Current week would be select extract(isoweek from current_date()) but I don't know how to use this to calculate a date.

Expected output is 2018-07-02 if current date is 2019-07-01

The reason for this is that I want to compare current sales with same week and day of week as last year.

Any suggestions are appreciated.

2
google-bigquery
What if there is no equivalent in the previous year? Some years have different numbers of ISO weeks. - Elliott Brossard
You're right. This is always a problem but this is how it is measured. - i_am_cris

2 Answers

1
votes

Something like this should get you going...

with dates as (select * from unnest(generate_date_array('2018-01-01','2019-12-31', interval 1 day)) as cal_date),
       cal as (select cal_date, cast(format_date('%Y', cal_date) as int64) as year, cast(format_date('%V', cal_date) as int64) as week_num, format_date('%A', cal_date) as weekday_name from dates)
select c1.cal_date, c1.week_num, c1.weekday_name, c2.cal_date as previous_year_same_weekday
from cal c1
inner join cal c2 
  on c1.year = c2.year+1 and c1.week_num = c2.week_num and c1.weekday_name = c2.weekday_name

The above query uses a week starting on a Monday, you may need to play around with the format_date() arguments as seen here to modify it for your needs.

1
votes

This query returns no results, implying that SHIFT works. The function returns NULL if a year does not have the same number of weeks as its predecessor.

CREATE TEMP FUNCTION P_YEAR(y INT64) AS (
  MOD(CAST(y + FLOOR(y / 4.0) - FLOOR(y / 100.0) + FLOOR(y / 400.0) AS INT64), 7)
);

CREATE TEMP FUNCTION WEEKS_YEAR(y INT64) AS (
  52 + IF(P_YEAR(y) = 4 OR P_YEAR(y - 1) = 3, 1, 0)
);

CREATE TEMP FUNCTION SHIFT(d DATE) RETURNS DATE AS (
  CASE
    WHEN WEEKS_YEAR(EXTRACT(ISOYEAR FROM d)) != WEEKS_YEAR(EXTRACT(ISOYEAR FROM d) - 1)
      THEN null
    WHEN WEEKS_YEAR(EXTRACT(ISOYEAR FROM d)) = 52
      THEN DATE_SUB(d, INTERVAL 52 WEEK)
    ELSE d
  END
);

WITH dates AS (
SELECT d
FROM UNNEST(GENERATE_DATE_ARRAY('2000-12-31', '2020-12-31', INTERVAL 1 DAY)) AS d
)
SELECT
 d,
 EXTRACT(ISOWEEK FROM d) AS orig_iso_week,
 EXTRACT(ISOWEEK FROM SHIFT(d)) AS new_iso_week,
 SHIFT(d) AS new_d
FROM dates
WHERE EXTRACT(ISOWEEK FROM d) != EXTRACT(ISOWEEK FROM SHIFT(d))
  AND SHIFT(d) IS NOT NULL