Calendar Table
The main prerequisite is the one and mighty calendar
table. Having a table that is retransformed every day and containing all the necessary details about every date is useful in many ways.
Calendar table is only used in a JOIN. You join it to the date/timestamp/datetime on which you want to perform dynamic timeframes. Everything happens in calendar view and you don't have to add anything else to your source view.
❗This table has be transformed daily as it contains very useful columns called *_offset
that change every day.
Snowflake
WITH calendar_base AS
SELECT
-- Day
date,
DATEDIFF(DAY, CURRENT_DATE(), date) AS day_offset,
DAYOFWEEKISO(date) < 6 AS is_weekday,
-- Day + Week
DAYOFWEEK(date) AS day_of_week,
DAYOFWEEKISO(date) AS iso_day_of_week,
DATE_TRUNC(WEEK, date) AS first_day_of_week,
DATEADD(DAY, 6, DATE_TRUNC(WEEK, date)) AS last_day_of_week,
date = first_day_of_week AS is_start_of_week,
date = last_day_of_week AS is_end_of_week,
-- Day + Month
DAYOFMONTH(date) AS day_of_month,
COUNT(date) OVER(PARTITION BY DATE_TRUNC(MONTH, date)) AS days_in_month,
DATE_TRUNC(MONTH, date) AS first_day_of_month,
LAST_DAY(date) AS last_day_of_month,
FLOOR((EXTRACT(DAY FROM date) + 6) / 7) AS day_of_week_in_month,
date = first_day_of_month AS is_start_of_month,
date = last_day_of_month AS is_end_of_month,
-- Day + Quarter
DATE_TRUNC(QUARTER, date) AS first_day_of_quarter,
LAST_DAY(date, QUARTER) AS last_day_of_quarter,
RANK() OVER(PARTITION BY DATE_TRUNC(QUARTER, date) ORDER BY date) AS day_of_quarter,
COUNT(date) OVER(PARTITION BY DATE_TRUNC(QUARTER, date)) AS days_in_quarter,
date = first_day_in_quarter AS is_start_of_quarter,
date = last_day_of_quarter AS is_end_of_quarter,
-- Day + Year
DAYOFYEAR(date) AS day_of_year,
DATE_TRUNC(YEAR, date) AS first_day_of_year,
LAST_DAY(date, YEAR) AS last_day_of_year,
COUNT(date) OVER(PARTITION BY DATE_TRUNC(YEAR, date)) AS days_in_year,
date = first_day_of_year AS is_start_of_year,
date = last_day_of_year AS is_end_of_year,
-- Week
WEEK(date) AS week,
WEEKISO(date) AS iso_week,
DATEDIFF(WEEK, CURRENT_DATE(), date) AS week_offset,
-- Month
MONTH(date) AS month,
DATEDIFF(MONTH, CURRENT_DATE(), date) AS month_offset,
TO_VARCHAR(date, 'YYYY-MM') AS month_year,
-- Month + Quarter
DENSE_RANK() OVER(PARTITION BY DATE_TRUNC(QUARTER, date) ORDER BY DATE_TRUNC(MONTH, date)) AS month_in_quarter,
-- Quarter
EXTRACT(QUARTER FROM date) AS quarter,
DATEDIFF(QUARTER, CURRENT_DATE(), date) AS quarter_offset,
TO_VARCHAR(DATE_TRUNC(QUARTER, date), 'YYYY-MM') AS quarter_ym,
-- Year
EXTRACT(YEAR from date) AS year,
DATEDIFF(YEAR, CURRENT_DATE(), date) AS year_offset,
MOD(EXTRACT(YEAR FROM date), 4) = 0 AS is_leap_year
FROM (
SELECT
DATEADD(DAY, '+' || SEQ4(), '2020-01-01'::DATE)::DATE AS date
FROM
TABLE (
GENERATOR(ROWCOUNT => 3652)
)
)
),
calendar AS (
SELECT
*,
IFF(is_weekday, (COUNT(IFF(is_weekday, date, NULL)) OVER(PARTITION BY first_day_of_month ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), NULL) AS workday_in_month,
IFF(NOT is_weekday, (COUNT(IFF(is_weekday, NULL, date)) OVER(PARTITION BY first_day_of_month ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), NULL) AS weekend_day_in_month
FROM calendar_base
)
SELECT
date,
day_offset,
day_of_week,
iso_day_of_week,
first_day_of_week,
last_day_of_week,
day_of_month,
days_in_month,
first_day_of_month,
last_day_of_month,
day_of_week_in_month,
first_day_of_quarter,
last_day_of_quarter,
day_of_quarter,
days_in_quarter,
day_of_year,
first_day_of_year,
last_day_of_year,
days_in_year,
week,
iso_week,
week_offset,
month,
month_offset,
month_year,
month_in_quarter,
quarter,
quarter_offset,
quarter_ym,
year,
year_offset,
is_leap_year,
workday_in_month,
weekend_day_in_month,
MAX(workday_in_month) OVER(PARTITION BY first_day_of_month) AS working_days_in_month,
MAX(weekend_day_in_month) OVER(PARTITION BY first_day_of_month) AS weekend_days_in_month
FROM calendar
;
Last updated