WITH prices as (

SELECT HOUR::date as date, avg(price) as avg_price

FROM ethereum.core.fact_hourly_token_prices

WHERE SYMBOL = 'WBTC'

GROUP BY 1

, raw as (

SELECT 'Deposit' as type, block_timestamp::date as date, tx_hash, ORIGIN_FROM_ADDRESS, RAW_AMOUNT / POW(10, 8) as amount, amount * avg_price as amount_usd

FROM avalanche.core.fact_token_transfers t INNER JOIN prices p ON block_timestamp::date = date

WHERE contract_address = '0x152b9d0fdc40c096757f570a51e494bd4b943e50' and FROM_ADDRESS = '0x0000000000000000000000000000000000000000'

and ORIGIN_FUNCTION_SIGNATURE = '0xa888d914'

UNION

SELECT 'Withdraw' as type, block_timestamp::date as date, tx_hash, ORIGIN_FROM_ADDRESS, RAW_AMOUNT / POW(10, 8) as amount, amount * avg_price as amount_usd

FROM avalanche.core.fact_token_transfers t INNER JOIN prices p ON block_timestamp::date = date

WHERE contract_address = '0x152b9d0fdc40c096757f570a51e494bd4b943e50' and TO_ADDRESS = '0x0000000000000000000000000000000000000000'

and ORIGIN_FUNCTION_SIGNATURE = '0x6e286671'

SELECT date, type, SUM(amount) as daily_amount, SUM(daily_amount) OVER (PARTITION BY type ORDER BY date ASC) as total_amount,

SUM(amount_usd) as daily_amount_usd, SUM(daily_amount_usd) OVER (PARTITION BY type ORDER BY date ASC) as total_amount_usd,

COUNT(DISTINCT tx_hash) as daily_tx, SUM(daily_tx) OVER (PARTITION BY type ORDER BY date ASC) as total_tx

FROM raw

WHERE date > '2022-11-01'

GROUP BY 1, 2