SalehLiquid Staking ETH Platforms Analysis-Cream date
    Updated 2022-09-02
    with price as (
    select price
    from ethereum.core.fact_hourly_token_prices
    where hour::date=CURRENT_DATE-1
    and symbol ='WETH'
    )
    , lst_Cream as (
    select
    TX_HASH
    ,BLOCK_TIMESTAMP
    ,ORIGIN_FROM_ADDRESS
    ,event_inputs:value/pow(10,18) as amount
    ,eth_price*event_inputs:value/pow(10,18) as amount_usd
    from ethereum.core.fact_event_logs
    join (
    select hour::date as date, avg(price) as eth_price
    from ethereum.core.fact_hourly_token_prices
    where symbol ='WETH' group by 1) price
    on price.date = block_timestamp::date
    where origin_to_address in ('0xcbc1065255cbc3ab41a6868c22d1f1c573ab89fd','0x49d72e3973900a195a155a46441f0c08179fdb64')
    and origin_function_signature = '0xa9059cbb'
    and amount > 0
    )
    ,lst_Cream_sum (
    select
    date_trunc(week,block_timestamp)::date as date
    ,count(tx_hash) as stakes_count
    ,count(DISTINCT ORIGIN_FROM_ADDRESS) as stakers_count
    ,sum(amount) as stake_ETH
    ,sum(amount_usd) as stake_USD_in_time_staking
    ,sum(amount*(select avg(price) from price)) as stake_usd_currently
    ,avg(amount) as stake_ETH_AVG
    ,avg(amount_usd) as stake_USD_AVG
    ,median(amount) as stake_ETH_median
    ,median(amount_usd) as stake_USD_median
    ,min(amount) as stake_ETH_MIN
    Run a query to Download Data