CREATE TABLE 'xrpl_offer_exchanges' (
  pair SYMBOL capacity 131072 CACHE index capacity 131072,
  rate DOUBLE,
  volume_a DOUBLE,
  volume_b DOUBLE,
  buyer VARCHAR,
  seller VARCHAR,
  taker VARCHAR,
  provider VARCHAR,
  isAMM BOOLEAN,
  autobridged VARCHAR,
  tx_hash VARCHAR,
  tx_type VARCHAR,
  ledger_index INT,
  tx_index INT,
  offer_sequence INT,
  ts TIMESTAMP
) timestamp (ts) PARTITION BY MONTH WAL;

WITH

first_selection as (SELECT pair, first(rate) AS open, last(rate) AS close, min(rate) AS low, max(rate) AS high, sum(volume_a) AS base_volume, sum(CASE WHEN buyer = taker then volume_a else 0 END) as base_volume_buy, sum(CASE WHEN seller = taker then volume_a else 0 END) as base_volume_sell, sum(volume_b) AS counter_volume, sum(CASE WHEN seller = taker then volume_b else 0 END) as counter_volume_buy, sum(CASE WHEN buyer = taker then volume_b else 0 END) as counter_volume_sell, count(*) AS exchanges, count_distinct(buyer) as unique_buyers, count_distinct(seller) as unique_sellers, last(ts) as last_trade FROM xrpl_offer_exchanges WHERE  ts >= '2024-03-25T17:53:02.932Z' AND ts <= '2024-03-26T17:53:02.932Z'  AND ( (pair not like 'XRP|%' OR (pair like 'XRP|%' AND volume_a >= 0.00001)) AND (pair not like '%|XRP' OR (pair like '%|XRP' AND volume_b >= 0.00001)))  ),

second_selection as (SELECT pair, rate as prev_rate, ts as prev_ts FROM xrpl_offer_exchanges WHERE  ts < '2024-03-25T17:53:02.932Z' AND pair in (SELECT pair FROM first_selection) LATEST ON ts PARTITION BY pair)

SELECT first_selection.pair, first_selection.open, first_selection.close, first_selection.low, first_selection.high, first_selection.base_volume, first_selection.base_volume_buy, first_selection.base_volume_sell, first_selection.counter_volume, first_selection.counter_volume_buy, first_selection.counter_volume_sell, first_selection.exchanges, first_selection.unique_buyers, first_selection.unique_sellers, first_selection.last_trade, second_selection.prev_rate, second_selection.prev_ts from first_selection LEFT JOIN second_selection on (pair) WHERE first_selection.pair LIKE '%|%' ;