SELECT 
  block_timestamp::date AS date,
  c.address_name,
  'ETH' AS token,
  sum(amount) AS amount
FROM ethereum.core.ez_eth_transfers a 
INNER JOIN (
  SELECT DISTINCT address, address_name FROM crosschain.core.address_labels 
  WHERE label_subtype = 'hot_wallet' AND project_name LIKE 'coinbase'
  AND address_name like 'coinbase %' AND blockchain = 'ethereum')

SELECT 
  platform, 
  count(DISTINCT(contract_address)) AS n_pools, 
  count(DISTINCT(tx_hash)) AS n_swaps, 
  sum(amount_in_usd) AS usd_swap_value, 
  count(DISTINCT(origin_from_address)) AS n_users 
FROM ethereum.core.ez_dex_swaps 
WHERE block_timestamp > current_date - 7 
GROUP BY platform 
HAVING n_swaps > 15 
ORDER BY n_users desc;

SELECT 
  project_name,
  sum(nfts.price_usd) AS sales_volume, 
  count(DISTINCT (nfts.tx_hash)) AS n_sales, 
  count(DISTINCT (nfts.platform_name)) AS n_platforms 
FROM ethereum.core.ez_nft_sales nfts 
WHERE nfts.block_timestamp > current_date - 7 
  AND nfts.project_name IS NOT NULL 
GROUP BY nfts.project_name
ORDER BY sales_volume DESC;

SELECT
  blockchain,
  block_timestamp::date AS date,
  count(DISTINCT(trader)) AS n_users
FROM crosschain.core.ez_dex_swaps
WHERE block_timestamp > current_date - 7
GROUP BY blockchain, date
ORDER BY date DESC;