Handling dates in BI projects and Data Warehouses

CREATE TABLE code_examples.dim_date
(
  dim_date_id              INT PRIMARY KEY ,
  date_actual              DATE NOT NULL,
  epoch                    BIGINT NOT NULL,
  day_suffix               VARCHAR(4) NOT NULL,
  day_name                 VARCHAR(9) NOT NULL,
  day_of_week              INT NOT NULL,
  day_of_week_iso          INT NOT NULL,
  day_of_month             INT NOT NULL,
  day_of_quarter           INT NOT NULL,
  day_of_year              INT NOT NULL,
  week_of_month            INT NOT NULL,
  week_of_year             INT NOT NULL,
  week_of_year_iso         CHAR(10) NOT NULL,
  month_actual             INT NOT NULL,
  month_name               VARCHAR(9) NOT NULL,
  month_name_abbreviated   CHAR(3) NOT NULL,
  quarter_actual           INT NOT NULL,
  quarter_name             VARCHAR(9) NOT NULL,
  year_actual              INT NOT NULL,
  year_actual_iso          INT NOT NULL,
  first_day_of_week        DATE NOT NULL,
  last_day_of_week         DATE NOT NULL,
  first_day_of_month       DATE NOT NULL,
  last_day_of_month        DATE NOT NULL,
  first_day_of_quarter     DATE NOT NULL,
  last_day_of_quarter      DATE NOT NULL,
  first_day_of_year        DATE NOT NULL,
  last_day_of_year         DATE NOT NULL,
  mmyyyy                   CHAR(6) NOT NULL,
  mmddyyyy                 CHAR(10) NOT NULL,
  weekend_indr             BOOLEAN NOT NULL
);

INSERT INTO code_examples.dim_date
SELECT TO_CHAR(datum,'yyyymmdd')::INT AS dim_date_id,
       datum AS date_actual,
       EXTRACT(epoch FROM datum) AS epoch,
       TO_CHAR(datum,'fmDDth') AS day_suffix,
       TO_CHAR(datum,'Day') AS day_name,
       EXTRACT(dow FROM datum) + 1 AS day_of_week,
       EXTRACT(isodow FROM datum) AS day_of_week_iso,
       EXTRACT(DAY FROM datum) AS day_of_month,
       datum - DATE_TRUNC('quarter',datum)::DATE +1 AS day_of_quarter,
       EXTRACT(doy FROM datum) AS day_of_year,
       TO_CHAR(datum,'W')::INT AS week_of_month,
       EXTRACT(week FROM datum) AS week_of_year,
       TO_CHAR(datum,'YYYY"-W"IW') AS week_of_year_iso,
       EXTRACT(MONTH FROM datum) AS month_actual,
       TO_CHAR(datum,'Month') AS month_name,
       TO_CHAR(datum,'Mon') AS month_name_abbreviated,
       EXTRACT(quarter FROM datum) AS quarter_actual,
       CASE
         WHEN EXTRACT(quarter FROM datum) = 1 THEN 'First'
         WHEN EXTRACT(quarter FROM datum) = 2 THEN 'Second'
         WHEN EXTRACT(quarter FROM datum) = 3 THEN 'Third'
         WHEN EXTRACT(quarter FROM datum) = 4 THEN 'Fourth'
       END AS quarter_name,
       EXTRACT(year FROM datum) AS year_actual,
       EXTRACT(isoyear FROM datum) AS year_actual_iso,
       datum +(1 -EXTRACT(isodow FROM datum))::INT AS first_day_of_week,
       datum +(7 -EXTRACT(isodow FROM datum))::INT AS last_day_of_week,
       datum +(1 -EXTRACT(DAY FROM datum))::INT AS first_day_of_month,
       (DATE_TRUNC('MONTH',datum) +INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month,
       DATE_TRUNC('quarter',datum)::DATE AS first_day_of_quarter,
       (DATE_TRUNC('quarter',datum) +INTERVAL '3 MONTH - 1 day')::DATE AS last_day_of_quarter,
       TO_DATE(EXTRACT(isoyear FROM datum) || '-01-01','YYYY-MM-DD') AS first_day_of_year,
       TO_DATE(EXTRACT(isoyear FROM datum) || '-12-31','YYYY-MM-DD') AS last_day_of_year,
       TO_CHAR(datum,'mmyyyy') AS mmyyyy,
       TO_CHAR(datum,'mmddyyyy') AS mmddyyyy,
       CASE
         WHEN EXTRACT(isodow FROM datum) IN (6,7) THEN TRUE
         ELSE FALSE
       END AS weekend_indr
FROM (SELECT '2018-01-01'::DATE+ SEQUENCE.DAY AS datum
      FROM GENERATE_SERIES (0,3650) AS SEQUENCE (DAY)
      GROUP BY SEQUENCE.DAY) DQ
ORDER BY 1;

CREATE VIEW dw.salesmart AS 
  SELECT s.order_id, s.quantity, s.extended_price_amount, s.discount_amount, s.sold_amount,
          c.company_name, c.contact_name, c.contact_title, c.customer_city, c.customer_country,
          c.customer_region, c.customer_postal_code, e.employee_name, e.employee_title,
          p.product_name, p.discontinued, p.category_name,
          d.*
  FROM dw.factsales s
  JOIN dw.dimcustomer c USING (customer_key)
  JOIN dw.dimemployee e USING (employee_key)
  JOIN dw.dimproduct p USING (product_key)
  JOIN dw.dimdate d on d.date_dim_id = s.order_date_key
;

SELECT quarter_name, count(order_id) as orders, sum(quantity) as quantity, 
       sum(round(sold_amount::numeric,2)) as sold_amount
FROM dw.salesmart
GROUP BY quarter_name;

SELECT day_name, count(order_id) as orders, sum(quantity) as quantity,
  	   sum(round(sold_amount::numeric,2)) as sold_amount
FROM dw.salesmart
GROUP BY day_name;

SELECT count(order_id) as orders, sum(quantity) as quantity, 
       sum(round(sold_amount::numeric,2)) as sold_amount,
       week_of_year
FROM dw.salesmart
WHERE day_of_week = 2
GROUP BY week_of_year