7. [Hard] Liquidity Pools on TerraSwap
The dashboard shows the volume of swaps in TerraSwap for the period from April to June. You can clearly see the period from mid May marks a clear upward trend in both the volume of swaps and also the addresses interacting with TerraSwap. The LUNA-UST pair accounts for more than 75% of the swap volume.
Please Note: This is an interactive dashboard
Developer Notes
We decode the msg_value from terra.messages for TerraSwap swap transactions and build the volumes from it. The SQL also has the swap and fee values from terra.msg_events for the transaction. The logic for prices in USD needs to be added to get the swap values in USD.
SQL
SELECT
BLOCK_DATE,
pair,
count(distinct transaction_id) as txn_count,
count(distinct wallet_address) as user_cnt
from
(
SELECT
msgs.block_timestamp :: DATE as BLOCK_DATE,
msgs.block_timestamp as block_timestamp,
msgs.tx_id as transaction_id,
msgs.msg_value : sender as wallet_address,
labels.address as contract_address,
labels.address_name as pair,
msg_events.inp_amount,
msg_events.inp_currency,
msg_events.fee_amount,
msg_events.fee_currency,
msg_events.tgt_amount,
msg_events.tgt_currency
from
(
SELECT
*
from
terra.msgs msgs,
lateral flatten(input => msg_value : execute_msg) fl
) msgs
inner JOIN terra.labels labels on msgs.msg_value : contract = labels.address
AND upper(label)= 'TERRASWAP'
and UPPER(label_subtype) = 'POOL'
AND upper(msgs.key)= 'SWAP'
AND UPPER(msgs.tx_status)= 'SUCCEEDED'
INNER JOIN (
SELECT
tx_id,
src.value : amount as inp_amount,
src.value : denom as inp_currency,
fee.value : amount as fee_amount,
fee.value : denom as fee_currency,
tgt.value : amount as tgt_amount,
tgt.value : denom as tgt_currency
from
terra.msg_events,
lateral flatten(
input => event_attributes : "0_amount"
) src,
lateral flatten(
input => event_attributes : "1_amount"
) fee,
lateral flatten(
input => event_attributes : "2_amount"
) tgt
where
upper(event_type)= 'TRANSFER'
) msg_events ON msgs.tx_id = msg_events.tx_id
WHERE
block_date BETWEEN '2021-04-01'
AND '2021-06-30'
) A
group by
1,
2
order by
1,
2