WITH steth_staked AS ( -- transaction that minted new stETH SELECT date_trunc('hour', block_timestamp) AS date, tx_id, origin_address, amount as stake_amount, price as stake_price, null as sell_amount, null as sell_price FROM ethereum.udm_events LEFT JOIN (SELECT hour, price FROM ethereum.token_prices_hourly WHERE token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84') ON hour = date_trunc('hour', block_timestamp) WHERE contract_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' --steth AND from_address = '0x0000000000000000000000000000000000000000' -- genesis address -- AND DATEDIFF(day, block_timestamp, GETDATE()) between 0 and 30 ), sell_steth as ( SELECT date_trunc('hour', block_timestamp) as date, a.tx_id, a.amount_in, a.amount_Usd FROM ethereum.dex_swaps a WHERE token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' -- steth AND direction = 'IN' -- swap in stETH AND DATEDIFF(day, block_timestamp, GETDATE()) between 0 and 90 ), origin_address AS ( -- to attach to dex swaps SELECT distinct tx_id as tx_origin, origin_address FROM ethereum.udm_events WHERE DATEDIFF(day, block_timestamp, GETDATE()) between 0 and 90 ), steth_sold AS ( -- steth sold by stakers SELECT date, tx_id, origin_address, null as stake_amount, null as stake_price, amount_in as sell_amount, price as sell_price FROM sell_steth LEFT JOIN origin_address ON tx_id = tx_origin LEFT JOIN (SELECT hour, price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84') ON hour = date WHERE origin_address IN (SELECT origin_address FROM steth_staked) AND amount_in > 0 AND DATEDIFF(day, date, GETDATE()) between 0 and 90 ), LONG_FORMAT AS ( SELECT * FROM steth_staked UNION ALL SELECT * FROM steth_sold ), steth_price AS ( -- current price of steTH SELECT hour, price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = '0xae7ab96520de3a18e5e111b5eaab095312d7fe84' ORDER BY hour DESC LIMIT 1 ), FINAL AS ( SELECT origin_address, -- staker address median(price) as current_steth_price, count(stake_price) as stake_count, -- how many times staked sum(stake_amount) as stETH_in, sum(sell_amount) as stETH_out, steth_In - steth_out as steth_balance, avg(stake_price) as avg_stake_price, avg(sell_price) as avg_sell_price, avg_sell_price - avg_stake_price as price_diff, -- positive = gain case when current_steth_price > avg_stake_price then 'Stake Below Current Price' when current_steth_price < avg_stake_price then 'Stake Above Current Price' else null end as staking_status, case when current_steth_price > avg_sell_price then 'Sold Below Current Price' when current_steth_price < avg_sell_price then 'Sold Above Current Price' when avg_sell_price IS NULL then 'Did not sell' else null end as selling_price_status, CASE WHEN stETH_in > 0 AND stETH_in <= 1 THEN '0-1 stETH' WHEN stETH_in > 1 AND stETH_in <= 10 THEN '0-10 stETH' -- amount staked in the last 30 days WHEN stETH_in > 10 AND stETH_in <= 100 THEN '10-100 stETH' WHEN stETH_in > 100 AND stETH_in <= 1000 THEN '100-1k stETH' WHEN stETH_in > 1000 THEN '>1K stETH' ELSE NULL END AS tier -- all stakers should fall at least into one of these tiers FROM LONG_FORMAT LEFT JOIN steth_price GROUP BY origin_address ) SELECT tier, count(staking_status) as "Address Count", sum(case when steth_balance is null then 1 else 0 end) as "Never sold", sum(case when steth_balance > 0 then 1 else 0 end) as "Sold some", sum(case when steth_balance <= 0 then 1 else 0 end) as "Sold all", avg(avg_stake_price) as "Avg Stake Price", avg(avg_sell_price) as "Avg Sell Price", median(case when price_diff > 0 then price_diff else null end) as "Median Price Profit/stETH", avg(case when price_diff > 0 then price_diff else null end) as "Avg Price Profit/stETH", median(case when price_diff < 0 then price_diff else null end) as "Median Price Loss/stETH", avg(case when price_diff < 0 then price_diff else null end) as "Avg Price Loss/stETH", sum(case when price_diff > 0 then 1 else 0 end) as "Profit", sum(case when price_diff < 0 then 1 else 0 end) as "Loss" -- avg(avg_days) as "Avg Days Holding", -- median(avg_days) as "Median Days Holding" FROM FINAL WHERE staking_status IS NOT NULL GROUP BY 1 ORDER BY 1,2

HODL or Sell