label as pool_name,

sum(CASE WHEN asset_1 LIKE 'CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF' OR asset_1 LIKE 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4' THEN val_1

WHEN asset_2 LIKE 'CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF' OR asset_2 LIKE 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4' THEN val_2 END) as swap_volume,

count(DISTINCT tx_id) as swaps,

COUNT(DISTINCT sender) as users

from (SELECT block_timestamp,

tx_id,

label,

MESSAGE_VALUE:sender as sender,

CASE WHEN attributes:wasm:amount_0 is NULL THEN attributes:wasm:offer_amount

ELSE attributes:wasm:amount_0 END / power(10,6) as val_1,

CASE WHEN attributes:wasm:amount_1 is NULL THEN attributes:wasm:return_amount

ELSE attributes:wasm:amount_1 END / power(10,6) as val_2,

attributes:wasm:ask_asset as asset_1,

attributes:wasm:offer_asset as asset_2

FROM terra.core.fact_messages

LEFT outer JOIN terra.core.dim_address_labels

ON message_value:contract = address

WHERE label LIKE '%astroport%'

AND (label LIKE '%axlusdc%' OR label LIKE '%axlusdt%')

AND label_subtype LIKE 'pool'

AND MESSAGE_VALUE:msg:swap is not null

)

where day>'2022-11-04'

GROUP BY 1,2