Query editor
with tx as (select *from query_5917456where type in ('Mint', 'Swap', 'Burn', 'Borrow', 'RepayBorrow')),new_users_by_date as (selectblock_date,sum(case when is_new_user then 1 else 0 end) as new_usersfrom (selectblock_date,user,row_number() over (partition by user order by block_date asc) = 1 as is_new_userfrom (select block_date, sender as user from txunionselect block_date, recipient as user from tx)where user is not null)group by block_date),-- Pass-through vaults, from the canonical registry (query_8294314). These forward-- FXRP onward, so the tx feed counts the same FXRP twice: once at the vault's own-- deposit event, once at the downstream venue. The overlap on any given day is-- what the vault no longer holds -- cumulative feed flow minus actual balance.-- This replaces the old upshift_sources / fxrp_lps / upshift_daily_allocation-- trio, which tracked only the ClearStar SUB-ACCOUNT (never the ClearStar vault-- itself), had no Mystic entry, and whose destination whitelist missed both the-- LayerZero OFT adapter and redemption burns. Needs no whitelist at all, so new-- venues cannot leak back in.passthrough_vaults as (select pool as vault, custody_address, projectfrom query_8294314where is_passthrough),-- Daily net FXRP the feed attributes to each vault's own pool address.vault_flow_daily as (select v.vault, t.block_date, sum(t.amount_fxrp) / 1e6 as flow_deltafrom tx tjoin passthrough_vaults v on v.vault = t.poolgroup by v.vault, t.block_date),-- Daily FXRP balance delta of each vault. Two equi-joins rather than one OR join:-- far cheaper in Trino, and self-transfers net to zero automatically instead of-- inflating the balance the way a single CASE would.vault_bal_daily as (select vault, block_date, sum(delta) / 1e6 as bal_deltafrom (select v.vault, t.evt_block_date as block_date, cast(t.value as int256) as deltafrom passthrough_vaults vjoin erc20_flare.evt_transfer t on t."to" = v.custody_addresswhere t.contract_address = 0xad552a648c74d49e10027ab8a618a3ad4901c5beand t.evt_block_date >= date '2024-01-01'union all