-- select -- block_timestamp::date as day, -- sum(amount) as daily_eth_staked, -- sum(daily_eth_staked) over (order by day) as cumulative_eth_staked with staker_details as (SELECT * from ethereum.udm_events --where tx_id = '0x4026d646872f7cb6da67725b2a0b7dc20665c4aa02d1893c6cec51ea3022820d' where block_timestamp>CURRENT_DATE-90 and contract_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' --steth and from_address = '0x0000000000000000000000000000000000000000') -- group by 1 -- order by 1 asc , higher_lower_buy as (SELECT * from (SELECT date(hour) as day, avg(price) as avg_price from ethereum.token_prices_hourly where symbol = 'ETH' GROUP by 1 ) where avg_price >= 4200 or avg_price <=2800 ), all_purchases as (SELECT tx_id, origin_address,avg_price, max(amount) as buy, buy*avg_price as amt_usd from (SELECT * from staker_details inner join higher_lower_buy on day = date(block_timestamp)) GROUP by 1,2,3 ), all_purchases_by_user as ( SELECT origin_address, sum(buy) as total_amt, sum(amt_usd) as total_amt_usd, total_amt_usd/total_amt as avg_buy_price from all_purchases GROUP by 1 ), total_users as (SELECT count(DISTINCT origin_address) as total_users, avg(buy) as avg_buy_per_txs, count(*) as total_txs, median(buy) as median_buy_per_tx from all_purchases) SELECT distribution, count(DISTINCT origin_address) as unique_users from (SELECT * , case when total_amt < balance and avg_buy_price <= 2800 then 'brought more' when total_amt = balance and avg_buy_price <= 2800 then 'hodl' when balance < 0.01*total_amt and avg_buy_price <= 2800 then 'sold full' when total_amt > balance and avg_buy_price <= 2800 then 'sold partially' end as distribution -- case when total_amt < balance and avg_buy_price <= 2800 then 'brought more' -- when total_amt = balance and avg_buy_price <= 2800 then 'hodl' -- when balance = '0' and avg_buy_price <= 2800 then 'sold full' -- when 'sold partially' and avg_buy_price <= 2800 end as distribution_low_purchase, -- , -- case when total_amt < balance and avg_buy_price >= 4200 then 'brought more' -- when total_amt = balance and avg_buy_price >= 4200 then 'hodl' -- when balance = '0' and avg_buy_price >= 4200 then 'sold full' -- when 'sold partially' and avg_buy_price >= 4200 end as distribution_high_purchase from (SELECT * from ethereum.erc20_balances where balance_date = CURRENT_DATE -1 and contract_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' ) inner join all_purchases_by_user on origin_address = user_address ) GROUP by 1 limit 100

HODL or Sell Lido