ramishoow2023-04-05 01:15 PM
    Updated 2023-04-05
    with rami as ( SELECT block_timestamp as dt, tx_hash, decoded_log:amount / 1e18 as royalty, decoded_log:collection as collection from avalanche.core.ez_decoded_event_logs where --final
    origin_to_address = '0xae079eda901f7727d0715aff8f82ba8295719977' and event_name in ('RoyaltyPayment') and tx_status = 'SUCCESS' ), final as ( SELECT
    name, sum(royalty) as royaltyfee from rami a LEFT join avalanche.core.dim_contracts s on a.collection = s.address GROUP by 1) SELECT CASE
    when royaltyfee < 1 then 'lower than 1 avax' when royaltyfee between 1 and 10 then 'between 1 and 10 avax' when royaltyfee BETWEEN 10 and 100 then 'between 10 and 100 avax'
    when royaltyfee BETWEEN 100 and 500 then ' between 100 and 500 avax' when royaltyfee BETWEEN 500 and 1000 then ' between 500 and 1000 avax' when royaltyfee > 1000 then ' more than 1000 avax'
    --when royaltyfee < 1 then 'lower than 1 avax' when royaltyfee between 1 and 10 then 'between 1 and 10 avax' when royaltyfee BETWEEN 10 and 100 then 'between 10 and 100 avax'
    --when royaltyfee BETWEEN 100 and 500 then ' between 100 and 500 avax' when royaltyfee BETWEEN 500 and 1000 then ' between 500 and 1000 avax' when royaltyfee > 1000 then ' more than 1000 avax'
    end as name , count(DISTINCT name) as Collec from final GROUP by 1 order by 1
    --final
    Run a query to Download Data