Analytics and market data

Counts by the minute, distinct users, funnels, bars and VWAP: one statement each, over the rows as they were written.

Four things in the select list make these work. An item is an expression — px * qty, or an aggregate of one, sum(px * qty), or an expression over aggregates, sum(px * qty) / sum(qty). group takes several keys, and bucket(at, 1m) makes a time one. count(distinct user) counts values, exactly. And first(px by at) and last(px by at) take the value of the earliest and latest row of a group. See Aggregates for the rules.

Every FenecQL block on this page is run, statement by statement, by a test (crates/fenec-core/tests/analytics_docs.rs), and each -- → x is the first value of the first row it checks. The costs are make analytics-bench on an M1 Air, in process, over a million events and a million ticks; over HTTP a request adds its round trip.

Counts by the minute and distinct users are what an app often keeps in Redis — INCR on a key a minute, PFADD and PFCOUNT. Here they are statements over the events as they were written; the other patterns, unique visitors among them, are recipes of Instead of Redis.

These recipes make a whole application in examples/analytics: a 572-byte tracker, beacons written once however often they are sent, rollups kept from the change stream exactly once, a dashboard that reads the raw events for a day and the rollups past it, a site a tenant, and the market data below with live prices — with its tests, Lighthouse scores and measurements at ten million events.

Analytics

Raw events, one row each. at takes @sorted — or @ttl(30d), which is an ordered index whose rows expire — so a time range reads only its rows.

create collection events (user int @hash, name text @hash, at timestamp @sorted, country text, props json)
put events [{user: 1, name: "view", at: "2026-05-03T09:00:10Z", country: "TR", props: {plan: "pro", v: 3}}, {user: 1, name: "cart", at: "2026-05-03T09:02:00Z", country: "TR", props: {plan: "pro", v: 1}}, {user: 1, name: "buy", at: "2026-05-03T09:05:30Z", country: "TR", props: {plan: "pro", v: 9}}, {user: 2, name: "view", at: "2026-05-03T09:01:00Z", country: "US", props: {plan: "free", v: 2}}, {user: 2, name: "view", at: "2026-05-03T10:15:00Z", country: "US", props: {plan: "free", v: 2}}, {user: 3, name: "view", at: "2026-05-03T10:20:00Z", country: "DE", props: {plan: "free", v: 1}}, {user: 3, name: "buy", at: "2026-05-03T10:40:00Z", country: "DE", props: {plan: "free", v: 5}}, {user: 1, name: "view", at: "2026-05-04T08:00:00Z", country: "TR", props: {plan: "pro", v: 1}}]

Rollups

Counts by the hour, or the minute: bucket truncates a timestamp to the interval it falls in, in UTC — ms, s, m, h, d from the epoch, w from a Monday, mo and y by the calendar. Name it with as and group by the name.

get events select bucket(at, 1h) as hour, count(*) as n where at >= "2026-05-03" and at < "2026-05-04" group hour   -- → "2026-05-03T09:00:00Z"
get events select count(*) as n, bucket(at, 1h) as hour where at >= "2026-05-03" and at < "2026-05-04" group hour   -- → 4: the first hour's
get events select count(*) as n, name, bucket(at, 1d) as day group name, day order n desc, name   -- → 4: views on the 3rd
get events select sum(props.v) as v, props.plan group props.plan order v desc   -- → 14: a json path summed

A count by bucket of the field a range is on reads its ordered index and no row: per-minute counts over a day of a million events — 1 441 groups of 143 000 rows — take 4.0 ms, where grouping by a minute field written at ingest, which reads the rows, takes 19.5. A raw collection under @ttl keeps a few days; a job that runs these statements writes the rollups into a collection without one before the raw rows go.

A day by a zone's clock

bucket counts in UTC; a third argument counts by a zone's clock — an IANA name, or an offset written out. A day then starts at local midnight: Istanbul's 3rd of May began at 21:00 UTC on the 2nd.

get events select bucket(at, 1d, "Europe/Istanbul") as day, count(*) as n group day order day   -- → "2026-05-02T21:00:00Z"
get events select count(*) as n, bucket(at, 1d, "Europe/Istanbul") as day group day order day   -- → 7: the 3rd in Istanbul
get events select count(*) as n, bucket(at, 1h, "+05:30") as hour where at < "2026-05-04" group hour order hour   -- → 4: 14:00 to 15:00 in India

