m.block_timestamp,

m.tx_id,

m.msg_value:sender::string as u_address,

l.label,

event_attributes:"1_amount"::float/POW(10,6) as amount,

event_attributes:"0_action"::string as action

FROM terra.msgs m

INNER JOIN terra.msg_events me on m.tx_id = me.tx_id

left join terra.labels l on (m.msg_value:sender::string = l.address)

WHERE

-- m.tx_id = '1DB3395685CF54199E299AF306A06B89578D422BF1E3657002DFFD7BFE3DC4A4' --reward claim tx

msg_value:contract::string = 'terra17f7zu97865jmknk7p2glqvxzhduk78772ezac5' --MIR staking address

AND msg_value:execute_msg:withdraw = '{}' --claim

AND event_attributes:"0_action" LIKE 'withdra%'

AND event_index = 1

AND m.block_timestamp >= getdate() - interval '1 month'

AND event_attributes:"1_amount"::float/POW(10,6) <> 0

ORDER BY 1 DESC)

SELECT

-- date_trunc('day',block_timestamp) as day,

CASE WHEN label IS NOT NULL THEN label ELSE u_address END as wallet,

sum(amount) as staking_rewards

FROM pre

GROUP BY 1

ORDER BY 2 DESC