Query editor
-- LayerZero 官方 OFT 币的跨链价差(源码见仓库 config/dune/lz-oft-gap.sql)WITH params AS (SELECT CAST(CAST('{{start_date}}' AS DATE) AS TIMESTAMP) AS start_ts, CAST(CAST('{{end_date}}' AS DATE) AS TIMESTAMP) AS end_ts),pairs (sym, rh_token, chain, o_token) AS (VALUES('EDEL', 0x88c4c42ed68584ff02f5a00d5d3d985108a3961c, 'base', 0xfb31f85a8367210b2e4ed2360d2da9dc2d2ccc95),('EDEL', 0x88c4c42ed68584ff02f5a00d5d3d985108a3961c, 'ethereum', 0xbf59dbc154421a7b37f4f2841e11f4ed2a1dee7c),('IMD', 0x5f7bb59365ce557c26dbcaa4ee9d39a4b95b7127, 'base', 0xff0c532fdb8cd566ae169c1cb157ff2bdc83e105),('IMD', 0x5f7bb59365ce557c26dbcaa4ee9d39a4b95b7127, 'ethereum', 0xd34a99bc0f67ae1bbd63c660e6d0b0dd03e263b7),('Surplus', 0xa8219d665c831ab5caadb14b24de9605383f2119, 'base', 0xc52aedec3374422d7510e294cfaa90799595cba3),('RAM', 0x5173d45a1191ee33cbb7d8c7e65f21b04ed54802, 'base', 0x03fe4dd36db2c8483e258974db82d07d82f8d239),('PENGU', 0x74be72affafbc8de30f0c11247814036314d625f, 'ethereum', 0x6418c0dd099a9fda397c766304cdd918233e8847),('BNKR', 0x178e54df3d091ee4d0b2534742ef9e3692b76526, 'base', 0x22af33fe49fd1fa80c7149773dde5890d3c76f3b),('hOHM', 0x76cf788606f3d968b93b8a243d0e185c974ee407, 'ethereum', 0x1db1591540d7a6062be0837ca3c808add28844f6),('MRDN', 0xbaabcb1800c539a58a46035d3982403a37fd6d5d, 'base', 0xe57e601c06689d3e2bf7db7bebb14b4ff28400c6),('Anon', 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c, 'base', 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c),('Anon', 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c, 'ethereum', 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c),('r33', 0x4e5195469a0360f5dfe811730c9ccacff0a0feee, 'base', 0xadd32480630a16dfacee6eefcb3ab2181449dc3b),('PENDLE', 0x5e49e1f85813f2b65858860a3fa231b4186f2e0e, 'ethereum', 0x808507121b80c02388fad14726482e061b8da827),('USDe', 0x5d3a1ff2b6bab83b63cd9ad0787074081a52ef34, 'ethereum', 0x4c9edd5852cd905f086c759e8383e09bff1e68b3)),toks AS (SELECT DISTINCT 'robinhood' AS chain, rh_token AS token FROM pairsUNIONSELECT DISTINCT chain, o_token FROM pairs),tr AS (SELECT d.blockchain AS chain, x.token, DATE_TRUNC('minute', d.block_time) AS m, d.amount_usd AS usd, x.qtyFROM dex.trades dCROSS JOIN params pCROSS JOIN UNNEST(ARRAY[d.token_bought_address, d.token_sold_address], ARRAY[d.token_bought_amount, d.token_sold_amount]) AS x(token, qty)JOIN toks k ON k.chain = d.blockchain AND k.token = x.tokenWHERE d.blockchain IN ('robinhood', 'base', 'ethereum')AND d.block_month >= CAST(DATE_TRUNC('month', p.start_ts) AS DATE)AND d.block_time >= p.start_ts AND d.block_time < p.end_tsAND d.amount_usd >= 20e0 AND x.qty > 0e0),mins AS (SELECT chain, token, m, SUM(usd) / SUM(qty) AS px, SUM(usd) AS vol, COUNT(*) AS nFROM tr GROUP BY 1, 2, 3),gaps AS (SELECT pr.sym, pr.chain, r.m, r.px / o.px - 1e0 AS gap, r.vol AS rh_vol, o.vol AS o_volFROM pairs prJOIN mins r ON r.chain = 'robinhood' AND r.token = pr.rh_tokenJOIN mins o ON o.chain = pr.chain AND o.token = pr.o_token AND o.m = r.m),seg AS (SELECT g.*,(ABS(g.gap) > 0.02e0) AS big,LAG(g.m) OVER (PARTITION BY g.sym, g.chain ORDER BY g.m) AS prev_m,LAG(ABS(g.gap) > 0.02e0) OVER (PARTITION BY g.sym, g.chain ORDER BY g.m) AS prev_bigFROM gaps g),seg2 AS (SELECT s.*, (s.big AND NOT (COALESCE(s.prev_big, false) AND s.prev_m = s.m - INTERVAL '1' MINUTE)) AS seg_startFROM seg s)SELECT