select

date_trunc('day', recorded_hour) as date,

symbol,

token_address,

avg(close) as price

from

solana.price.ez_token_prices_hourly

where

symbol in ('sol', 'eth')

group by

1,

2,

3

),

main as (

select

tx_id,

case

when swap_program ilike 'jupiter%' then 'Jupiter'

when swap_program ilike 'orca%' then 'Orca'

when swap_program ilike 'raydium%' then 'Raydium'

when swap_program ilike 'saber%' then 'Saber'

else initcap(swap_program)

end as program,

block_timestamp,

fee / 1e9 * pricet1.price as swap_fee

from

solana.defi.fact_swaps

join solana.core.fact_transactions using(tx_id, block_timestamp)

join pricet pricet1 on pricet1.date = date_trunc('day', block_timestamp :: date)

and symbol = 'sol'

where

block_timestamp :: date >= '2021-01-01'

and succeeded = 1

union