Exchange flows are an essential metric in the cryptocurrency market as they offer insights into market sentiment and potential market volatility. This dashboard provides historical data on CEX flows on the NEAR blockchain, allowing users to analyze trends and patterns to make informed decisions.

The primary goal of this dashboard is to offer users historical data that can be used to anticipate potential outcomes of future events, particularly during times of critical market events when this dashboard is likely to be in high demand.

Through this dashboard, users will gain insights into several crucial metrics such as market dominance, user base, volume, and hot wallet balances of different CEXs. Additionally, users can identify interesting trends in various metrics across volatile events in the past.

Finally, the dashboard also sheds light on how CEXs structure their wallets for processing deposits and withdrawals, enabling users to better understand the mechanics behind CEX flows on the NEAR blockchain.

Methodology for creating the dashboard:

1. Data source: Use the DIM label table as the primary data source for the dashboard. It contains information on transfers to and from the NEAR chain by seven recognized CEXs: Binance, Huobi, Coinbase, Okex, Gate.io, Kucoin, and Mexc.

2. Data cleaning: Since the analysis focuses only on the aforementioned CEXs, filter out any other CEXs that support the NEAR chain but do not have their hot or cold wallets identified in the label table.

3. Data visualization: Create visualizations such as pie charts, bar graphs, and line charts to represent the inflows and outflows of assets from NEAR to CEX and vice versa. To provide a clearer understanding of the charts, place the transfers from and to CEXs alongside one another.

4. Dashboard structure: Structure the dashboard with three tabs for easier analysis. The first tab should provide an overview of transfers to and from CEXs, including the percentage of inflows and outflows and the volume of transactions. The second tab should be focused on the CEXs themselves, showing the number of users and volume by CEX. The third tab should be based on dApps in the ecosystem that users who onboarded funds from CEXes are using.

5. Address labeling: Use the Flipside database to identify the hot wallets and deposit wallets for the CEXs. Hot wallets rarely change and can be used, so label them with the subtype "hot_wallet." Deposit wallets are created for each new user, so identify them manually and label them with the subtype "deposit_wallet."

Overall, this methodology will provide a structured approach to creating a dashboard that accurately represents the inflows and outflows of assets from NEAR to CEX and vice versa, as well as the CEXes and dApps that are driving user adoption.

For the context of this analysis we define deposit address as wallets that:

Sent all their outgoing token / avax transfers to a CEX All those transfers must go to the same dex project_name or in SQL terms [1].

WITH manual_deposit_wallets AS (
  SELECT DISTINCT from_address AS deposit_address, cex_name
  FROM asset_transfers
  LEFT JOIN cex_hot_wallets c ON c.hot_wallet = to_address
  WHERE deposit_address NOT IN (SELECT hot_wallet FROM cex_hot_wallets)
  GROUP BY deposit_address, cex_name
  --All asset transfers from that address must to to same CEX
  sum(IFF(cex_name IS NULL, 1, 0)) = 0 
  QUALIFY 1 = count(DISTINCT cex_name) OVER (PARTITION BY deposit_address)
)

CEX wallet structure

It's common knowledge that CEXes do have hot wallet(s) that are connected to the internet and perform active tasks like withdrawals and cold wallet(s) for secure asset storage.

However there is another part of the CEX that is often overlooked: Deposit wallets. These are wallets created and controlled by the CEX on behalf of each user. Users send their funds to the deposit wallet instead of the the hot wallet. This enables the CEX to know which user sent the asset without needing any additional information like a tx memo [1].