WITH Temp AS

-- CosmosHub

SELECT

BLOCK_TIMESTAMP::DATE As Day,

'CosmosHub' AS Blockchain,

SUM(1.0 * CAST(FEE AS INTEGER) / POW(10, 6)) AS FEE_ATOM

FROM cosmos.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= {{start_date}}

AND FEE_DENOM = 'uatom'

GROUP BY Day

--ORDER BY Day;

UNION ALL

-- Osmosis

SELECT

BLOCK_TIMESTAMP::DATE AS Day,

'Osmosis' AS Blockchain,

SUM(1.0 * CAST(regexp_substr(FEE, '^[0-9]*') AS INT) / pow(10,6)) AS FEE_ATOM

FROM osmosis.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= {{start_date}}

AND RIGHT(FEE, LEN(FEE) - LEN(regexp_substr(FEE, '^[0-9]*'))) IN ('ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2', 'uatom')

GROUP BY Day

SELECT *

FROM Temp

ORDER BY Day, Blockchain;