with top_10_program as
(
select label.LABEL
from solana.core.fact_events as event
JOIN solana.core.dim_labels as label
on event.PROGRAM_ID=label.ADDRESS
where date(BLOCK_TIMESTAMP)>='2022-06-01' and SUCCEEDED ='TRUE'
group by label.LABEL
order by count(*) desc
limit 10
)
select date(BLOCK_TIMESTAMP) as day, t2.LABEL, count(*)
from solana.core.fact_events as t1 JOIN solana.core.dim_labels as t2
on t1.PROGRAM_ID=t2.ADDRESS
where SUCCEEDED ='TRUE' and date(BLOCK_TIMESTAMP)>='2022-01-01'
and t2.LABEL in
(select LABEL from top_10_program)
group by date(BLOCK_TIMESTAMP),t2.LABEL
order by 1