-- Credit to pinehearst for this query! :)
WITH bridge_vol AS (
SELECT
DATE_TRUNC('{{period}}', block_timestamp) as date,
symbol,
token_name,
median(current_bal) as balance,
median(current_bal_usd) as balance_usd
FROM ethereum.core.ez_balance_deltas
WHERE user_address IN ('0x6bfad42cfc4efc96f529d786d643ff4a8b89fa52', '0x23ddd3e3692d1861ed57ede224608875809e127f')
AND block_timestamp > '2022-07-19' AND block_timestamp < CURRENT_DATE
AND symbol IS NOT NULL
GROUP BY 1,2,3
ORDER BY 1,2
),
current_price AS (
SELECT
symbol as smybol1,
avg(price) as usd
FROM ethereum.core.fact_hourly_token_prices
WHERE symbol IN (SELECT distinct symbol FROM bridge_vol)
AND current_date - date(hour) < 1
GROUP BY 1
UNION
SELECT
case when symbol = 'WETH' then 'ETH' end as symbol1,
avg(price) as usd
FROM ethereum.core.fact_hourly_token_prices
WHERE symbol = 'WETH'
AND current_date - date(hour) < 1
GROUP BY 1
)
SELECT
*,
balance*usd as standardized_usd