ClickHouse table + materialized‑view writer

Base table

CREATE TABLE meridian.raw_events (
  timestamp   DateTime64(3),
  session_id  String,
  event_type  LowCardinality(String),
  payload     String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (session_id, timestamp);

Materialized view

CREATE MATERIALIZED VIEW meridian.session_metrics
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (session_id, minute)
AS SELECT
  session_id,
  toStartOfMinute(timestamp) AS minute,
  countIf(event_type = 'page_view') AS page_views,
  countIf(event_type = 'click')     AS clicks,
  min(timestamp)                    AS first_seen,
  max(timestamp)                    AS last_seen
FROM meridian.raw_events
GROUP BY session_id, minute;

Usage notes

• Insert into raw_events directly — the view populates automatically.
• Query session_metrics with FINAL to collapse merged rows.
• Tune PARTITION BY granularity based on retention window.