WITH PrelimData AS (

SELECT

BLOCK_TIMESTAMP,

LTRIM(SUBSTR(TOPICS[3], 1 + 26, 32)) as address,

TO_NUMBER(LTRIM(SUBSTR(DATA, 3, 64),'0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') as market_id,

(TO_NUMBER(LTRIM(SUBSTR(DATA, 3 + 64 * 1, 64),'0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') / pow(10,8)) as price,

(TO_NUMBER(COALESCE(NULLIF(LTRIM(SUBSTR(DATA, 3 + 64 * 2, 64), '0'), ''), '0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') / pow(10,10)) as qty,

(TO_NUMBER(COALESCE(NULLIF(LTRIM(SUBSTR(DATA, 3 + 64 * 3, 64), '0'), ''), '0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') / pow(10,18)) as fee_usd,

(TO_NUMBER(COALESCE(NULLIF(LTRIM(SUBSTR(DATA, 3 + 64 * 5, 64), '0'), ''), '0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')) as is_long,

(TO_NUMBER(COALESCE(NULLIF(LTRIM(SUBSTR(DATA, 3 + 64 * 2, 64), '0'), ''), '0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') / pow(10,10)) * (TO_NUMBER(COALESCE(NULLIF(LTRIM(SUBSTR(DATA, 3 + 64 * 2, 64), '0'), ''), '0'), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') / pow(10,10)) as volume,

SUM(volume) OVER (PARTITION BY address ORDER BY BLOCK_TIMESTAMP ASC) as cumulative_volume_per_address

FROM arbitrum.core.fact_event_logs

WHERE CONTRACT_ADDRESS = '0xf45f2903438d383769ca3e80356c167e9d9af1ad'

AND TOPICS[0] = '0x4af5e5fe76d7cc778f30ee5a2c0ffb95f63e38fe49ab7f58a9a4381bdaabcda3'

--AND BLOCK_TIMESTAMP > '2024-01-01 19:48:39.000'

--AND INSERTED_TIMESTAMP >= CURRENT_DATE - interval '14 day'

),

NumberedData AS (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY address ORDER BY BLOCK_TIMESTAMP DESC) AS rn

FROM PrelimData

SELECT

BLOCK_TIMESTAMP,

address,

market_id,

price,

qty,

fee_usd,

is_long,

volume,