with prices as ( SELECT date_trunc('day', block_timestamp) AS dayz, currency, avg(price_usd) * 1 as avgg FROM terra.oracle_prices WHERE block_timestamp >= CURRENT_DATE - 400 group by dayz, currency order by dayz DESC limit 10000 ), nonnative as ( SELECT date_trunc('day', block_timestamp) AS dayzz, COUNT(DISTINCT tx_id) AS n_trades, COUNT(DISTINCT (msg_value:sender::string )) as n_traders, 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 dayzz, swap_currency, contract_label order by dayzz DESC LIMIT 10000000 ), combine as ( select * FROM nonnative left join prices on nonnative.dayzz = prices.dayz and nonnative.swap_currency = prices.currency --where TRADING_VOL_TOKEN0 > 1 limit 100000000 ) select dayzz, n_trades, n_traders, contract_label, (avgg * TRADING_VOL_TOKEN0) as USDADJ, (USDADJ / n_trades ) as pertrade, (USDADJ / n_traders ) as pertrader from combine where USDADJ > 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 dayzz > '2021-04-16T00:00:00Z' limit 1000000000

Assets on Mirror