SELECT date_day AS date

FROM crosschain.core.dim_dates

WHERE date_day >= CURRENT_DATE - 365 AND date_day <= CURRENT_DATE

),

single_action_txs AS (

SELECT tx_id, max(attribute_value) AS action

FROM evmos.core.fact_msg_attributes

WHERE msg_type = 'message'

AND attribute_key = 'action'

AND attribute_value = '/ethermint.evm.v1.MsgEthereumTx'

GROUP BY tx_id

HAVING count(*) = 1

),

event_logs AS (

SELECT tx.tx_id, m.block_timestamp,

TRY_PARSE_JSON(attribute_value) AS log,

LOWER(log:address) AS contract_address,

log:topics AS topics,

LOWER(TRY_BASE64_DECODE_BINARY(log:data)::variant) AS data

FROM single_action_txs tx

JOIN evmos.core.fact_msg_attributes m ON m.tx_id = tx.tx_id AND m.msg_type = 'tx_log'

),

token_transfers AS (

SELECT tx_id, block_timestamp,

contract_address,

'0x' || SUBSTR(topics[1], 21) AS from_address,

'0x' || SUBSTR(topics[2], 21) AS to_address,

ethereum.public.udf_hex_to_int(data) AS amount,

address_name AS name

FROM event_logs

LEFT JOIN evmos.core.dim_labels ON contract_address = address

WHERE lower(topics[0]) = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'