maybeyonasens_post_airdrop_revenue
    Updated 2021-12-25
    with
    eth_price as (
    select
    hour,
    price
    from ethereum.token_prices_hourly
    where symbol = 'WETH'
    ),
    ens_reg as (
    select
    block_timestamp,
    tx_id,
    event_inputs:cost / pow(10,18) as name_price_eth,
    event_inputs:owner::string as user,
    event_inputs:name::string as name,
    len(name) as chars,
    name_price_eth*price as name_price_usd,
    to_timestamp(event_inputs:expires) as expiry_date,
    datediff('year', block_timestamp, expiry_date) as no_years,
    round(name_price_usd)-(no_years*5) as premium,
    case
    when premium <= 2100 then round(28-(premium/71))
    else 0 end
    as days_since_expiry,
    case
    when premium <= 2100 then round(premium/71)
    else 0 end
    as days_left_to_zero
    from ethereum.events_emitted join eth_price on date_trunc('HOUR',block_timestamp) = hour
    where
    event_name = 'NameRegistered'
    and event_removed = FALSE
    and event_inputs:cost is not NULL
    and contract_address = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'
    ),
    ens_renew as (
    Run a query to Download Data