Untitled

WITH impression_data AS
(SELECT
      DISTINCT
      visitor_id,
      TRY_CAST(home_data.field0 AS varchar) AS position,
      TRY_CAST(json_extract(home_data.field1, '$.component_id') AS varchar) AS component_id,
      CASE
        WHEN  TRY_CAST(json_extract(home_data.field1, '$.component_id') AS int) IN (7359,7360,7361,7362,7363,7364,7365,7366,7378,7382,7383,7381,7384,7385,7386,7387,7388,7389,7390,7391,7392,7393,7394,7395,7396,7397,7398,7399,7400,7401,8198)
        THEN 'personalised'
        ELSE 'non_personalised'
      END AS personalisation,
      TRY_CAST(json_extract(home_data.field1, '$.ga_data.info.unique_token') AS varchar) AS unique_token,
      COALESCE(TRY_CAST(json_extract(home_data.field1, '$.ga_data.variant') AS VARCHAR),
        TRY_CAST(json_extract(home_data.field1, '$.variant') AS VARCHAR)) AS algo


    FROM (
      select visitor_id,ZIP(SEQUENCE(1, CARDINALITY(json_arr)), json_arr) as widgets_with_position FROM (
SELECT  visitor_id, user_journey, array_agg(offset order by (timestamp)) as offset_arr,
flatten(array_agg(json_arr order by (timestamp))) as json_arr
FROM (
SELECT *,   sum( case when offset = 0 then 1 else 0 end) over (partition by visitor_id order by timestamp) user_journey
FROM (
            SELECT visitor_id, timestamp,
            CASE WHEN cast(json_extract(uri_args, '$.offset') AS int) IS NULL THEN 0
              ELSE cast(json_extract(uri_args, '$.offset') AS int) END as offset,

            TRY_CAST(json_extract(response_body, '$.data.widgets') AS array(json)) as json_arr

            FROM nt_landing.raw_logs
            where year = {eval_year} and month = {eval_month:02d} and day = {eval_day:02d} and
            uri_categorization='home_page'
            AND lower(CAST(request_headers as varchar)) LIKE '%"x-platform":"android%'


            and request_uri IN ('/v9/home_page/initialize' , '/v8/home_page/initialize' , '/v7/home_page/initialize')
            )
    )
    group by visitor_id, user_journey
    order by user_journey
)
      )
     CROSS JOIN UNNEST(widgets_with_position) AS t(home_data)

),
click_data as
(SELECT clk.visitorid, position, clk.component_id, widget_button,clk.algo
FROM
(SELECT
    DISTINCT

	visitorid,


	CASE
	    WHEN lower(event) = 'click on widget' THEN 'widget'
	    WHEN lower(event) = 'banner click' THEN 'banner'
	    WHEN lower(event) IN ('medicines', 'lab tests', 'consult doctors', 'health products',
	                          'covid support', 'articles', 'health records', 'pill reminder') THEN 'vertical_entry'
	END AS widget_button,

	TRY_CAST(json_extract(properties__label, '$.ga_data.info.component_id') as varchar) AS component_id,
	TRY_CAST(json_extract(properties__label, '$.ga_data.info.unique_token') as varchar) AS unique_token,
	COALESCE(TRIM(lower(cast(json_extract(properties__label, '$.variant') as varchar))), TRIM(lower(cast(json_extract(properties__label, '$.ga_data.variant') as varchar)))) as algo

FROM nt_stg.pr_rudder_andrd_prod
WHERE year = {eval_year} AND month = {eval_month:02d} AND day = {eval_day:02d}

    AND event_category = 'Homepage'
    AND lower(event) IN ('click on widget', 'banner click', 'medicines', 'lab tests', 'consult doctors','health products', 'covid support', 'articles', 'health records', 'pill reminder')) clk INNER JOIN impression_data
    ON clk.component_id = impression_data.component_id AND
    clk.unique_token = impression_data.unique_token)


SELECT DISTINCT 'app' platform,imps.algo, imps.component_id,imps.personalisation, imps.position, imps.impressions, clks.clicks, (100.0*CAST(clks.clicks as double))/CAST(imps.impressions as double) AS positional_ctr
FROM
(SELECT component_id, position,algo,personalisation, COUNT(DISTINCT visitor_id) impressions
FROM impression_data group by 1,2,3,4) imps LEFT JOIN
(SELECT component_id, position,algo, COUNT(DISTINCT visitorid) clicks FROM click_data group by 1,2,3) clks
ON imps.component_id = clks.component_id AND imps.position = clks.position AND imps.algo = clks.algo