gistfile1.txt

--old logic 
DROP TABLE IF EXISTS test.fisrt_activation_date;
WITH
  item_simple_legacy As (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION by item_code, webshop_code ORDER BY item_code, webshop_code, COALESCE(item_activation_date,'9999-12-31') ASC) as row_nr
  FROM public.item_legacy
  )
  SELECT c.meta_sku AS item_code,
         c.webshop_code web_shop_code,
COALESCE(CAST(c.item_attributes_first_activated_at AS TIMESTAMP),
    il.item_first_activation_date,
    ch.first_activation_date) first_activation_date into test.fisrt_activation_date
  FROM public.item_catalog c
 LEFT JOIN public.item_code_mapping icm ON c.item_code = icm.item_code_sap
LEFT JOIN item_simple_legacy  il ON
COALESCE (icm.item_code_navision, c.item_code) = il.item_code AND c.webshop_code = il.webshop_code AND il.row_nr = 1
left join
(SELECT
               item_code,
               webshop_code,
               MIN(CASE WHEN ch.active_flag THEN ch.period_date END) AS first_activation_date,
               MIN(CASE WHEN ch.sellable_flag THEN ch.period_date END) AS first_sellable_date
             FROM public.item_catalog_history ch
             GROUP BY item_code, webshop_code) ch
ON c.item_code = ch.item_code AND c.webshop_code = ch.webshop_code;

--items which has mismatched first activation date and not legacy items 
drop table if EXISTS  test.mfad;
select dt,item_code into test.mfad from (
select date(first_activation_date) dt,item_code from test.fisrt_activation_date
where web_shop_code = 'DE'
except
select date(item_first_activation_date) dt ,item_code
from star_analytical.d_item) a;


select count(*) from test.mfad  where mfad.item_code like '0%'
and dt is not null and mfad.item_code not like '%P'limit 200

select * from star_analytical.d_item where item_code = '000000001000124844';

select item_code,webshop_code,item_attributes_first_activated_at
from public.item_catalog where item_code = '000000001000124844';

select min(period_date),webshop_code,item_code from public.item_catalog_history
where item_code = '000000001000124844'
and active_flag
GROUP BY webshop_code,item_code
order by webshop_code,item_code