WITH wallets as (

SELECT DISTINCT address, AVG(balance) as average_staking,

IFF(average_staking > 0, 'YES', 'NO') as active_delegator

FROM terra.daily_balances

WHERE balance_type = 'staked'

AND currency = 'LUNA'

AND address != 'terra1fl48vsnmsdzcv85q5d2q4z5ajdha8yu3nln0mh' -- foundation_staking wallet

GROUP BY 1

ORDER BY average_staking DESC

),

delegators as (

SELECT

DATE(block_timestamp) as date, COUNT(*) as total_transactions,

SUM(

CASE

WHEN msg_value:execute_msg:borrow_stable IS NOT NULL THEN msg_value:execute_msg:borrow_stable:borrow_amount::float / POW(10, 6)

WHEN msg_value:execute_msg:cast_vote IS NOT NULL THEN msg_value:execute_msg:cast_vote:amount::float / POW (10, 6)

WHEN msg_value:coins[0] IS NOT NULL THEN msg_value:coins[0]:amount::float / POW(10, 6)

ELSE 0

END

) as total_volume

FROM terra.msgs

WHERE msg_value:contract::string IN ('terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s', 'terra1897an2xux840p9lrh6py3ryankc6mspw49xse3', 'terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5')

AND msg_value:sender::string IN (SELECT address FROM wallets WHERE active_delegator = 'YES')

AND YEAR(block_timestamp) = YEAR(CURRENT_DATE())

GROUP BY 1

),

other as (

SELECT

DATE(block_timestamp) as date, COUNT(*) as total_transactions,