count(DISTINCT concat('0x', right(topics[2],40))) as minter,

sum(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[2]) / pow(10,18)) as avusd_minted

from avalanche.core.fact_event_logs

where contract_address = '0x1499cb3197427b78dc0e2d356a1e0e4149e0ed51'

and topics[0] = '0xf114ca9eb82947af39f957fa726280fd3d5d81c3d7635a4aeb5c302962856eba'

group by 1

),

redeem as (

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

count(DISTINCT concat('0x', right(topics[2],40))) as redeemer,

sum(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[2]) / pow(10,18)) as avusd_redeemed

from avalanche.core.fact_event_logs

where contract_address = '0x1499cb3197427b78dc0e2d356a1e0e4149e0ed51'

and topics[0] = '0x18fd144d7dbcbaa6f00fd47a84adc7dc3cc64a326ffa2dc7691a25e3837dba03'

group by 1

select date,

minter as "Minter",

redeemer as "Redeemer",

avusd_minted as "Mint avUSD",

-avusd_redeemed "Redeem avUSD",

avusd_minted-avusd_redeemed as "NET Minted",

sum(avusd_minted) over (order by date) - sum(avusd_redeemed) over (order by date) as "TVL"

from mint

full outer join redeem using(date)

order by 1 desc