WITH swaps_hourly AS(

-- use the events_emitted table to parse swaps

-- example: https://etherscan.io/tx/0x76e3af1083e5bb2f3aa87b974dc3c3a81e7aa8eb4830c69b0456f1a8a4d00038#eventlog

SELECT DATE_TRUNC('hour',block_timestamp) AS swap_date, contract_address AS pool_address,

SUM(event_inputs:amount0In) AS amount0In,

SUM(event_inputs:amount1In) AS amount1In

FROM ethereum.events_emitted

WHERE block_timestamp >= CURRENT_DATE - interval '4 months' AND block_timestamp <= CURRENT_DATE - interval '1 day'

AND contract_address = '0xc3f279090a47e80990fe3a9c30d24cb117ef91a8' -- WETH-ALCX SLP

AND event_name = 'Swap'

GROUP BY 1,2

), pool_labels AS (

-- get pool info so we can interpret swaps events for arbitrary pools (look for when the pool was created)

-- example: https://etherscan.io/tx/0xa0c88324023a3261067751716dddaa1749184e41a867794fc419faab8b236a29#eventlog

SELECT DATE(block_timestamp) AS creation_date, contract_address, tx_id,

REGEXP_REPLACE(event_inputs:pair,'\"','') as pool_address,

REGEXP_REPLACE(event_inputs:token0,'\"','') as token0,

REGEXP_REPLACE(event_inputs:token1,'\"','') as token1

FROM ethereum.events_emitted

WHERE block_timestamp >= '2021-01-01'

AND contract_address = '0xc0aee478e3658e2610c5f7a4a2e1777ce9e4f2ac' -- uniswap v2 factory (also used by Sushiswap, and was used to create WETH-ALCX)

AND event_name = 'PairCreated'

), usd_swaps AS (

SELECT s.swap_date,s.pool_address,

COALESCE(aa.symbol,'Token1') AS token0_symbol,COALESCE(bb.symbol,'Token2') AS token1_symbol,

p.token0,p.token1,

aa.price/bb.price AS price_ratio,

COALESCE(s.amount0In/POWER(10,aa.decimals)*aa.price,0) AS volume0,

COALESCE(s.amount1in/POWER(10,bb.decimals)*aa.price,0) AS volume1,

aaa.amount_usd AS token1_tvl,

bbb.amount_usd AS token2_tvl

FROM swaps_hourly s

LEFT JOIN pool_labels p