-- forked from Playwo / Label Justification #1 @ https://flipsidecrypto.xyz/Playwo/q/2023-04-20-12-40-am-SUMZpD

WITH timeframe AS (

SELECT

date_day AS date

FROM crosschain.core.dim_dates

WHERE date_day <= CURRENT_DATE

AND date_day >= CURRENT_DATE - 500

),

asset_transfers AS (

SELECT

block_timestamp,

TX_HASH,

TX_SIGNER AS from_address,

TX_RECEIVER AS to_address,

DEPOSIT/1e24 as amount

FROM near.core.fact_transfers

WHERE DEPOSIT > 0

),

cex_hot_wallets AS (

SELECT

address AS hot_wallet,

project_name AS cex_name

FROM near.core.dim_address_labels

WHERE blockchain = 'near'

AND label_type = 'cex'

AND label_subtype = 'hot_wallet'

),

cex_deposit_wallets AS (

SELECT

address AS deposit_address,

project_name AS cex_name

FROM near.core.dim_address_labels

WHERE blockchain = 'near'