select

recorded_at::date as date,

Symbol,

avg(price) as avg_price

from osmosis.core.dim_prices

group by 1,2

),

transfer as (

SELECT

BLOCK_TIMESTAMP,

TX_ID,

SENDER,

AMOUNT/pow(10,DECIMAL) as "Adjusted Amount",

lower(split(currency,'-')[0]) as Symbol,

iff(Symbol ilike 'u%', substring(Symbol, 2, LEN(Symbol)), Symbol) as Token

from axelar.core.fact_transfers

where 1=1

and TX_SUCCEEDED = 'TRUE'

and TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')

AND AMOUNT is not NULL

AND CURRENCY is not NULL

AND not CURRENCY ilike any ('factory%', 'gravity%')

)

BLOCK_TIMESTAMP::date as date,

case

when BLOCK_TIMESTAMP between '2022-11-02' and '2022-11-07' then 'CoinDesk report'

when BLOCK_TIMESTAMP between '2022-11-07' and '2022-11-09' then 'Binance decided to sell its FTT holding'

when BLOCK_TIMESTAMP between '2022-11-09' and '2022-11-11' then 'Binance announced plans to acquire FTX but...'

when BLOCK_TIMESTAMP like '2022-11-11%' then 'FTX filed for Chapter 11 bankruptcy protection'

when BLOCK_TIMESTAMP between '2022-11-12' and '2022-11-14' then 'FTX Hacked'

when BLOCK_TIMESTAMP >= '2022-11-14' then 'Softbank wrote down a $100 million investment in FTX'

else 'Not Special News'

end as status,

count(distinct tx_id) as "# TXs",