date,

case

when fee < 0.01 then 'less than 0.01'

when fee between 0.01 and 0.1 then '0.01 - 0.1'

when fee between 0.1 and 1 then '0.1 - 1'

when fee between 1 and 10 then '1 - 10'

when fee between 10 and 50 then '10 - 50'

when fee between 50 and 100 then '50 - 100'

when fee between 100 and 500 then '100 - 500'

else 'more then 500'

end as status,

count(DISTINCT project_name) as "count dApps"

(

select

date_trunc('day', t.block_timestamp) as date,

project_name,

sum(tx_fee) as fee

from

avalanche.core.fact_transactions t

join avalanche.core.fact_event_logs l on t.tx_hash = l.tx_hash

join avalanche.core.dim_labels c on l.contract_address = c.address

t.block_timestamp >= '{{StartDate}}'

and t.block_timestamp <= '{{EndDate}}'

and not label_type like 'token'

group by

1,

2

group by

1,

2

order by

2