SELECT DISTINCT address, ADDRESS_LABEL, address_label_subtype, ADDRESS_NAME

FROM terra.daily_balances

WHERE ADDRESS_LABEL_TYPE = 'cex' and ADDRESS_LABEL NOT LIKE 'group%'

and ADDRESS_NAME = 'kucoin_deposits'

-- WHERE address = 'terra1rvxcszyfecrt2v3a7md8p30hvu39kj6xf48w9e' --Kucoin withdrawaladdress

, tx as (

SELECT date_trunc('week', block_timestamp) as week, tx_id,

msg_value:amount[0]:amount / POW(10, 6) as amount,

msg_value:from_address::string as from_address,

CASE

WHEN msg_value:amount[0]:denom::string = 'uluna' THEN 'LUNA'

WHEN msg_value:amount[0]:denom::string = 'uusd' THEN 'UST'

WHEN msg_value:amount[0]:denom::string = 'usdr' THEN 'SDT'

WHEN msg_value:amount[0]:denom::string = 'ukrw' THEN 'KRT'

ELSE 'Other'

END as currency

FROM terra.msgs

WHERE MSG_MODULE = 'bank' and msg_value:to_address::string IN (SELECT address FROM kucoin_deposits) --and array_size(msg_value:amount) > 1

-- LIMIT 500

UNION

SELECT date_trunc('week', block_timestamp) as week, tx_id,

msg_value:execute_msg:transfer:amount / POW(10, 6) as amount,

msg_value:sender::string as from_address,

l.address_name as currency

FROM terra.msgs t LEFT OUTER JOIN terra.labels l ON t.msg_value:contract::string = l.address

WHERE msg_value:execute_msg:transfer:recipient::string IN (SELECT address FROM kucoin_deposits)

SELECT week, currency, COUNT(DISTINCT tx_id) as tx_count

FROM tx

GROUP BY 1, 2