maybeyonasens_prem_month
    Updated 2021-12-12
    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,
    case
    when chars <= 3 then 640
    when chars = 4 then 160
    else 5 end as prem_fee,
    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*prem_fee) 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
    Run a query to Download Data