nitsHigher purchase than currrent price
    Updated 2022-06-16
    -- 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 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 token_address ilike '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2'
    GROUP by 1 )
    where avg_price >= 1500 ),
    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
    Run a query to Download Data