SELECT 
    toDate(block_timestamp) AS day, 
    count() AS number_transactions, 
    joinGet('test__ETH_USD_join_by_day', 'price', day) AS eth_usd, 
    round(avg(gas_used * (gas_price / exp10(18))) * eth_usd, 2) AS `avg_tx_fee_$`, 
    round(max(gas_used * (gas_price / exp10(18))) * eth_usd, 2) AS `max_tx_fee_$`, 
    round(min(gas_used * (gas_price / exp10(18))) * eth_usd, 2) AS `min_tx_fee_$`
FROM test__transactions 
GROUP BY 
    day, 
    eth_usd
ORDER BY day ASC

SELECT 
    hash, 
    toDate(block_timestamp) AS day, 
    joinGet('test__ETH_USD_join_by_day', 'price', day) AS eth_usd, 
    round((gas_used * (gas_price / exp10(18))) * eth_usd, 2) AS `fee_in_$`
FROM test__transactions 
ORDER BY `fee_in_$` DESC
LIMIT 10

SELECT 
    joinGet(test__blocks_join_by_number, 'miner', block_number) AS miner, 
    countIf((miner = from_address) OR (miner = to_address)) AS txs_to_miners_inside_pool, 
    countIf((miner != from_address) AND (to_address != miner)) AS external_accounts, 
    txs_to_miners_inside_pool + external_accounts AS total
FROM test__transactions 
WHERE gas_price = 0
GROUP BY miner
ORDER BY external_accounts DESC

SELECT 
    from_address, 
    joinGet(test__blocks_join_by_number, 'miner', block_number) AS miner, 
    count() AS tx
FROM test__transactions 
WHERE (from_address != miner) AND (to_address != miner) AND (gas_price <= 1000000000)
GROUP BY 
    from_address, 
    miner
ORDER BY tx DESC

WITH(
    SELECT 
      arrayMap(x -> cityHash64(x), arrayConcat(topK(20)(from_address), topK(20)(to_address))),
      count()
    FROM test__transactions
  ) AS tr


  SELECT
    address,
    count() as number_transactions,
    round(number_transactions/tupleElement(tr,2)*100, 2) as percentatge
  FROM (
    SELECT from_address as address FROM test__transactions 
    UNION ALL
    SELECT to_address   as address FROM test__transactions
  )
  WHERE has(tupleElement(tr,1), cityHash64(address))
  GROUP BY address
  ORDER BY number_transactions DESC
  LIMIT 30

WITH(
      SELECT COUNT(*) FROM test__transactions
    ) as total

    SELECT 
        address,
        COUNT(*) as number_transactions,
        round(number_transactions/total*100,2) as percentatge
    FROM (
       SELECT from_address as address FROM test__transactions
       UNION ALL
       SELECT to_address as address FROM test__transactions
    )
    GROUP BY address
    ORDER BY number_transactions DESC
    LIMIT 30

SELECT cityHash64('0xdac17f958d2ee523a2206206994597c13d831ec7')