from optimism.core.fact_event_logs

where event_name = 'Swap')

,

label as ( select '0xa132dab612db5cb9fc9ac426a0cc215a3423f9c9' as token , 'Velodrome' as PROJECT_NAME

from optimism.core.dim_labels

UNION

select '0xba12222222228d8ba445958a75a0704d566bf2c8' token , 'Balancer' as project

from optimism.core.dim_labels

UNION

select '0xdef1abe32c034e558cdd535791643c58a13acc10' token , '0x' as project

from optimism.core.dim_labels

UNION

select ADDRESS as token, PROJECT_NAME

from optimism.core.dim_labels

where label_type = 'dex'

,

from_token as ( select date(BLOCK_TIMESTAMP) as date , tx_hash as tx , origin_from_address as swapper, token , PROJECT_NAME as project, symbol as token_in , decimals as decimal_in , EVENT_INPUTS:value as amount_i

from optimism.core.fact_event_logs a join label b on a.ORIGIN_TO_ADDRESS = b.token

join optimism.core.dim_contracts c on a.contract_address = c.address

where tx_hash in ( select tx_hash from tx) and origin_from_address = EVENT_INPUTS:from

,

to_token as ( select date(BLOCK_TIMESTAMP) as date_ , tx_hash , origin_from_address , token , PROJECT_NAME , symbol as token_out , decimals as decimal_out , EVENT_INPUTS:value as amount_ou

from optimism.core.fact_event_logs a join label b on a.ORIGIN_TO_ADDRESS = b.token

join optimism.core.dim_contracts c on a.contract_address = c.address

where tx_hash in ( select tx_hash from tx) and origin_from_address = EVENT_INPUTS:to)

,

final as ( select date , tx_hash, project_name , swapper , token_in , token_out , amount_i/pow(10,decimal_in) as amount_in , amount_ou/pow(10,decimal_out) as amount_out

from from_token a left outer join to_token b on a.tx = b.tx_hash and date = date_

where date >= CURRENT_DATE - 100

,

price as ( select date(hour) as p_date ,symbol , avg(price) as prices

from optimism.core.fact_hourly_token_prices