tx_id,

EVENT_DATA:"initiator" as user,

event_data:"amount" as amt,

event_data:"contractTVL"::float as tvl

from flow.core.fact_events

where tx_succeeded = 'TRUE'

and event_contract = 'A.a45ead1cf1ca9eda.FlowRewards'

and event_type = 'Locked')

,

base2 as (select block_timestamp::date as date,

count(DISTINCT tx_id) as transactions,

count(DISTINCT user) as users,

sum(amt) as "Locked Flow",

max(tvl) as "TVL Flow",

avg(amt) as "Avg Amount",

median(amt) as "Median Amount",

max(amt) as "Max Amount"

from base

group by 1)

,

price as ( select hour::date,

avg(price) as avg_price

from flow.price.ez_prices_hourly

where symbol = 'FLOW'

group by 1

order by 1 desc

limit 1)

select date,

"TVL Flow",

"TVL Flow"*avg_price as "TVL (USD)"

from base2 , price

order by 1 desc

limit 1