Where daylight time begins or ends a local day is 23 or 25 hours, and an offset written into the statement — bucket(at + 10800000, 1d) — cannot follow it. Berlin's clocks went back on the 25th of October 2026, so that day holds the hour its clock showed twice.

create collection beats (at timestamp @sorted)
put beats [{at: "2026-10-24T22:30:00Z"}, {at: "2026-10-25T22:30:00Z"}, {at: "2026-10-26T00:30:00Z"}]
get beats select bucket(at, 1d, "Europe/Berlin") as day, count(*) as n group day order day   -- → "2026-10-24T22:00:00Z"
get beats select count(*) as n, bucket(at, 1d, "Europe/Berlin") as day group day order day   -- → 2: one day of 25 hours
get beats select count(*) as n, bucket(at, 1d) as day group day order day   -- → 1: in UTC, three days

A page passes its own zone as a parameter — Intl.DateTimeFormat().resolvedOptions().timeZone in a browser. The rules are IANA's from 2000 on; a time before it in a named zone is refused, and an offset answers any time. Counted off the ordered index, a week's days of a million events by Istanbul's clock take 29 to 34 ms against 23 to 27 in UTC, where the offset written in reads every row, 133 to 154; its hours by Berlin's clock 31 against 25; with a filter that reads the rows, 94 to 103 against 91 to 98 (make analytics-bench).

A rollup kept as the events come is a row a key, made at its first count and added to after: if absent else set is that upsert, one statement for a page of keys, the set reading the row it finds and new. the document put. With the page as a parameter, put hourly $1 if absent else set {n: n + new.n}, its text is the same whatever the page holds.

create collection hourly (key text @unique, n int)
put hourly [{key: "2026-05-03T09|view", n: 2}, {key: "2026-05-03T09|buy", n: 1}] if absent else set {n: n + new.n}   -- → 2: made
put hourly [{key: "2026-05-03T09|view", n: 3}, {key: "2026-05-03T10|view", n: 2}] if absent else set {n: n + new.n}   -- → 2: one added to, one made
get hourly select n where key = "2026-05-03T09|view"   -- → 5

A page of 1 000 keys, half of them new, is one statement in 1.09 ms in process, where reading which keys exist and writing the new rows and the old ones over took 1.49 ms and a round trip more.

Distinct users

count(distinct f) holds each value once a group, exactly, up to a million values a query — about 70 MB — and refuses a query past that rather than count short.

get events select count(distinct user) as users, bucket(at, 1d) as day group day   -- → 3: on the 3rd
get events select count(distinct user) where at >= "2026-05-04"   -- → 1
get events select user, count(distinct bucket(at, 1d)) as days group user order days desc, user   -- → 1: active two days

Distinct users per hour over seven days of a million events, 169 groups: 125 ms. Before, a query an hour listed the users and the client counted them: 7.7 s.

Past a million values, or to add days up, count approximately: approx_count_distinct(f) is HyperLogLog, 2¹⁴ registers — 16 KB a group, sparse while a group has few values — with a standard error of 0.81%, whatever the count. hll_accumulate(f) answers the sketch itself as bytes, to keep a day's in a rollup row; hll_combine(s) merges sketches and hll_estimate(s) reads a count off one, so a month is its days' sketches merged — not their counts added, which counts a user of two days twice. Sketches are the same bytes wherever made, the browser's or a server's. A query's sketches are held to 64 MB, 4 096 dense groups, and refused past that.

get events select approx_count_distinct(user) as users   -- → 3
get events select hll_estimate(hll_accumulate(user)) as users where at >= "2026-05-04"   -- → 1
get events select bucket(at, 1d) as day, hll_accumulate(user) as users group day   -- → "2026-05-03T00:00:00Z"

Per hour over the million events it takes 121 ms, the furthest of the 169 hours 1.3% off; over thirty sketches of 200 000 values the error's root mean square is 0.77%.

Funnels

How many users reached each step, and how many of the viewers bought:

