with prices as ( SELECT date_trunc('day', block_timestamp) AS days, currency, avg(price_usd) * 1 as avg FROM terra.oracle_prices WHERE block_timestamp >= CURRENT_DATE - 4000 group by days, currency order by days DESC limit 100000000 ), nonnative as ( SELECT date_trunc('day', block_timestamp) AS dayss, COUNT(DISTINCT tx_id) AS n_tradez, COUNT(DISTINCT (msg_value:sender::string )) as n_traderz, msg_value:execute_msg:swap:offer_asset:info:native_token:denom::string as swap_currency, sum(msg_value:execute_msg:swap:offer_asset:amount / POW(10,6)) as trading_vol_token0, c.address_name as contract_label FROM terra.msgs LEFT OUTER JOIN terra.labels c ON msg_value:contract::string = c.address WHERE msg_value:execute_msg:swap IS NOT NULL group by dayss, swap_currency, contract_label order by dayss DESC LIMIT 10000000 ), combine as ( select * FROM nonnative left join prices on nonnative.dayss = prices.days and nonnative.swap_currency = prices.currency --where TRADING_VOL_TOKEN0 > 1 limit 100000000 ) select dayss, n_tradez, n_traderz, contract_label, (avg * TRADING_VOL_TOKEN0) as USDAVG, (USDAVG / n_tradez ) as pertrade, (USDAVG / n_traderz ) as pertrader from combine where USDAVG > 0 and contract_label <> 'Terraswap LUNA-SDT Pair' and contract_label <> 'bLUNA' and contract_label <> 'mGS' and contract_label is not null and contract_label <> 'Terraswap LUNA-UST Pair' and contract_label <> 'Terraswap bLUNA-LUNA Pair' and contract_label <> 'Terraswap LUNA-KRT Pair' and contract_label <> 'Terraswap LUNA-MNT Pair' and contract_label <> 'Terraswap SPEC-UST Pair' and contract_label <> 'Terraswap LUNA-MNT Pair' and contract_label <> 'Terraswap LOTA-UST Pair' and contract_label = 'Terraswap ANC-UST Pair' and contract_label <> 'Terraswap MIR-UST Pair' and contract_label <> 'Terraswap MINE-UST Pair' and dayss > '2021-03-16T00:00:00Z' limit 1000000000

Measuring Anchor Risk