Untitled

select
toDate(utc_event_time, 'Europe/Moscow') AS event_date_MSC,
decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_source')) as utm_source,
decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_medium')) as utm_medium,
decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_campaign')) as utm_campaign,
decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_term')) as utm_term,
decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_content')) as utm_content,
splitByString('-id-', decodeURLComponent(extractURLParameter(traffic_source.page_url[1], 'utm_campaign')))[1] as utm_seller
   ,splitByChar('|', ecom.affiliation)[1] as wb_supplier
   ,ecom.nm
   ,counter_id
   ,countIf(event_type = 'ec.purchase') as cnt_purch
   ,countIf(event_type = 'ec.add_to_cart') as cnt_add_to_cart
   ,countIf(event_type = 'ec.view_item') as cnt_view_item
   ,sum(curr(ecom.grand_total100, ecom.currency, utc_event_date)) as sum_purch
from analytics.events
array join ecom
where 1=1
and utc_event_date in( toDate('2023-10-15 23:30:00'),toDate('2023-10-16 00:00:00')) and (utc_event_time >='2023-10-15 23:30:00' and utc_event_time <'2023-10-16 00:00:00')
--and (utc_event_time >='2023-10-15 14:00:00' and utc_event_time<'2023-10-15 14:30:00')
  and counter_id in (4,50,1000,100)
  and event_type in( 'ec.add_to_cart','ec.purchase','ec.view_item')

 AND match(utm_campaign, '^\d+-id-')
group by event_date_MSC, utm_source,utm_medium,utm_campaign,utm_term,utm_content,utm_seller,wb_supplier,ecom.nm, counter_id;