-- SET @pbi_datetime = '2018-01-01 10:00:10'

-- POPULATE fact_order_cancel
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from fact_order_cancel;
insert into `fact_order_cancel`
(order_id, discount_canceled, shipping_canceled,
 tax_canceled, subtotal_canceled, total_canceled, `stg_inserted_at`, `inserted_at`)
  select
    o.order_id,
    abs(o.discount_canceled),
    o.shipping_canceled,
    o.tax_canceled,
    o.subtotal_canceled,
    o.total_canceled,
    o.inserted_at as stg_inserted_at,
    @db_date_time as inserted_at
  from bim_stg_canceled_order o
    left join bim_stg_canceled_order o2
      on o.order_id = o2.order_id
      and o2.pk > o.pk
    left join fact_order_cancel f
      on f.order_id = o.order_id
WHERE o.inserted_at >= @max_inserted_at
      AND f.order_id IS NULL
      AND o2.pk is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}fact_order_cancel{{/IF_ANALYZE}}
