token as (select '0xa3d1a8deb97b111454b294e2324efad13a9d8396' as token),

-- Step 1: Get the top 150 wallets based on the most recent balance

top_wallets as (

select

tb.wallet_address,

argMax(tb.value, tb.block_number) as most_recent_balance -- Get the latest balance

from

token_balances_base_mainnet_v1 tb

where

tb.token_address = (select token from token)

and tb.wallet_address != (select token from token)

and tb.timestamp >= now() - interval '2 months' -- Only consider data from the last 2 months

group by tb.wallet_address

order by most_recent_balance desc

limit 150 -- Limit to the top 150 wallets based on most recent balance

),

-- Step 2: Get the historical weekly balances for the top wallets within the last 2 months

top_wallets_weekly as (

select

tb.wallet_address,

date_trunc('week', tb.timestamp) as stringWeek, -- Group by week

argMax(tb.value, tb.block_number) as weekly_balance

from

token_balances_base_mainnet_v1 tb

where

tb.wallet_address in (select wallet_address from top_wallets) -- Get data only for top wallets

and tb.token_address = (select token from token)

and tb.timestamp >= now() - interval '2 months' -- Limit data to the last 2 months

group by tb.wallet_address, stringWeek

),

-- Step 3: Get the total supply by week for the same token, limited to the last 2 months

total_supply_by_week as (

select

date_trunc('week', timestamp) as stringWeek,

argMax(value, block_number) as weekly_total_supply

from

token_total_supplies_base_mainnet_v1

where

token_address = (select token from token)

and timestamp >= now() - interval '2 months' -- Limit total supply data to the last 2 months

group by stringWeek

)

tw.wallet_address,

tw.stringWeek,

tw.weekly_balance / 1e18 as weekly_balance, -- Convert to ether if needed

ts.weekly_total_supply / 1e18 as weekly_total_supply, -- Convert total supply to ether if needed

case

when ts.weekly_total_supply > 0 then (tw.weekly_balance / ts.weekly_total_supply) * 100

else 0

end as percent -- Calculate the percentage of the total supply held by the wallet

top_wallets_weekly tw

total_supply_by_week ts

tw.stringWeek = ts.stringWeek -- Join based on the same week

order by

tw.wallet_address, tw.stringWeek;