kafka-ingest-concept/templates/create_mvs.sql
kjuulh f345a956c9
feat: add iceberg with spark
Signed-off-by: kjuulh <contact@kjuulh.io>
2024-08-05 23:23:34 +02:00

23 lines
502 B
SQL

-- The number of clicks on the ad within one minute after the ad was shown.
create materialized view m_click_statistic as
select
count(user_id) as clicks_count,
ad_id
from
ad_source
where
click_timestamp is not null
and impression_timestamp < click_timestamp
and impression_timestamp + interval '1' minute >= click_timestamp
group by
ad_id;
create materialized view clicks as
select
user_id,
ad_id,
click_timestamp,
impression_timestamp
from
ad_source;