WITH days_array AS
(
  SELECT
    GENERATE_DATE_ARRAY('2019-11-01', '2019-12-15', INTERVAL 1 DAY) AS days
),
days AS
(
  SELECT day FROM days_array,
  UNNEST(days) as day
),

shipments_statuses AS
(
  SELECT
    id, shipment_id, user_id, type, created_at
    ,SAFE_CAST(REGEXP_EXTRACT(JSON_EXTRACT(data, '$.status_id'), r'[\d]+') AS INT64) AS status_id
  FROM `bigdata-awok.als_awok_logistics_rt.aw_shipment_history_log`
    WHERE (EXTRACT(DATE FROM created_at) >= DATE("2019-11-01")
    AND type = "status_change")
),
shipment_history AS
(
  SELECT *, EXTRACT(DATE FROM created_at) AS created_at_date FROM shipments_statuses
),
shipments_statuses_per_days AS
(
  SELECT shipment_history.shipment_id, shipment_history.status_id, shipment_history.created_at_date
  FROM shipment_history JOIN (
    SELECT shipment_id, created_at_date, MAX(created_at) AS maximum_timestamp_per_day
    FROM shipment_history
    GROUP BY shipment_id, created_at_date
  ) AS nested
  ON shipment_history.shipment_id  = nested.shipment_id
    AND shipment_history.created_at_date = nested.created_at_date
    AND shipment_history.created_at = nested.maximum_timestamp_per_day
)
,
shipments_statuses_per_days_and_per_statuses AS
(
  SELECT
    created_at_date
    ,COUNT(status_4) AS status_4
    ,COUNT(status_5) AS status_5
    ,COUNT(status_6) AS status_6
    ,COUNT(status_18) AS status_18
    ,COUNT(status_19) AS status_19
    ,COUNT(status_136) AS status_136
  FROM (
    SELECT
      shipment_id
      ,created_at_date
      ,CASE WHEN status_id = 4
        THEN status_id
      END AS status_4
      ,CASE WHEN status_id = 5
        THEN status_id
      END AS status_5
      ,CASE WHEN status_id = 6
        THEN status_id
       END AS status_6
      ,CASE WHEN status_id = 18
        THEN status_id
       END AS status_18
      ,CASE WHEN status_id = 19
        THEN status_id
       END AS status_19
      ,CASE WHEN status_id = 136
        THEN status_id
       END AS status_136
    FROM
      shipments_statuses_per_days)
  GROUP BY created_at_date
)
SELECT day, status_4, status_5, status_6, status_18, status_19, status_136
FROM days
LEFT JOIN shipments_statuses_per_days_and_per_statuses
ON days.day = shipments_statuses_per_days_and_per_statuses.created_at_date