dbt_cloud/models/intermediate/int__dates.sql

-- 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