select

(BLOCK_TIMESTAMP) AS RAIN_DATE,

TRY_PARSE_JSON(replace(TX_RECEIPT[1]:outcome:logs[0],'EVENT_JSON:','')) : data[0] : new_owner_id as USER

from flipside_prod_db.mdao_near.transactions

where TRY_PARSE_JSON(replace(TX_RECEIPT[1]:outcome:logs[0],'EVENT_JSON:','')) : data[0] : old_owner_id = 'aurora'

and tx_receiver = 'aurora'

and tx_signer = 'relay.aurora'

), FIRST as (

select USER,

a.txn_hash as TXN,

datediff('minute',RAIN_DATE,BLOCK_TIMESTAMP) as DATE_FIRST

from flipside_prod_db.mdao_near.transactions a join RAIN b on a.tx_signer = b.USER

where RAIN_DATE < block_timestamp

), A as (select USER, TXN, min(DATE_FIRST) from FIRST group by 1,2),

maintable as (

select

method_name as ACTION,

USER,

from flipside_prod_db.mdao_near.actions_events_function_call a join A b on a.txn_hash = b.TXN

)

select

Action,

count (distinct txn_hash) as Number_of_Actions,

count (distinct USER) as Number_of_Users

from maintable

where ACTION is not null

group by 1

order by 2 desc

LIMIT 20