web_session.sql

with t as (select date,
	    clientid, 
	    arrayJoin(JSONExtract(assumeNotNull(hour_info),'Array(String)')) as hour, 
	    arrayJoin(extractAll(assumeNotNull(productSku),'"(\\w+)"')) as product 
		from {{ ref("src_for_web_sessions") }})
select CAST(t.date as Date) as "Дата", t.hour as "Час", item.`ссылка` as "Товар", count(*) as "Количество просмотров"  from t	
join (select * from {{source('bq','`cifra_товары`')}} ) as item on item.`код sku` = t.product
group by t.date, t.hour, item.`ссылка`, t.clientid
UNION ALL
select CAST(t.date as Date) as "Дата", t.hour as "Час", item.`ссылка` as "Товар", count(*) as "Количество просмотров"  from t
join (select * from {{source('bq','`cifra_товары`')}} limit 1 by `код группы sku`) as item on item.`код группы sku` = t.product
group by t.date, t.hour, item.`ссылка`, t.clientid