CREATE OR REPLACE TABLE `live_nahrin_reports.Data_PDPRadicalPrediction_logistic_reg_v1_11M`
as
SELECT
  count(distinct session_id) as sessions
  , count(distinct order_id) as purchases
  , ifnull((select string_agg(v order by v) from unnest(feature_1) as v where v != ''),'none') as feature_1
  , ifnull((select string_agg(v order by v) from unnest(feature_2) as v where v != ''),'none') as feature_2
  , ifnull((select string_agg(v order by v) from unnest(feature_3) as v where v != ''),'none') as feature_3
  , ifnull((select string_agg(v order by v) from unnest(feature_4) as v where v != ''),'none') as feature_4
  , ifnull((select string_agg(v order by v) from unnest(feature_5) as v where v != ''),'none') as feature_5
  , ifnull((select string_agg(v order by v) from unnest(feature_6) as v where v != ''),'none') as feature_6
  , ifnull((select string_agg(v order by v) from unnest(feature_7) as v where v != ''),'none') as feature_7
  , ifnull((select string_agg(v order by v) from unnest(feature_8) as v where v != ''),'none') as feature_8
  , ifnull((select string_agg(v order by v) from unnest(feature_9) as v where v != ''),'none') as feature_9
  , ifnull((select string_agg(v order by v) from unnest(feature_10) as v where v != ''),'none') as feature_10
from
(
SELECT

  s.session_id
  , any_value(s.order_id) as order_id
  , array_agg(distinct case when feature = 1 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_1
  , array_agg(distinct case when feature = 2 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_2
  , array_agg(distinct case when feature = 3 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_3
  , array_agg(distinct case when feature = 4 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_4
  , array_agg(distinct case when feature = 5 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_5
  , array_agg(distinct case when feature = 6 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_6
  , array_agg(distinct case when feature = 7 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_7
  , array_agg(distinct case when feature = 8 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_8
  , array_agg(distinct case when feature = 9 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_9
  , array_agg(distinct case when feature = 10 then ifnull(concat(v.name, '-', v.value), '-') else '' end) as feature_10

FROM `live_nahrin_views.pdp_radical_tracking_markers` 
join unnest(values) as v
join unnest(sessions) as sid
join  `live_nahrin_reports.session_primary` as s on s.session_id = sid
group by session_id, feature
)
group by feature_1, feature_2, feature_3, feature_4, feature_5, feature_6, feature_7, feature_8, feature_9, feature_10
;


CREATE OR REPLACE MODEL `live_nahrin_reports.Model_PDPRadicalPrediction_logistic_reg_v1_11M`
	OPTIONS(
	  model_type='logistic_reg'
	  , CATEGORY_ENCODING_METHOD = 'DUMMY_ENCODING'
	  , CALCULATE_P_VALUES = TRUE, 
	  ENABLE_GLOBAL_EXPLAIN = TRUE
	) AS

SELECT

  (IF(purchases >= it,1,0)) AS label
  , feature_1, feature_2, feature_3, feature_4, feature_5, feature_6, feature_7, feature_8, feature_9, feature_10

FROM 
  `live_nahrin_reports.Data_PDPRadicalPrediction_logistic_reg_v1_11M`
  join unnest(GENERATE_ARRAY(1,sessions)) as it

;