WITH BRIDGE_TO_ETH AS (
SELECT BLOCK_TIMESTAMP, TX_ID, MSG_VALUE:"sender"::string, MSG_VALUE:"execute_msg":"burn":"amount"::float/1e6 AS AMOUNT FROM terra.msgs
WHERE MSG_VALUE:"contract"::string = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun'
AND MSG_VALUE:"execute_msg":"burn":"amount" IS NOT NULL
AND TX_STATUS = 'SUCCEEDED'),
BRIDGE_TO_TERRA AS (
SELECT BLOCK_TIMESTAMP, TX_ID, MSG_VALUE:"execute_msg":"mint":"amount"::float/1e6 AS AMOUNT, MSG_VALUE:"execute_msg":"mint":"recipient"::string AS ADDRESS FROM terra.msgs
WHERE MSG_VALUE:"contract"::string = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun'
AND MSG_VALUE:"execute_msg":"mint":"amount" IS NOT NULL
AND MSG_VALUE:"sender"::string = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc'
)
SELECT DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS BLOCKTIME, SUM(AMOUNT) FROM BRIDGE_TO_ETH GROUP BY BLOCKTIME ORDER BY BLOCKTIME
-- SELECT * FROM BRIDGE_TO_TERRA WHERE DATE_TRUNC('Day', BLOCK_TIMESTAMP) = '2021-08-28T00:00:00Z'