SELECT

CREATED_AT,

CAST(CALL:returnValues:destinationChain AS STRING) AS DESTINATION_CHAIN,

CAST(CALL:chain AS STRING) AS SOURCE_CHAIN,

CONCAT(

CAST(CALL:chain AS STRING),

' -> ',

CAST(CALL:returnValues:destinationChain AS STRING)

) AS path,

COALESCE(CAST(data:value AS FLOAT), 0) AS volume,

CAST(id AS STRING) AS id,

lower(COALESCE(CAST(CALL:receipt:from AS STRING),CAST(call:transaction:from as STRING))) as user_address,

CAST(TIME_SPENT:total AS FLOAT) AS TIME_SPENT

FROM axelar.axelscan.fact_gmp

),

total_metrics AS (

SELECT

SOURCE_CHAIN,

COUNT(id) AS "Total Transactions",

SUM(VOLUME) AS "Total Volume",

COUNT(DISTINCT user_Address) AS "Total Users"

FROM total_Data

WHERE SOURCE_CHAIN IS NOT NULL

GROUP BY SOURCE_CHAIN

),

monthly_data AS (

SELECT

SOURCE_CHAIN,

DATE_TRUNC('month', CREATED_AT) AS month,

SUM(VOLUME) AS total_volume,

COUNT(id) AS total_transactions,

COUNT(DISTINCT user_address) AS total_users

FROM total_Data

WHERE SOURCE_CHAIN IS NOT NULL

GROUP BY SOURCE_CHAIN, DATE_TRUNC('month', CREATED_AT)