select

block_timestamp,

tx_id,

instructions[0]:programId::string as program_id

from solana.core.fact_transactions

where block_timestamp >= '2022-01-01'

and block_timestamp < '2022-02-01'

and SUCCEEDED = 'TRUE'

UNION

select

block_timestamp,

tx_id,

instructions[0]:programId::string as program_id

from solana.core.fact_transactions

where block_timestamp >= '2022-02-01'

and block_timestamp < '2022-03-01'

and SUCCEEDED = 'TRUE'

),

labels as (

SELECT

address,

label

FROM solana.core.dim_labels

)

SELECT

distinct(program_id) as contract,

COALESCE(label, 'Null_label') as label,

Count(tx_id) as nb_times_used

from transactions left join labels on transactions.program_id = labels.address

group by 1,2

order by 3 desc