Appearance
Operators
The ten operators, what each does to the rows that reach it, and one example of each.
| Operator | Does |
|---|---|
where | keeps the rows whose condition is true |
project | keeps only the columns you list |
extend | adds columns, keeping the rest |
expand | one row per element of an array |
summarize | aggregates, optionally per group |
join | pairs rows with a second pipeline on keys |
union | appends a second pipeline's rows |
order | sorts, stably |
take | keeps the first n rows |
distinct | keeps the first row of each distinct combination |
where
Keeps a row only when the condition is exactly true. false drops it and so does null, so a reverted call — which yields null — never passes a where. To keep rows whose condition is unknown, where not coalesce(paused, false).
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH at latest-21600..latest every 7200 as s
| where not s.isStakingPaused()
| project { block: $block, buffered: format(s.getBufferedEther(), 18) }project
Keeps the listed columns and drops everything else. The record form { name: expr, … } names each column; a bare name keeps a column as it is.
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH as s
| extend totalShares = s.getTotalShares()
| project { block: $block, chain: $chain, totalShares }extend
Adds a column and keeps the ones already there. Use it when you want one more value rather than a new set of columns, or to name something once and use it twice.
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH as s
| extend pooled = s.getTotalPooledEther(), shares = s.getTotalShares()
| project { block: $block, ethPerShare: todecimal(pooled) / shares }expand
Turns an array into one row per element. as names the element and index its position, counted from zero. Every other column is repeated on each row, and the alias is still there, so a call on the row's element — r.getStakingModuleIsActive(moduleId) — reads one value per element.
cql
let router = ethereum:0xFdDf38947aFB03C621C71b06C9C70bce73f12999;
from router as r
| expand r.getStakingModuleIds() as moduleId index i
| project { i, moduleId, active: r.getStakingModuleIsActive(moduleId), validators: r.getStakingModuleActiveValidatorsCount(moduleId) }| i | moduleId | active | validators |
|---|---|---|---|
| 0 | 1 | true | 258795 |
| 1 | 2 | true | 5000 |
| 2 | 3 | true | 25246 |
| 3 | 4 | true | 4609 |
summarize
Groups rows and computes one value per group. Write the aggregates first, then the group keys after by; a key can be renamed, by day = bin($timestamp, 1d). With by and no aggregate you get the distinct groups.
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH at latest-21600..latest every 7200 as s
| extend ethPerShare = format(s.getPooledEthByShares(1e18), 18)
| summarize first = first(ethPerShare), last = last(ethPerShare), samples = count() by day = bin($timestamp, 1d)The aggregate functions are sum, avg, min, max, count, count_distinct, first, last, make_list, make_set, arg_max and arg_min — see Functions.
join
Pairs each row with the rows of a second pipeline that have the same key. The second pipeline goes in parentheses; the keys after on, separated by commas. A key written as one name appears once in the output.
kind= | Keeps |
|---|---|
inner | the default: rows with a match on both sides |
leftouter | every left row, right columns null where there is no match |
rightouter | every right row, left columns null where there is no match |
fullouter | both |
leftsemi | left rows that have a match, left columns only |
leftanti | left rows that have no match, left columns only |
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH at latest-21600..latest every 7200 as a
| project { block: $block, ethPerShare: a.getPooledEthByShares(1e18) }
| join kind=leftouter (
from stETH at latest-21600..latest every 7200 as b
| project { block: $block, totalShares: b.getTotalShares() }
) on blockTwo rows match when every key is equal, with the same == a where uses, so 1 meets 1.0. A key that is null on either side never matches: inner drops that row and leftanti keeps it. Under rightouter and fullouter a merged key takes whichever side has a value, so it must have the same type on both sides, down to the width the contracts declare: a uint128 key against a uint256 one is CQL2055 there, although union accepts the pair. When the types differ, or the names do, compare two columns instead; nothing is merged, and both stay:
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
let wstETH = ethereum:0x7f39C581F595B53c5cb19bD0b3f8dA6c935E2Ca0;
from stETH events Transfer at 26_030_000..26_030_200 as t
| join (
from wstETH events Transfer at 26_030_000..26_030_200 as w
) on t.to == w.from
| project { holder: t.to, stethIn: t.value, wstethOut: w.value }A column both sides have that is not a key is kept from both, the right one renamed with the lowest free number: value1, then value2, and $address becomes $address1. Through the right alias, w.value and w.$address name the renamed column whatever its number.
leftanti answers "which of these has no counterpart there". The stETH recipients of 201 blocks that received no wstETH anywhere in that range, by how many transfers they received:
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
let wstETH = ethereum:0x7f39C581F595B53c5cb19bD0b3f8dA6c935E2Ca0;
from stETH events Transfer at 26_030_000..26_030_200 as t
| join kind=leftanti (
from wstETH events Transfer at 26_030_000..26_030_200 as w
) on to
| summarize transfers = count() by to
| order by transfers desc
| take 20| to | transfers |
|---|---|
| ethereum:0x85B78AcA6Deae198fBF201c82DAF6Ca21942acc6 | 10 |
| ethereum:0x889edC2eDab5f40e902b864aD4d7AdE8E412F9B1 | 6 |
| ethereum:0x7f39C581F595B53c5cb19bD0b3f8dA6c935E2Ca0 | 4 |
| ethereum:0x2C0552e5dCb79B064Fd23E358A86810BC5994244 | 2 |
| ethereum:0x1231DEB6f5749EF6cE6943a275A1D3E7486F4EaE | 2 |
| ethereum:0x96e4A3d57B641Bf790E79229c918A7298ee14395 | 2 |
| ethereum:0xeEE8a7B4f547685e07aA7b0592031Ad3Cefabef3 | 2 |
| ethereum:0x0Ab2e96e042a7402aF586BDd1e12020A0a8011e7 | 1 |
| ethereum:0x0038DFB2bBc353Daa6aD22Cb11Ee1D78f41B59cd | 1 |
| ethereum:0x6252A0bc7d264621E2D69747b0c49BB2Ad96BC64 | 1 |
| ethereum:0x6A000F20005980200259B80c5102003040001068 | 1 |
| ethereum:0xaBdf03d7B8548050235685BCBB4C8225dff3ABa9 | 1 |
| ethereum:0x11111605ef067242653c980B8f6F1ffE50305Afe | 1 |
| ethereum:0x625229aAa6E8Cbf693bD749feF75b088ce7bBC64 | 1 |
| ethereum:0x7f699B4bdC1Aa5d88C3c170fef61B773B845fa38 | 1 |
| ethereum:0xd13911D2EeB1D02bC9F4a358Fc4c660aA60a9adC | 1 |
| ethereum:0x950fD558f47E234A2fDe23B7d61f7Ccdbcb4A86F | 1 |
| ethereum:0x88B220102300Aa938Dc348C713135aA6E179Da29 | 1 |
| ethereum:0x88B20b5ede814134911AB2DC2B596e5b3899Da29 | 1 |
| ethereum:0x88b20638D4351C1f47c668569DCF3AB20D27da29 | 1 |
to is a field of both events, so on to compares the two recipients. 66 of the range's 84 stETH transfers went to an address that received no wstETH; kind=leftsemi keeps the other 18. The wstETH contract itself is third: wrapping sends it stETH, and nothing sent it wstETH.
A joined row is read at the left row's block, so a call written after the join, even through the right alias, is answered there. A right row that nothing matched, which rightouter and fullouter add, has no left row and is read at its own block. On a row that lacks one side — an unmatched left row under leftouter or fullouter, or such a right row — a call through that side's alias is null and reads nothing. To read the right side at its own block on every row, call it inside the parentheses: extend on the right, before the join. $block is not a join key across two chains — their numbers mean nothing to each other, and on $block is refused (CQL3007) — so give each side a column t = bin($sampleAt, 1h) and join on t instead, as Compare a contract across chains does. The left row's block is then on the left side's chain, and a call on an address on another chain than the row's needs a block on the address's chain (CQL3006). After such a join, b.f() with { block: b.$block } reads the right side's own block; $block is the left row's number, on the wrong chain, and nothing refuses it. extend on the right is the simpler way there too.
A join holds both of its sides in memory at once, and a run's memory has a limit (Long ranges and limits), so summarize or narrow each side inside its parentheses before joining long ranges.
union
Appends the rows of a second pipeline after the first. Columns match by name and must have the same type on both sides, with two exceptions: a column only one side has is kept, null on the other side's rows, and two whole numbers the contracts declare differently, such as a uint128 and a uint256 or an int256 and a uint256, are one plain bigint. Any other difference is CQL2038.
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH at 25_800_000..25_900_000 every 50_000 as a
| project { block: $block, totalShares: a.getTotalShares() }
| union (
from stETH at 25_950_000..26_000_000 every 50_000 as b
| project { block: $block, totalShares: b.getTotalShares(), pooledEther: b.getTotalPooledEther() }
)
| extend supply = stETH.totalSupply()| block | totalShares | pooledEther | supply |
|---|---|---|---|
| 25800000 | 7717792489966679762581887 | null | 9586377241718516419916753 |
| 25850000 | 7746921193305714220226254 | null | 9626716672509879440847280 |
| 25900000 | 7774962693861889385743622 | null | 9665660942259896720081033 |
| 25950000 | 7767649559030338213761765 | 9660738434498185963490253 | 9660738434498185963490253 |
| 26000000 | 7811469894931136862947183 | 9719445089671689740474249 | 9719445089671689740474249 |
Every row of the first pipeline, then every row of the second: three samples, then two. pooledEther is null on the three the first pipeline produced, which never asked for it. Each row keeps its own block, so supply is read at the block its row came from, and on the last two rows it equals pooledEther, because stETH's supply is the ether it pools. After a union the two aliases name nothing, so a call there goes through a name from let, as stETH.totalSupply() does. Like a join, a union holds both of its sides in memory at once.
order
Sorts by one or more keys, asc or desc after each. The sort is stable, so rows with equal keys keep the order they arrived in; null sorts first ascending.
cql
let router = ethereum:0xFdDf38947aFB03C621C71b06C9C70bce73f12999;
from router as r
| expand r.getStakingModuleIds() as moduleId
| extend validators = r.getStakingModuleActiveValidatorsCount(moduleId)
| order by validators desc, moduleId asctake
Keeps the first n rows in their current order — after an order by, the top n.
cql
let router = ethereum:0xFdDf38947aFB03C621C71b06C9C70bce73f12999;
from router as r
| expand r.getStakingModuleIds() as moduleId
| extend validators = r.getStakingModuleActiveValidatorsCount(moduleId)
| order by validators desc
| take 2distinct
Keeps the first row of each distinct combination of the listed columns, and only those columns.
cql
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84;
from stETH at latest-21600..latest every 7200 as s
| extend treasury = s.getTreasury()
| distinct treasuryRow order
Rows leave a source in chain, block, address, transaction and log order, and every operator keeps that order unless it is order. join emits one row per match: the left rows in their order, each left row's matches in the right side's order. leftouter and fullouter keep a left row nothing matched in its place, and rightouter and fullouter then add the right rows nothing matched, in their order. leftsemi and leftanti emit the left rows they keep once each, in their order. union emits the first pipeline's rows, then the second's. first(), last(), take and make_list all mean that order.
A query CQL refuses
The let on the first line has no semicolon, so the parser reads on into from and stops there:
cql-error
let stETH = ethereum:0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84
from stETH as s
| project { block: $block }See also
- Expressions — what goes inside an operator.
- How a query is put together.