Azinn14. the Number of Solana pools
    Updated 2022-11-06
    with t1 as
    (select count(distinct STAKE_POOL) as number from solana.core.fact_stake_pool_actions),
    number as
    (select
    count(case when STAKE_POOL='49Yi1TKkNyYjPAFdR9LBvoHcUjuPX4Df5T5yv39w2XTn' then 1 else null end) as pool_1,
    count(case when STAKE_POOL='5oc4nmbNTda9fx8Tw57ShLD132aqDK65vuHH4RU1K4LZ' then 1 else null end) as pool_2,
    count(case when STAKE_POOL='8szGkuLTAux9XMgZ2vtY39jVSowEcpBfFfD8hXSEqdGC' then 1 else null end) as pool_3,
    count(case when STAKE_POOL='7ge2xKsZXmqPxa3YmXxXmzCp9Hc2ezrTxh6PECaxCwrL' then 1 else null end) as pool_4,
    count(case when STAKE_POOL='stk9ApL5HeVAwPLr3TLhDXdZS8ptVu7zp6ov8HFDuMi' then 1 else null end) as pool_5,
    count(case when STAKE_POOL='CtMyWsrUtAwXWiGr9WjHT5fC3p3fgV8cyGpLTo2LJzG1' then 1 else null end) as pool_6,
    count(case when STAKE_POOL='GUAMR8ciiaijraJeLDEDrFVaueLm9YzWWY9R7CBPL9rA' then 1 else null end) as pool_7,
    date_trunc('day',BLOCK_TIMESTAMP ) as date
    from solana.core.fact_stake_pool_actions
    group by date),

    volume as
    (select
    sum(case when STAKE_POOL='49Yi1TKkNyYjPAFdR9LBvoHcUjuPX4Df5T5yv39w2XTn' then AMOUNT else null end) as pool_1,
    sum(case when STAKE_POOL='5oc4nmbNTda9fx8Tw57ShLD132aqDK65vuHH4RU1K4LZ' then AMOUNT else null end) as pool_2,
    sum(case when STAKE_POOL='8szGkuLTAux9XMgZ2vtY39jVSowEcpBfFfD8hXSEqdGC' then AMOUNT else null end) as pool_3,
    sum(case when STAKE_POOL='7ge2xKsZXmqPxa3YmXxXmzCp9Hc2ezrTxh6PECaxCwrL' then AMOUNT else null end) as pool_4,
    sum(case when STAKE_POOL='stk9ApL5HeVAwPLr3TLhDXdZS8ptVu7zp6ov8HFDuMi' then AMOUNT else null end) as pool_5,
    sum(case when STAKE_POOL='CtMyWsrUtAwXWiGr9WjHT5fC3p3fgV8cyGpLTo2LJzG1' then AMOUNT else null end) as pool_6,
    sum(case when STAKE_POOL='GUAMR8ciiaijraJeLDEDrFVaueLm9YzWWY9R7CBPL9rA' then AMOUNT else null end) as pool_7,
    date_trunc('day',BLOCK_TIMESTAMP ) as date
    from solana.core.fact_stake_pool_actions
    group by date),

    pool_volume as


    (
    select sum(AMOUNT) as volume , STAKE_POOL_NAME from solana.core.fact_stake_pool_actions
    group by 2
    Run a query to Download Data