Click rank

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
insights as (
  select
    insight_id,
    {COMPONENT}
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and insight_type = '{INSIGHT-TYPE}'
)
select
  ins.{COMPONENT},
  round(
    div0(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.{COMPONENT}
order by
  ins.{COMPONENT};

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
insights as (
  select
    insight_id,
    lower(query_expression) as query_expression
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and insight_type = 'search'
    and nullif(query_expression, '') is not null
)
select
  ins.query_expression,
  round(
    div0(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.query_expression
having
  click_rank > 0
order by
  click_rank asc
limit
  10;

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
insights as (
  select
    ins.insight_id,
    con.name as ec_listing
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS ins
    inner join COVEO_CORE_MODEL_V001.COMMERCE.CONFIGS con on con.config_id = ins.config_id
  where
    ins.start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and ins.insight_type = 'listing'
)
select
  ins.ec_listing,
  round(
    div0(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.ec_listing
having
  click_rank > 0
order by
  click_rank asc
limit
  10;

set start_timestamp = '<START-DATE>'; -- The start date and time. Replace with the desired timestamp.
set end_timestamp = '<END-DATE>';   -- The end date and time. Replace with the desired timestamp.

with clicked_impressions as (
  select
    insight_id,
    count(distinct click_event_id) as click_count,
    sum(position) as click_position_sum
  from
    COVEO_CORE_MODEL_V001.COMMON.CLICKED_IMPRESSIONS
  where
    clicked_impression_timestamp between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
  group by
    insight_id
),
insights as (
  select
    insight_id,
    origin as recommendation_component
  from
    COVEO_CORE_MODEL_V001.COMMON.INSIGHTS
  where
    start_time between dateadd(day, -2, $start_timestamp :: date) and $end_timestamp
    and insight_type = 'recommendation'
)
select
  ins.recommendation_component,
  round(
    div0(
      sum(ci.click_position_sum),
      sum(ci.click_count)
    ),
    2
  ) as click_rank
from
  insights ins
  left join clicked_impressions ci on ins.insight_id = ci.insight_id
group by
  ins.recommendation_component
having
  click_rank > 0
order by
  click_rank asc
limit
  10;