with base as (select from_address, min(block_timestamp) as first_bond_pooled from flipside_prod_db.thorchain.bond_events where split(memo, ':')[0] is not null and split(memo, ':')[0] != '' and split(memo, ':')[0] != 'UNBOND' and split(memo, ':')[0] ilike '%bond%' and split(memo, ':')[2] is null group by 1), base2 as (select from_address, min(block_timestamp) as first_bond from flipside_prod_db.thorchain.bond_events where split(memo, ':')[0] = '' and bond_type = 'bond_paid' group by 1), base3 as (select a.from_address, date_trunc('week', a.first_bond_pooled) as first_bonding_pooled, b.first_bond, case when (first_bond is null or first_bond > a.first_bond_pooled) then 'First Time Bonding RUNE' else 'Bonded RUNE Before' end as categories, 1 as numbering, sum(numbering) over (order by first_bonding_pooled) as cum_numbering from base a left join base2 b on a.from_address = b.from_address order by cum_numbering desc) select from_address, sum(case when (split(memo, ':')[0] ilike '%bond%' and split(memo, ':')[2] is null) then e8/1e8 when (split(memo, ':')[0] ilike '%unbond%' and split(memo, ':')[3] is null) then ifnull(-e8/1e8,0) end) as rune_bonded_pooled, case when rune_bonded_pooled > 0 then 'still Bonding RUNE' else 'Unbonded RUNE' end as categories, case when rune_bonded_pooled <= 0 then '7. Unbonded RUNE' when rune_bonded_pooled > 0 and rune_bonded_pooled <= 1000 then '1.Bonded RUNE > 0 and <= 1K' when rune_bonded_pooled > 1000 and rune_bonded_pooled <= 10000 then '2.Bonded RUNE > 1K and <= 10K' when rune_bonded_pooled > 10000 and rune_bonded_pooled <= 100000 then '3.Bonded RUNE > 10K and <= 100K' when rune_bonded_pooled > 100000 and rune_bonded_pooled <= 200000 then '4.Bonded RUNE > 100K and <= 200K' when rune_bonded_pooled > 200000 and rune_bonded_pooled <= 300000 then '5.Bonded RUNE > 200K and <= 300K' else '6.Bonded RUNE > 300K' end as categories2, 1 as numbering from flipside_prod_db.thorchain.bond_events where bond_type in ('bond_paid','bond_returned') and from_address in (select from_address from base3) group by 1 order by 2 desc --where (first_bond is null or first_bond > first_bond_pooled)

Bond Providers on THORChain