MahrooUntitled Query
    Updated 2022-10-26
    with pricet as (
    select recorded_at::date as day,
    symbol,
    address,
    avg (price) as USDPrice
    from osmosis.core.dim_prices t1 join osmosis.core.dim_labels t2 on project_name = symbol
    group by 1,2,3),

    wallettypes as (
    select 'Humpback Whale' as wallet_type,
    t1.address,
    sum (balance*usdprice/pow(10,decimal)) as USD_Balance
    from osmosis.core.fact_daily_balances t1 join pricet t2 on t1.currency = t2.address and t1.date = t2.day
    where date = (select max (date) from osmosis.core.fact_daily_balances)
    group by 1,2 having usd_balance >= 10000000

    union ALL

    select 'Whale' as wallet_type,
    t1.address,
    sum (balance*usdprice/pow(10,decimal)) as USD_Balance
    from osmosis.core.fact_daily_balances t1 join pricet t2 on t1.currency = t2.address and t1.date = t2.day
    where date = (select max (date) from osmosis.core.fact_daily_balances)
    group by 1,2 having usd_balance >= 1000000 and usd_balance < 10000000

    union ALL

    select 'Shark' as wallet_type,
    t1.address,
    sum (balance*usdprice/pow(10,decimal)) as USD_Balance
    from osmosis.core.fact_daily_balances t1 join pricet t2 on t1.currency = t2.address and t1.date = t2.day
    where date = (select max (date) from osmosis.core.fact_daily_balances)
    group by 1,2 having usd_balance >= 100000 and usd_balance < 1000000

    union ALL

    Run a query to Download Data