binhachonCopy of 21. Top Pool Analytics
    Updated 2022-01-10
    with liquidity_pools as (
    select
    pool_address,
    pool_name
    from ethereum.dex_liquidity_pools
    where platform ilike '%sushi%'
    ),
    account_balance as (
    select
    user_address,
    sum(amount_usd) as balance
    from ethereum.erc20_balances
    where balance_date in (select distinct balance_date from ethereum.erc20_balances order by balance_date desc limit 1)
    group by user_address
    ),
    average_account_balance as (
    select
    user_address as user_address_average,
    sum(amount_usd)/count(distinct balance_date) as average_balance
    from ethereum.erc20_balances
    where balance_date > getdate() - interval'1 year'
    group by user_address_average
    ),
    dex_24h_volume as (
    select
    pool_address as pool_address_24h,
    sum(amount_usd) as volume_24h,
    volume_24h * 0.003 as fee_24h
    from ethereum.dex_swaps
    where block_timestamp > getdate() - interval'1 day'
    group by pool_address_24h
    ),
    dex_7days_volume as (
    select
    pool_address as pool_address_7days,
    sum(amount_usd) as volume_7days,
    Run a query to Download Data