with pools as (

select * from ethereum.core.dim_dex_liquidity_pools where platform = 'sushiswap'

),

base as (

select block_timestamp, tx_hash, origin_from_address as LP,

contract_address as Pool_Address,

event_inputs:amount0::numeric as amount0,

event_inputs:amount1::numeric as amount1,

case when event_name = 'Mint' then 'Add Liquidity'

when event_name = 'Burn' then 'Remove Liquidity'

end as type

from ethereum.core.fact_event_logs

where block_timestamp between '2022-09-01' and '2022-09-30'

and event_name in ('Mint','Burn')

and contract_address in (select Pool_Address from pools)

),

--- SQL HELP https://app.flipsidecrypto.com/dashboard/md82eX

joined_data as (

select a.tx_hash,

a.block_timestamp,a.LP,a.Pool_Address,a.type, a.amount0 , a.amount1,

b.pool_name, b.token0 as Token0_Address, b.token1 as Token1_Address, c.symbol as Token0_Symbol, c.amount as Token0_Amount,

c.amount_usd as Token0_USD_Amount,

d.symbol as Token1_Symbol, d.amount as Token1_Amount, d.amount_usd as Token1_USD_Amount

from base a

left join pools b

on a.Pool_Address = b.Pool_Address

left join ethereum.core.ez_token_transfers c

on a.tx_hash = c.tx_hash and c.contract_address = b.token0 and c.raw_amount = a.amount0

and c.block_timestamp between '2022-09-01' and '2022-09-30'

left join ethereum.core.ez_token_transfers d

on a.tx_hash = d.tx_hash and d.contract_address = b.token1 and d.raw_amount = a.amount1

and d.block_timestamp between '2022-09-01' and '2022-09-30'

),