SELECT date_trunc('d',block_timestamp) as day,

Case --'LunaCrash','FTXandAlamedaCrash','3ACCrash','CelsiusandVoyagerCrash','RoninHack','WormHoleHack','NomadBridgeHack','FtxHack','BeanstalkHack','WintermuteHack','ElrondHack','BinancebridgeHack','MangomarketsHack','QubitFinanceHack','TinymanHack'

WHEN {{Event}}='LunaCrash' then day between '2022-05-11 00:00:00' and '2022-05-14 00:00:00'

WHEN {{Event}}='FTXandAlamedaCrash' then day between '2022-11-06 00:00:00' and '2022-11-12 00:00:00'

WHEN {{Event}}= '3ACCrash' then day between '2022-06-16 00:00:00' and '2022-06-28 00:00:00'

WHEN {{Event}}= 'CelsiusandVoyagerCrash' then day between '2022-07-03 00:00:00' and '2022-07-14 00:00:00'

WHEN {{Event}}= 'RoninHack' then day between '2022-03-22 00:00:00' and '2022-03-25 00:00:00'

WHEN {{Event}}= 'WormHoleHack' then day between '2022-02-01 00:00:00' and '2022-02-05 00:00:00'

WHEN {{Event}}= 'NomadBridgeHack' then day between '2022-08-01 00:00:00' and '2022-08-05 00:00:00'

WHEN {{Event}}= 'FtxHack' then day between '2022-11-11 00:00:00' and '2022-11-14 00:00:00'

WHEN {{Event}}= 'BeanstalkHack' then day between'2022-04-16 00:00:00' and '2022-04-19 00:00:00'

WHEN {{Event}}= 'WintermuteHack' then day between '2022-09-19 00:00:00' and '2022-09-23 00:00:00'

WHEN {{Event}}= 'ElrondHack' then day between '2022-06-04 00:00:00' and '2022-06-07 00:00:00'

WHEN {{Event}}= 'BinancebridgeHack' then day between '2022-10-05 00:00:00' and '2022-10-08 00:00:00'

WHEN {{Event}}= 'MangomarketsHack' then day between '2022-10-10 00:00:00' and '2022-10-14 00:00:00'

WHEN {{Event}}= 'QubitFinanceHack' then day between '2022-01-27 00:00:00' and '2022-01-30 00:00:00'

WHEN {{Event}}= 'TinymanHack' then day between '2021-12-31 00:00:00' and '2022-01-03 00:00:00'

END as event,

sum(amount) as transfer_volume,

count(DISTINCT ASSET_SENDER) as senders,

count(DISTINCT RECEIVER) as receivers,

median(amount) as median_transfer_size,

avg(amount) as average_transfer_size,

count (distinct tx_id) as txs,

txs/senders as AVG_txs

FROM algorand.core.ez_transfer

where ASSET_NAME LIKE 'USDC'

GROUP BY 1,2

having event=true),

Arbitrum as (

SELECT date_trunc('d',block_timestamp) as day,

Case --'LunaCrash','FTXandAlamedaCrash','3ACCrash','CelsiusandVoyagerCrash','RoninHack','WormHoleHack','NomadBridgeHack','FtxHack','BeanstalkHack','WintermuteHack','ElrondHack','BinancebridgeHack','MangomarketsHack','QubitFinanceHack','TinymanHack'

WHEN {{Event}}='LunaCrash' then day between '2022-05-11 00:00:00' and '2022-05-14 00:00:00'

WHEN {{Event}}='FTXandAlamedaCrash' then day between '2022-11-06 00:00:00' and '2022-11-12 00:00:00'