tx_id,

block_timestamp,

authorizers[0] as user,

event_data:amount as tx_fee

from flow.core.fact_transactions

join flow.core.fact_events

using (tx_id)

where tx_succeeded = 1

and event_contract = 'A.f919ee77447b7497.FlowFees'

and event_Type = 'FeesDeducted'

and block_timestamp::date between case '{{ type }}'

when 'daily' then date('{{ end_date }}') - interval '1 day'

when 'weekly' then date('{{ end_date }}') - interval '1 week'

when 'monthly' then date('{{ end_date }}') - interval '1 month'

when 'yearly' then date('{{ end_date }}') - interval '1 year'

end and '{{ end_date }}'

count(distinct tx_id) as transactions,

(transactions / (select count(distinct tx_id) from flow.core.fact_transactions where block_timestamp::date between case '{{ type }}'

when 'daily' then date('{{ end_date }}') - interval '1 day'

when 'weekly' then date('{{ end_date }}') - interval '1 week'

when 'monthly' then date('{{ end_date }}') - interval '1 month'

when 'monthly' then date('{{ end_date }}') - interval '1 year'

end and '{{ end_date }}')) * 100 as success_rate,

count(distinct user) as users,

sum(tx_fee) as fees_volume_flow,

avg(tx_fee) as average_fees_amount_flow,

transactions / count(distinct block_timestamp::date) as daily_average_transactions,

users / count(distinct block_timestamp::date) as daily_average_users

from main