DATA - Transfers

Credits: chainiqedu#0820

1. Info: Been doing some digging on the Solana.transfers table and want to share the following to see if others agree or in case it helps. TL;DR Can't quite use Solana.transfers yet but I think I'm on the correct lines with understanding and mapping transfers to exchange given the current tables. (edited)

2. [7:30 AM]= = = Part 1: One of the bounty questions asks for an analysis on the volume of transactions being sent to a centralised exchange. For the sake of this thread, let's simplify and say we want to look at transactions where tokens are being transferred to the FTX exchange. Looking up the crosschain.labels table the FTX exchange account address is: 6ZRCB7AAqGre6c72PRz3MHLC73VMYvJ8bi9KHf1HFpNk (this is slightly modified to the correct version, crosschain.labels drops the caps) Looking at transfers to do this address in solana.transfers we can use: SELECT * FROM solana.transfers WHERE tx_to_address = '6ZRCB7AAqGre6c72PRz3MHLC73VMYvJ8bi9KHf1HFpNk' LIMIT 100 However this returns no results = = = (edited)

3. [7:32 AM]= = = Part 2: If we do the opposite and look at transactions that are leaving the FTX exchange, we actually get some results: SELECT * FROM solana.transfers WHERE tx_from_address = '6ZRCB7AAqGre6c72PRz3MHLC73VMYvJ8bi9KHf1HFpNk' LIMIT 100 Looking in particular at the columns tx_from_address and tx_to_address - this appears to be showing a transfer from the FTX exchange to another address = = =

4. [7:32 AM]= = = Part 3: Let's verify this using a blockexplorer tool: https://explorer.solana.com/tx/q7ePbrkr4e2dg72wwrQi6GFvkpsCkYk9FjWeEPqUZzU2JKm6AgnXBQDqQLzd1KA27y2oZGgEqDyMHgXjVZNLSZ4 In the transfer section of the above link, we see 3 fields Authority, Destination & Source. Mapping these to the SQL results we see that tx_from_address maps to Authority, tx_to_address maps to Destination but we see that source does not appear anywhere in the table. Hypothesis (After spending way too much time on explorer.solana.com): There is a global FTX account the manages a bunch of sub accounts for each token (account/address per token) however when performing actions like sending from the wallet the global FTX account (in this case, 6ZRCB7AAqGre6c72PRz3MHLC73VMYvJ8bi9KHf1HFpNk has to authorise ) it is the global address that is used as the from_address. If we flip this around then, what we will see is that when we send from an account to FTX, we won't be sending to the global address we will be sending to the token account address under the global FTX address. = = =Explorer | SolanaLook up transactions and accounts on the various Solana clusters

5. [7:33 AM]= = = Part 4: Testing this out, look at an example where a transaction sends in to FTX https://explorer.solana.com/tx/tGuXFp9TPuLxf2YofJfFh6Jh69KZxx5PW3fvmE96B7eMpKEyfCWGqY6dMczBRNgDvpbbiLeXLZvCpghjcE6ztbQ We see here that the destination address is actually: FE2Ggj1pA47G6nw25crEjv7BvKFU9MAwgSUUMmUuaXmN If we look up who own's that account address, we get the globabl FTX address: https://explorer.solana.com/address/FE2Ggj1pA47G6nw25crEjv7BvKFU9MAwgSUUMmUuaXmN Therefore, if we go back to flipside now and query for the following we should get some results: SELECT * FROM solana.transfers WHERE tx_to_address = 'FE2Ggj1pA47G6nw25crEjv7BvKFU9MAwgSUUMmUuaXmN' Which we do! = = =Explorer | SolanaLook up transactions and accounts on the various Solana clustersExplorer | SolanaLook up transactions and accounts on the various Solana clusters

6. [7:33 AM]= = = Summary Still some work to do to make this usable for the current bounties, but if we can get a list of all the addresses that are under the global FTX address (and other exchanges for that matter) then you should be able to use solana.transfers to find transaction volume This will then open up using the solana_amount field (which isn't always $SOL so more work to do there also) Hope this helps, happy for any feedback = = =

1. great detective work @chainiqedu !! i have some suspicions that it has to do with 'authority' when i saw that in the transactions table, the to and from addresses are the same address. so how would you go about searching for a list of all the addresses that are under the global FTX using the database? (edited)

2. I'm not sure that's possible at the moment looking at the tables. My next thought was to use the flipside data and add to it it by getting the account addresses (or as much of them as possible) from solscan or explorer.solana for the month of december. Longer term do we need to do something like add known accounts for CEX to the labels table? Difficult part with that would be keeping it up to date and pulling the data from somewhere Other points on solana.transfers: I think this only includes Token Transfers (there are 2 types of transfers on Solana that I can see, SOL transfers (transferring Sol from one address to another) and Token Transfers (e.g transferring USDT/SRM from one address to another). These are differentiated by the program_id where for Sol to Sol transfers the program_id is 1111111111111 where as for Token transfers it is TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA Still to check if Sol to Sol is available in the transactions table - I believe the program_id is there but need to look to see if we can use that data somehow Examples Sol Transfer: https://solscan.io/tx/62Jd6BVJnkqso4DEPC1cHsgeNXQzuUkfitCX9xUUr3D7aFCtesokduT7i4jtWWXtCUY5WxYCfWEA9PFveBLxfxEM Token Transfer: https://solscan.io/tx/qgMdWeXDsbmi2BXtcCdvAB6CoNfPEuo8dSjAGLecM2C6rqmFC8BBz6qCV74PT3LvHEGFSXajDEKqXkFa38SRG1x