select

block_timestamp::date as p_date,

median(to_amount/from_amount) as USDPrice

from terra.core.ez_swaps

where from_currency = 'uluna'

and to_currency in ('ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF',

'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4') --USDC,USDT

and to_amount > 0

and from_amount > 0

and tx_succeeded = TRUE

group by 1

SELECT

date_trunc('day', block_timestamp)::date as date,

count(distinct tx_id) as "Tx Count",

sum("Tx Count") over (order by date) as "Cumulative Tx Count",

avg("Tx Count") over (order by date, date rows between 6 preceding and current row) as "MA7-D Tx Count",

avg("Tx Count") over (order by date, date rows between 14 preceding and current row) as "MA15-D Tx Count",

avg("Tx Count") over (order by date, date rows between 29 preceding and current row) as "MA30-D Tx Count",

count(distinct TX_SENDER) as "User Count",

avg("User Count") over (order by date, date rows between 6 preceding and current row) as "MA7-D User Count",

avg("User Count") over (order by date, date rows between 14 preceding and current row) as "MA15-D User Count",

avg("User Count") over (order by date, date rows between 29 preceding and current row) as "MA30-D User Count",

sum(FEE) as "Transactions Fee(LUNA)",

sum("Transactions Fee(LUNA)") over (order by date) as "Cumulative Transactions Fee(LUNA)",

avg("Transactions Fee(LUNA)") over (order by date, date rows between 6 preceding and current row) as "MA7-D Transactions Fee(LUNA)",

avg("Transactions Fee(LUNA)") over (order by date, date rows between 14 preceding and current row) as "MA15-D Transactions Fee(LUNA)",

avg("Transactions Fee(LUNA)") over (order by date, date rows between 29 preceding and current row) as "MA30-D Transactions Fee(LUNA)",

sum(FEE*USDPrice) as "Transactions Fee($)",

sum("Transactions Fee($)") over (order by date) as "Cumulative Transactions Fee($)",

avg("Transactions Fee($)") over (order by date, date rows between 6 preceding and current row) as "MA7-D Transactions Fee($)",

avg("Transactions Fee($)") over (order by date, date rows between 14 preceding and current row) as "MA15-D Transactions Fee($)",

avg("Transactions Fee($)") over (order by date, date rows between 29 preceding and current row) as "MA30-D Transactions Fee($)",

sum(CASE WHEN tx_succeeded = true THEN 1 ELSE 0 END) as "Succeed Tx Count",

sum(CASE WHEN tx_succeeded != true THEN 1 ELSE 0 END) as "Failed Tx Count",