with pricet1 as ( select block_timestamp, t2.address_name as asset1, t3.address_name as asset2, swap_from_amount, swap_to_amount, swaP_from_mint, swap_to_mint from solana.core.fact_swaps t1 join Solana.core.dim_labels t2 on t1.swap_from_mint = t2.address join solana.core.dim_labels t3 on t1.swap_to_mint = t3.address where SUCCEEDED = 'TRUE'), pricet2 as ( select address,label,address_name from solana.core.dim_labels where address_name ilike '%usd%' and label_subtype='token_contract'), pricet as ( select block_timestamp::date as date1, swap_from_mint, sum(swap_from_amount) as volume1, sum(swap_to_amount) as volume2, volume2/volume1 as usdprice from pricet1 where swap_to_mint in (select address from pricet2) and swap_to_amount > 0 and swap_from_amount > 0 group by 1,2), hacktable as ( select date_trunc(hour, block_timestamp) as hour_time, label, tx_id, tx_from as victim, tx_to as hacker_wallet, usdprice, amount from solana.core.fact_transfers t left join solana.core.dim_labels l on t.mint = l.address left join pricet p on t.mint = p.swap_from_mint and t.block_timestamp::date = p.date1::date where tx_to in ('CEzN7mqP9xoxn2HdyW6fjEJ73t7qaX9Rp2zyS6hb3iEu','GeEccGJ9BEzVbVor1njkBCCiqXJbXVeDHaXDCrBDbmuy','5WwBYgQG6BdErM2nNNyUmQXfcUnB68b6kesxBywh1J3n','Htp9MGP8Tig923ZFY7Qf2zzbMUmYneFRAhSp7vSg4wxV')), table1 as ( select victim, sum (usdprice*amount) as Stolen_Volume from hacktable group by 1) select case when Stolen_Volume < 5 then 'Less Than $5 Loss' when Stolen_Volume >= 5 and Stolen_Volume < 10 then 'Between $5 and $10 Loss' when Stolen_Volume >= 10 and Stolen_Volume < 50 then 'Between $10 and $50 Loss' when Stolen_Volume >= 50 and Stolen_Volume < 100 then 'Between $50 and $100 Loss' when Stolen_Volume >= 100 and Stolen_Volume < 250 then 'Between $100 and $250 Loss' when Stolen_Volume >= 250 and Stolen_Volume < 500 then 'Between $250 and $500 Loss' when Stolen_Volume >= 500 and Stolen_Volume < 1000 then 'Between $500 and $1000 Loss' when Stolen_Volume >= 1000 and Stolen_Volume < 5000 then 'Between $1000 and $5000 Loss' when Stolen_Volume >= 5000 and Stolen_Volume < 10000 then 'Between $5000 and $10000 Loss' when Stolen_Volume >= 10000 and Stolen_Volume < 20000 then 'Between $10000 and $20000 Loss' else 'More Than $20000 Loss' end as type, count(victim) from table1 group by 1

Solana Hack on August 2022