from osmosis.core.ez_prices

where symbol = 'OSMO'

and RECORDED_HOUR::date >= '2023-01-01'

group by 1)

,

luna_price as ( select date(block_timestamp) as date, sum(TO_AMOUNT/pow(10,TO_DECIMAL))/sum(FROM_AMOUNT/pow(10,from_decimal)) as luna_price

from terra.core.ez_swaps

where FROM_CURRENCY = 'uluna'

and TO_CURRENCY = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4'

and date >= '2023-01-01'

and from_amount < pow(10,8)

and to_amount < pow(10,8)

group by 1)

,

final as ( select date(block_timestamp) as date, VALIDATOR_ADDRESS,'Osmo' as token, count(DISTINCT(tx_id)) as total_swap , sum(AMOUNT/pow(10,6)*osmo_price) as volume

from osmosis.core.fact_staking a left outer join osmo_price b on a.block_timestamp::date = b.date

where block_timestamp >= '2023-01-01' and action = 'delegate'

group by 1,2,3)

,

luna_final as ( select date(block_timestamp) as date,VALIDATOR_LABEL as validator_address, 'Luna' as token, count(DISTINCT(tx_id)) as total_swap , sum((AMOUNT)*luna_price) as volume

from terra.core.ez_staking a left outer join luna_price b on a.block_timestamp::date = b.date

where block_timestamp >= '2023-01-01' and action in ( 'Delegate') and TX_SUCCEEDED = 'TRUE'

group by 1,2,3)

,

osmo_f as ( select LABEL ,token, sum(volume) as total_volume, rank() over (order by total_volume desc) as rank

from final a join osmosis.core.fact_validators b on a.validator_address = b.address

group by 1,2

order by 3 desc

limit 10 )

,

luna_f as ( select validator_address as LABEL ,token, sum(volume) as total_volume, rank() over (order by total_volume desc) as rank

from luna_final

where validator_address is not null