select block_timestamp::date as day,

swap_from_mint as token,

median (swap_to_amount/swap_from_amount) as USDPrice

from

solana.fact_swaps

where

swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC, USDT

and swap_to_amount > 0

and swap_from_amount > 0

and block_timestamp >= CURRENT_DATE - 30

and succeeded = 'TRUE'

group by 1,2 -- prices comes from https://app.flipsidecrypto.com/velocity/queries/431bde4b-6831-4de6-87d3-8d399639aa5f

), defi_addresses as (

select

address, initcap(label) as dapp_name

from

solana.core.dim_labels

where

label_type in ('dapp', 'dex')

union all

select 'srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX' as address, 'Openbook' as dapp_name

), defi_transactions as (

select

tx_id, a.dapp_name

from

solana.core.fact_events t join defi_addresses a on t.program_id=a.address

where

block_timestamp > CURRENT_DATE-30

), transfers as (

date_trunc('day', block_timestamp) as day,

t.tx_id,

SWAP_FROM_AMOUNT*USDPrice as price,

dapp_name

from