select a.TX_ID

,a.block_timestamp

,tx_from

,split(ATTRIBUTE_VALUE,'uatom')[0]::numeric/1e6 as "amount"

from cosmos.core.fact_msg_attributes a

inner join cosmos.core.fact_transactions b on a.TX_ID=b.TX_ID

where a.msg_type = 'delegate'

and a.attribute_key = 'amount'

and a.ATTRIBUTE_VALUE ilike '%uatom%'

and a.tx_succeeded = 'TRUE'

date_trunc('{{date_range}}',block_timestamp) as "Date"

,count(distinct tx_from) as "Delegators"

,sum("amount") as "Delegate amount"

,count(distinct TX_ID) as "Delegation txs"

,sum("Delegate amount") over(order by "Date") as "Cumulative Delegate amount"

,"Delegate amount"/"Delegators" as "Avg amount per delegator"

,Avg("Delegate amount"/"Delegators") over(order by "Date") as "Total Avg amount per delegator"

from a

group by 1

order by 1 desc