-- CONFIG {{ config( materialized='view' ) }} -- IMPORT WITH holiday_and_non_working_day_lists AS ( SELECT * FROM {{ ref('stg_googlesheet__event_and_holiday_lists') }} ), -- LOGIC date_spine AS ( {{ dbt_utils.date_spine( start_date="to_date('01/01/2015', 'mm/dd/yyyy')", datepart="day", end_date="dateadd(year, 40, current_date)" ) }} ), fiscal_calendar as ( SELECT date_day, date_day AS date_actual, DAYNAME(date_day) AS day_name, DATE_PART('month', date_day) AS month_actual, DATE_PART('year', date_day) AS year_actual, DATE_PART(quarter, date_day) AS quarter_actual, CASE WHEN DATE_PART(dayofweek, date_day) = 0 THEN 7 ELSE DATE_PART(dayofweek, date_day) END AS day_of_week, CASE WHEN day_name = 'Mon' THEN date_day ELSE DATEADD('day', 0, DATE_TRUNC('week', date_day)) END AS first_day_of_week, CASE WHEN day_name = 'Mon' THEN WEEK(date_day) ELSE WEEK(date_day) END AS week_of_year_temp, --remove this column CASE WHEN day_name = 'Mon' AND LEAD(week_of_year_temp) OVER (ORDER BY date_day) = '1' THEN '1' ELSE week_of_year_temp END AS week_of_year, DATE_PART('day', date_day) AS day_of_month, ROW_NUMBER() OVER (PARTITION BY year_actual, quarter_actual ORDER BY date_day) AS day_of_quarter, ROW_NUMBER() OVER (PARTITION BY year_actual ORDER BY date_day) AS day_of_year, -- @TODO @GERSON ,done -- BUSINESS LOGIC: Feb - Jan (2022), Feb - Jan (2023) CASE WHEN month_actual in (2,3,4,5,6,7,8,9,10,11,12) THEN year_actual WHEN DATE_PART('month', DATEADD( month,-1, date_day) ) = 12 THEN year_actual-1 ELSE (year_actual+1) END AS fiscal_year, -- @TODO @GERSON , done -- BUSINESS LOGIC: Feb - Apr (Q1), May - Jul (Q2), Aug - Oct (Q3), Nov - Jan (Q4) CASE WHEN month_actual in (2,3,4) THEN '1' WHEN month_actual in (5,6,7) THEN '2' WHEN month_actual in (8,9,10) THEN '3' WHEN month_actual in (11,12) THEN '4' WHEN DATE_PART('month', DATEADD(month,-1,date_day)) =12 THEN '4' -- WHEN month_actual= DATE_PART('year', month_actual) +1 and month_actual=1 THEN '4' ELSE '4' END AS fiscal_quarter, -- @TODO @GERSON ,done ROW_NUMBER() OVER (PARTITION BY fiscal_year, fiscal_quarter ORDER BY date_day) AS day_of_fiscal_quarter, ROW_NUMBER() OVER (PARTITION BY fiscal_year ORDER BY date_day) AS day_of_fiscal_year, TO_CHAR(date_day, 'MMMM') AS month_name, TRUNC(date_day, 'Month') AS first_day_of_month, LAST_VALUE(date_day) OVER (PARTITION BY year_actual, month_actual ORDER BY date_day) AS last_day_of_month, FIRST_VALUE(date_day) OVER (PARTITION BY year_actual ORDER BY date_day) AS first_day_of_year, LAST_VALUE(date_day) OVER (PARTITION BY year_actual ORDER BY date_day) AS last_day_of_year, FIRST_VALUE(date_day) OVER (PARTITION BY year_actual, quarter_actual ORDER BY date_day) AS first_day_of_quarter, LAST_VALUE(date_day) OVER (PARTITION BY year_actual, quarter_actual ORDER BY date_day) AS last_day_of_quarter, -- @TODO @GERSON ,done -- Change this 2 too FIRST_VALUE(date_day) OVER (PARTITION BY fiscal_year, fiscal_quarter ORDER BY date_day) AS first_day_of_fiscal_quarter, LAST_VALUE(date_day) OVER (PARTITION BY fiscal_year, fiscal_quarter ORDER BY date_day) AS last_day_of_fiscal_quarter, -- @TODO @GERSON,done FIRST_VALUE(date_day) OVER (PARTITION BY fiscal_year ORDER BY date_day) AS first_day_of_fiscal_year, LAST_VALUE(date_day) OVER (PARTITION BY fiscal_year ORDER BY date_day) AS last_day_of_fiscal_year, -- @TODO @GERSON ,done DATEDIFF('week', first_day_of_fiscal_year, date_actual)+1 AS week_of_fiscal_year, -- @TODO @GERSON ,done CASE WHEN EXTRACT('month', date_day) = 1 THEN 12 ELSE EXTRACT('month', date_day) - 1 END AS month_of_fiscal_year, LAST_VALUE(date_day) OVER (PARTITION BY first_day_of_week ORDER BY date_day) AS last_day_of_week, (year_actual || '-Q' || fiscal_quarter) AS quarter_name, -- @TODO @GERSON,done (fiscal_year || '-' || DECODE(fiscal_quarter, 1, 'Q1', 2, 'Q2', 3, 'Q3', 4, 'Q4')) AS fiscal_quarter_name, ('FY' || SUBSTR(fiscal_quarter_name, 3, 7)) AS fiscal_quarter_name_fy, DENSE_RANK() OVER (ORDER BY fiscal_quarter_name) AS fiscal_quarter_number_absolute, --here check fiscal_year || '-' || MONTHNAME(date_day) AS fiscal_month_name, ('FY' || SUBSTR(fiscal_month_name, 3, 8)) AS fiscal_month_name_fy, -- @TODO @GERSON DATE_TRUNC('month', last_day_of_fiscal_quarter) AS last_month_of_fiscal_quarter, IFF(DATE_TRUNC('month', last_day_of_fiscal_quarter) = date_actual, TRUE, FALSE) AS is_first_day_of_last_month_of_fiscal_quarter, DATE_TRUNC('month', last_day_of_fiscal_year) AS last_month_of_fiscal_year, IFF(DATE_TRUNC('month', last_day_of_fiscal_year) = date_actual, TRUE, FALSE) AS is_first_day_of_last_month_of_fiscal_year, DATEADD('day',7,DATEADD('month',1,first_day_of_month)) AS snapshot_date_fpa, DATEADD('day',44,DATEADD('month',1,first_day_of_month)) AS snapshot_date_billings FROM date_spine QUALIFY date_actual >= '2016-01-01' ORDER BY date_day ), holiday_group_by_date AS ( SELECT date, day, ARRAY_AGG(DISTINCT event_name) WITHIN GROUP (ORDER BY event_name) AS holiday_event_names, ARRAY_AGG(DISTINCT event_description) WITHIN GROUP (ORDER BY event_description) AS holiday_event_descriptions, ARRAY_AGG(DISTINCT state) WITHIN GROUP (ORDER BY state) AS holiday_states, ARRAY_AGG(DISTINCT country) WITHIN GROUP (ORDER BY country) AS holiday_countries, ARRAY_AGG(DISTINCT religion) WITHIN GROUP (ORDER BY religion) AS holiday_religions, ARRAY_AGG(DISTINCT holiday_general_type) WITHIN GROUP (ORDER BY holiday_general_type) AS holiday_event_general_types, ARRAY_AGG(DISTINCT holiday_specific_type) WITHIN GROUP (ORDER BY holiday_specific_type) AS holiday_event_specific_types, MAX(is_malaysia_holiday) AS is_malaysia_holiday, MAX(is_company_holiday) AS is_company_holiday, MAX(is_china_holiday) AS is_china_holiday, MAX(is_china_warehouse_holiday) AS is_china_warehouse_holiday FROM holiday_and_non_working_day_lists GROUP BY day, date ORDER BY date ), fiscal_calendar_join_holiday_list AS ( SELECT fiscal_calendar.*, holidays.holiday_event_names, holidays.holiday_event_descriptions, holidays.holiday_event_general_types, holidays.holiday_event_specific_types, holidays.holiday_states, holidays.holiday_countries, holidays.holiday_religions, holidays.is_malaysia_holiday, holidays.is_company_holiday, holidays.is_china_holiday, holidays.is_china_warehouse_holiday, '{{ modules.datetime.datetime.now(modules.pytz.timezone("Asia/Kuala_Lumpur")) }}' AS _dbt_ran_datetime FROM fiscal_calendar LEFT JOIN holiday_group_by_date AS holidays ON fiscal_calendar.date_day = holidays.date ), -- FINAL final__int__dates AS ( SELECT date_day, date_actual, day_name, month_actual, year_actual, quarter_actual, day_of_week, first_day_of_week, week_of_year, day_of_month, day_of_quarter, day_of_year, fiscal_year, fiscal_quarter, day_of_fiscal_quarter, day_of_fiscal_year, month_name, first_day_of_month, last_day_of_month, first_day_of_year, last_day_of_year, first_day_of_quarter, last_day_of_quarter, first_day_of_fiscal_quarter, last_day_of_fiscal_quarter, first_day_of_fiscal_year, last_day_of_fiscal_year, week_of_fiscal_year, month_of_fiscal_year, last_day_of_week, quarter_name, fiscal_quarter_name, fiscal_quarter_name_fy, fiscal_quarter_number_absolute, fiscal_month_name, fiscal_month_name_fy, last_month_of_fiscal_quarter, is_first_day_of_last_month_of_fiscal_quarter, last_month_of_fiscal_year, is_first_day_of_last_month_of_fiscal_year, snapshot_date_fpa, snapshot_date_billings, holiday_event_names, holiday_event_descriptions, holiday_event_general_types, holiday_event_specific_types, holiday_countries, holiday_states, holiday_religions, is_malaysia_holiday, is_company_holiday, is_china_holiday, is_china_warehouse_holiday, _dbt_ran_datetime FROM fiscal_calendar_join_holiday_list ) SELECT * FROM final__int__dates