SELECT block_timestamp ,tx_id, transfer_type , sender , amount/pow(10,get(raw_metadata , 1):exponent) AS token_amount , project_name AS currency, receiver

FROM cosmos.core.fact_transfers a

JOIN osmosis.core.dim_labels b

ON a.currency = get(b.raw_metadata , 0):aliases[0]

WHERE tx_succeeded = TRUE

AND project_name = 'ATOM'

),

In_flow AS (

SELECT receiver , sum(token_amount) AS total_in -- ,token_amount,currency

FROM base_transfers_with_usd_value

WHERE transfer_type IN ('IBC_TRANSFER_IN' , 'COSMOS')

GROUP BY 1

),

out_flow AS (

SELECT sender , sum(token_amount) AS total_out -- ,token_amount,currency

FROM base_transfers_with_usd_value

WHERE transfer_type IN ('IBC_TRANSFER_OUT' , 'COSMOS')

AND sender IN (SELECT receiver FROM In_flow)

GROUP BY 1

),

base_Approximate_balance AS (

SELECT receiver AS cosmos_address, total_in ,total_out ,abs(total_in - total_out) AS Approximate_balance , Approximate_balance * 9.9 AS Value_usd

FROM In_flow a

JOIN out_flow b

ON a.receiver = b.sender

),

base_validator_address AS (

SELECT concat ('cosmos', SUBSTR(attribute_value , -39)) AS address,

substring(address,1,len(address)-6)as cosmos_likly_address ,