Time series (SQL)

A Time series panel plots one or more lines — or stacked areas — over the dashboard's time range. Reach for it whenever the question is “how did this change over time”: daily interaction counts, hourly error rates, a running total.

Query convention

Two columns, aliased bucket and count:

SELECT toDate(timestamp) AS bucket, count() AS countFROM chomper.eventsWHERE timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})GROUP BY bucket ORDER BY bucket

Use toDate(...) for day-level buckets. Switch to toStartOfHour(...) (or similar) once the time of day itself is part of the story, not just the date.

Splitting into multiple series — and stacking them

Add a third column aliased series, and the chart automatically splits into one line per distinct value instead of a single total. Turning on the panel's Stacked option then stacks them instead of overlapping them. A bucket with no rows for a given series is filled in as zero rather than left blank, so a quiet day for one series doesn't visually dent the stacked total.

Example: Interactions by type

SELECT toDate(timestamp) AS bucket, event_class AS series, count() AS countFROM chomper.eventsWHERE event_class IN ('call', 'chat', 'email', 'visit')  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})GROUP BY bucket, seriesORDER BY bucket, series

event_class becomes the series column, so the four interaction channels — call, chat, email, visit — each get their own stacked area instead of one flat total.

“Interactions by type” — stacked by channel, with per-series Max/Sum legend stats enabled.

Reading the chart

The legend below the chart is a sortable table — enable Min/Max/Delta/Sum/p95 in the panel's display options and click a column header to sort by it. Clicking a legend row isolates that one series, dimming the rest; Ctrl/Cmd-click adds another series into the selection, so two of several can be compared side by side.

Drilling down into a bucket

Clicking a point on the chart runs the drill-down query with that point's date bound as {bucket:String}. This panel's drill-down query normalizes two differently-shaped fields into one readable reason column — a call's tags array vs. a chat/email's single category string:

multiIf(  event_class = 'call', arrayStringConcat(JSONExtract(metadata, 'tags', 'Array(String)'), ', '),  JSONExtractString(metadata, 'category')) AS reason

Worth reusing any time a drill-down spans event classes that don't share an identical metadata shape.