page-visits

Visits grouped by date

select toDate(timestamp) as date, sum(visit_count) as visits from page_events where date >= '2024-09-01' group by date;

Visits grouped by date and channel

select toDate(timestamp), channel_id, sum(visit_count) from page_events where toDate(timestamp) >= '2023-06-15' group by toDate(timestamp), channel_id;

Another page view statistics

todo: find out why do we need two tables for similar data

select sum(view_count), toStartOfDay(toDateTime64(timestamp, 0)) as day from window_events group by day order by day;

Export

clickhouse-client --host=127.0.0.1 --port=9000 --user=default --database=statistics --query "select toDate(timestamp) as date, sum(visit_count) as visits from page_events where date >= '2024-03-01' group by date;" --format CSV > /tmp/page_visits.csv