get events select count(distinct user) as users, name where name in ["view", "cart", "buy"] group name order users desc   -- → 3: viewed
get events select count(distinct user) where name = "buy" and user in (get events select user where name = "view")   -- → 2: viewed and bought

The inner get is a list of up to 100 000 values; past that ask from the users' side with lookup … required (Subqueries). An ordered funnel — a view, then a buy within the hour — compares each user's first of each: having keeps the groups a condition holds for, over their keys, the list's columns by name and any aggregate, and count after it counts them rather than send a row a user.

get events select user, min(case when name = "view" then at end) as viewed, min(case when name = "buy" then at end) as bought where name in ["view", "buy"] group user having bought >= viewed and bought <= viewed + 3600000 count   -- → 2
get events select user, count(*) as n group user having n >= 3 order n desc   -- → 1

Retention

Each user's first day is their cohort, and the days they came back are a group of two keys. The table of cohorts by days since is folded from these two answers on the client.

get events select user, min(bucket(at, 1d)) as cohort group user   -- → 1
get events select user, bucket(at, 1d) as day, count(*) as events group user, day   -- → 1

Market data

Ticks, one row each, written as they come — not always in time order.

create collection ticks (sym text @hash, at timestamp @sorted, px float, qty int)
put ticks [{sym: "S07", at: "2026-05-03T10:00:05Z", px: 100.25, qty: 10}, {sym: "S07", at: "2026-05-03T10:00:40Z", px: 101.5, qty: 5}, {sym: "S07", at: "2026-05-03T10:00:20Z", px: 99.75, qty: 20}, {sym: "S07", at: "2026-05-03T10:01:10Z", px: 102.25, qty: 1}, {sym: "S07", at: "2026-05-03T10:01:50Z", px: 101.75, qty: 4}, {sym: "S08", at: "2026-05-03T10:00:30Z", px: 50.5, qty: 100}, {sym: "S08", at: "2026-05-03T10:01:30Z", px: 51.25, qty: 50}]

Bars

Open, high, low, close and volume, a bar a minute. first(px by at) is the price of the bar's earliest tick, whatever order they were written in; a tie in at goes to the row written first, and last's to the row written last. Without by they take the order the rows were written in.

get ticks select bucket(at, 1m) as bar, first(px by at) as open, max(px) as high, min(px) as low, last(px by at) as close, sum(qty) as volume where sym = "S07" group bar   -- → "2026-05-03T10:00:00Z"
get ticks select first(px by at) as open, bucket(at, 1m) as bar where sym = "S07" group bar   -- → 100.25
get ticks select last(px by at) as close, bucket(at, 1m) as bar where sym = "S07" group bar   -- → 101.5
get ticks select max(px) - min(px) as range where sym = "S07"   -- → 2.5

An hour of one-minute bars of one symbol out of a million ticks over 50: 1.7 ms in process, 2.3 over HTTP. Before, open and close were two queries a bar, 120 for the hour: 195 ms in process, 278 over HTTP.

VWAP

The volume-weighted average price is an expression over two aggregates:

get ticks select sum(px * qty) / sum(qty) as vwap, sym group sym order sym   -- → 100.35625
get ticks select sym, sum(px * qty) as notional, sum(qty) as volume group sym order notional desc   -- → "S08"

Every symbol's over a million ticks: 118 ms. Before, the client read every tick out and summed them: 173 ms in process, and a million rows over the wire.

The latest price

get ticks select last(px by at) as px, sym group sym order sym   -- → 101.75

Every symbol's, a scan of the million ticks: 108 ms, against 129 for a query a symbol (where sym = $1 order at desc limit 1, each sorting its symbol's 20 000 rows). A page that shows it all the time keeps a row a symbol instead:

create collection quotes (sym text @unique, px float, high float, low float)
put quotes {sym: "S07", px: 101.75, high: 101.75, low: 101.75} if absent   -- → 1
set quotes {px: 99.5, high: greatest(high, 99.5), low: least(low, 99.5)} where sym = "S07"   -- → 1
get quotes select low where sym = "S07"   -- → 99.5
get quotes select case when px < high then "below" else "at the high" end as where_it_is where sym = "S07"   -- → "below"

greatest and least pass a null over, and case works out only the branch it takes.