0xHaM-dUntitled Query
Updated 2023-01-04Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- SQL Credit https://app.flipsidecrypto.com/velocity/queries/4f3f5cf0-ff72-4ab3-bb5d-4fc6b7a13b47
with airdropees as (
select distinct tx_to as address from
solana.core.fact_transfers
where tx_from in (
'9AhKqLR67hwapvG8SA2JFXaCshXc9nALJjpKaHZrsbkw',
'6JZoszTBzkGsskbheswiS6z2LRGckyFY4SpEGiLZqA9p')
and mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'
and block_timestamp > '2022-12-24'
)
, tb1 as (
SELECT
--date_trunc('day',tx.block_timestamp) as date,
time_slice(tx.block_timestamp, 12, 'HOUR') as date,
case when tx.tx_to = '9AhKqLR67hwapvG8SA2JFXaCshXc9nALJjpKaHZrsbkw' then 'bonk airdrop address'
when tx.tx_to = '6JZoszTBzkGsskbheswiS6z2LRGckyFY4SpEGiLZqA9p' then 'bonk new airdrop address'
when tx.tx_to = 'BqnpCdDLPV2pFdAaLnVidmn3G93RP2p5oRdGEY2sJGez' then 'orca bonk-sol pool'
when tx.tx_to = '5P6n5omLbLbP4kaPGL8etqQAHEx2UCkaUyvjLDnwV4EY' then 'orca bonk-usdc pool'
when tx.tx_to = '2PFvRYt5h88ePdQXBrH3dyFmQqJHTNZYLztE847dHWYz' then 'dex bonk-usdc pool'
when tx.tx_to = 'DBR2ZUvjZTcgy6R9US64t96pBEZMyr9DPW6G2scrctQK' then 'bonk dao wallet'
when tx.tx_to = '4CUMsJG7neKqZuuLeoBoMuqufaNBc2wdwQiXnoH4aJcD' then 'bonk team wallet'
when tx.tx_to = '2yBBKgCwGdVpo192D8WZeAtqyhyP8DkCMnmTLeVYfKtA' then 'bonk marketing wallet'
else coalesce(lto.label,'unlabeled') end as to_label,
coalesce(lto.label_type,'unlabeled') as to_label_type,
case when tx.tx_to in (select address from airdropees) then 'airdrop recipient'
else coalesce(lto.label_subtype,'unlabeled user') end as to_label_subtype,
case when tx.tx_from = '9AhKqLR67hwapvG8SA2JFXaCshXc9nALJjpKaHZrsbkw' then 'bonk airdrop address'
when tx.tx_from = '6JZoszTBzkGsskbheswiS6z2LRGckyFY4SpEGiLZqA9p' then 'bonk new airdrop address'
when tx.tx_from = 'BqnpCdDLPV2pFdAaLnVidmn3G93RP2p5oRdGEY2sJGez' then 'orca bonk-sol pool'
when tx.tx_from = '5P6n5omLbLbP4kaPGL8etqQAHEx2UCkaUyvjLDnwV4EY' then 'orca bonk-usdc pool'
when tx.tx_from = '2PFvRYt5h88ePdQXBrH3dyFmQqJHTNZYLztE847dHWYz' then 'dex bonk-usdc pool'
when tx.tx_from = 'DBR2ZUvjZTcgy6R9US64t96pBEZMyr9DPW6G2scrctQK' then 'bonk dao wallet'
when tx.tx_from = '4CUMsJG7neKqZuuLeoBoMuqufaNBc2wdwQiXnoH4aJcD' then 'bonk team wallet'
when tx.tx_from = '2yBBKgCwGdVpo192D8WZeAtqyhyP8DkCMnmTLeVYfKtA' then 'bonk marketing wallet'
else coalesce(lfr.label,'unlabeled') end as from_label,
coalesce(lfr.label_type,'unlabeled') as from_label_type,
Run a query to Download Data