with swap_address as(

SELECT address, address_name FROM terra.labels

WHERE address_name ilike '%terraswap%lp'

)

SELECT swap_address.address_name as swap_pair, COUNT(*) as total_transactions,

SUM(msg_value:execute_msg:send:amount::float / POW(10,6)) AS swap_volume

FROM terra.msgs JOIN swap_address ON msg_value:contract::string = swap_address.address

WHERE msg_value:contract::string IN (

SELECT address FROM terra.labels WHERE address_name ilike '%terraswap%lp'

)

AND tx_status = 'SUCCEEDED' AND block_timestamp > dateadd(year, -1, getdate())

GROUP BY swap_address.address_name ORDER BY swap_volume