with synth_buy as (select block_timestamp, from_address as address, iff(charindex('-', pool_name) = 0, pool_name, LEFT(pool_name, CHARINDEX('-', pool_name) - 1)) as synth, to_amount as synth_amount, from_amount as rune_amount, to_amount/from_amount as synth_value_in_rune from thorchain.swaps where to_asset like '%/%'), avg_daily_synth_price_in_rune as ( select date_trunc('day', block_timestamp) as date, synth, avg(synth_value_in_rune) as avg_buy_synth_price from synth_buy group by date, synth ), daily_buyers as (select date_trunc('day', block_timestamp) as date, count(distinct(from_address)) as n_users, iff(charindex('-', pool_name) = 0, pool_name, LEFT(pool_name, CHARINDEX('-', pool_name) - 1)) as synth from thorchain.swaps group by date, synth ), current_synth_price as ( select date_trunc('day', block_timestamp) as date, synth, avg(synth_value_in_rune) as current_avg_synth_price from synth_buy where date = current_date() group by date, synth ), combined as (select * from avg_daily_synth_price_in_rune left join daily_buyers using (date, synth)), price_shift as (select c.*, p.current_avg_synth_price, ((p.current_avg_synth_price - c.avg_buy_synth_price)/c.avg_buy_synth_price)*100 as "% change" from combined c, current_synth_price p where c.synth = p.synth), final_values as (select (select sum(n_users) from price_shift where "% change" >= 0 and "% change" <= 10) as "0/10%", (select sum(n_users) from price_shift where "% change" > 10 and "% change" <= 20) as "10/20%", (select sum(n_users) from price_shift where "% change" > 20 and "% change" <= 30) as "20/30%", (select sum(n_users) from price_shift where "% change" > 30 and "% change" <= 40) as "30/40%", (select sum(n_users) from price_shift where "% change" > 40 and "% change" <= 50) as "40/50%", (select sum(n_users) from price_shift where "% change" > 50 and "% change" <= 60) as "50/60%", (select sum(n_users) from price_shift where "% change" > 60 and "% change" <= 70) as "60/70%", (select sum(n_users) from price_shift where "% change" > 70 and "% change" <= 80) as "70/80%", (select sum(n_users) from price_shift where "% change" > 80 and "% change" <= 90) as "80/90%", (select sum(n_users) from price_shift where "% change" > 90 and "% change" <= 100) as "90/100%", (select sum(n_users) from price_shift where "% change" > 100) as "100%+", (select sum(n_users) from price_shift where "% change" < 0 and "% change" >= -10) as "-10/0%", (select sum(n_users) from price_shift where "% change" < -10 and "% change" >= -20) as "-20/-10%", (select sum(n_users) from price_shift where "% change" < -20 and "% change" >= -30) as "-30/-20%", (select sum(n_users) from price_shift where "% change" < -30 and "% change" >= -40) as "-40/-30%", (select sum(n_users) from price_shift where "% change" < -40 and "% change" >= -50) as "-50/-40%", (select sum(n_users) from price_shift where "% change" < -50 and "% change" >= -60) as "-60/-50%", (select sum(n_users) from price_shift where "% change" < -60 and "% change" >= -70) as "-70/-60%", (select sum(n_users) from price_shift where "% change" < -70 and "% change" >= -80) as "-80/-70%", (select sum(n_users) from price_shift where "% change" < -80 and "% change" >= -90) as "-90/-80%", (select sum(n_users) from price_shift where "% change" < -90 and "% change" >= -100) as"-100/-90%" from price_shift limit 1) select bin, n_synth_buys from final_values unpivot( n_synth_buys for bin in ("0/10%", "10/20%", "10/20%", "20/30%", "30/40%", "40/50%", "50/60%", "60/70%", "70/80%", "80/90%", "100%+", "-10/0%", "-20/-10%", "-30/-20%", "-40/-30%", "-50/-40%", "-60/-50%", "-70/-60%", "-80/-70%", "-90/-80%", "-100/-90%" ) )u

Price Shift for Synth Holders