adambalaFace Rarity
    Updated 2022-10-02
    with trait1 as
    ( SELECT
    RIGHT(token_name, LENGTH(token_name) - CHARINDEX('#',token_name))::INT AS token_id
    , lower(token_metadata:background) AS background
    FROM ethereum.core.dim_nft_metadata
    WHERE TRUE
    AND CONTRACT_ADDRESS = lower('0x8a90cab2b38dba80c64b7734e58ee1db38b8992e'))
    ---------------------------------------------------
    , rarity as (select count(distinct token_id) as Number ,background ,Number /100 AS rarity
    from trait1
    group by 2)
    ----------------------------------------------------
    , background as
    (select token_id , rarity.background ,Number ,rarity
    from trait1 inner join rarity on trait1.background = rarity.background)
    -------------------------------------------------------------------------------------------------------------------------------------------
    , trait2 as
    ( SELECT
    RIGHT(token_name, LENGTH(token_name) - CHARINDEX('#',token_name))::INT AS token_id
    , lower(token_metadata:body) AS body
    FROM ethereum.core.dim_nft_metadata
    WHERE TRUE
    AND CONTRACT_ADDRESS = lower('0x8a90cab2b38dba80c64b7734e58ee1db38b8992e'))
    ---------------------------------------------------
    , rarity2 as (select count(distinct token_id) as Number ,body ,Number /100 AS rarity
    from trait2
    group by 2)
    ----------------------------------------------------
    , body as
    (select token_id , rarity2.body ,Number ,rarity
    from trait2 inner join rarity2 on trait2.body = rarity2.body)
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    , trait3 as
    ( SELECT
    RIGHT(token_name, LENGTH(token_name) - CHARINDEX('#',token_name))::INT AS token_id
    , lower(token_metadata:face) AS face