-- forked from Top 100 Programs in Period by Unique Signers (Excl. System Programs) copy @ https://flipsidecrypto.xyz/edit/queries/161464d1-1f92-4872-bfa2-13bb9b2a0400

-- forked from marqu / Top 100 Programs in Period by Unique Signers (Excl. System Programs) @ https://flipsidecrypto.xyz/marqu/q/V9OYDwyvKWRD/top-100-programs-in-period-by-unique-signers-excl-system-programs

with

programs as (

select

tx_id,

program_id,

ifnull(label_type, 'unlabeled') as label,

signers[0] as fee_payer,

f.value as signer

from solana.core.fact_events events

inner join lateral flatten (input => signers) f

left join solana.core.dim_labels labels

on events.program_id = labels.address

where succeeded = {{tx_succeeded}}

and (not labels.label = 'solana' or labels.label is null)

and block_timestamp ::date > current_date() - interval '{{days}} days'

),

aggregated as (

select

label,

count(distinct tx_id) as txs,

count(distinct signer) as signers,

count(distinct fee_payer) as fee_payers

from programs