ethereum_hourly_tx.sql

SELECT 
SQRT(
POWER((h0-0.0417),2)+POWER((h1-0.0417),2)+POWER((h2-0.0417),2)+POWER((h3-0.0417),2)+
POWER((h4-0.0417),2)+POWER((h5-0.0417),2)+POWER((h6-0.0417),2)+POWER((h7-0.0417),2)+
POWER((h8-0.0417),2)+POWER((h9-0.0417),2)+POWER((h10-0.0417),2)+POWER((h11-0.0417),2)+
POWER((h12-0.0417),2)+POWER((h13-0.0417),2)+POWER((h14-0.0417),2)+POWER((h15-0.0417),2)+
POWER((h16-0.0417),2)+POWER((h17-0.0417),2)+POWER((h18-0.0417),2)+POWER((h19-0.0417),2)+
POWER((h20-0.0417),2)+POWER((h21-0.0417),2)+POWER((h22-0.0417),2)+POWER((h23-0.0417),2)
) AS daemon_distance,
address,hsum,
h0,h1,h2,h3,h4,h5,h6,h7,h8,h9,h10,h11,h12,h13,h14,h15,h16,h17,h18,h19,h20,h21,h22,h23

FROM
(
SELECT address,hsum,
h0/hsum As h0,h1/hsum As h1,h2/hsum As h2,h3/hsum As h3,h4/hsum As h4,h5/hsum As h5,
h6/hsum As h6,h7/hsum As h7,h8/hsum As h8,h9/hsum As h9,h10/hsum As h10,h11/hsum As h11,
h12/hsum As h12,h13/hsum As h13,h14/hsum As h14,h15/hsum As h15,
h16/hsum As h16,h17/hsum As h17,h18/hsum As h18,h19/hsum As h19,
h20/hsum As h20,h21/hsum As h21,h22/hsum As h22,h23/hsum As h23
FROM 
(
SELECT h0.address, 
h0.c AS h0,h1.c AS h1,h2.c AS h2,h3.c AS h3,h4.c AS h4,h5.c AS h5,h6.c AS h6,h7.c AS h7,h8.c AS h8,
h9.c AS h9,h10.c AS h10,h11.c AS h11,h12.c AS h12,h13.c AS h13,h14.c AS h14,h15.c AS h15,h16.c AS h16,
h17.c AS h17,h18.c AS h18,h19.c AS h19,h20.c AS h20,h21.c AS h21,h22.c AS h22,h23.c AS h23,
h0.c+h1.c+h2.c+h3.c+h4.c+h5.c+h6.c+h7.c+h8.c+h9.c+h10.c+h11.c+h12.c+
h13.c+h14.c+h15.c+h16.c+h17.c+h18.c+h19.c+h20.c+h21.c+h22.c+h23.c AS hsum
FROM
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 0 GROUP BY from_address) AS h0,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 1 GROUP BY from_address) AS h1,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 2 GROUP BY from_address) AS h2,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 3 GROUP BY from_address) AS h3,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 4 GROUP BY from_address) AS h4,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 5 GROUP BY from_address) AS h5,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 6 GROUP BY from_address) AS h6,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 7 GROUP BY from_address) AS h7,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 8 GROUP BY from_address) AS h8,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 9 GROUP BY from_address) AS h9,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 10 GROUP BY from_address) AS h10,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 11 GROUP BY from_address) AS h11,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 12 GROUP BY from_address) AS h12,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 13 GROUP BY from_address) AS h13,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 14 GROUP BY from_address) AS h14,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 15 GROUP BY from_address) AS h15,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 16 GROUP BY from_address) AS h16,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 17 GROUP BY from_address) AS h17,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 18 GROUP BY from_address) AS h18,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 19 GROUP BY from_address) AS h19,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 20 GROUP BY from_address) AS h20,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 21 GROUP BY from_address) AS h21,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 22 GROUP BY from_address) AS h22,
(SELECT from_address AS address, COUNT(1) AS c FROM `bigquery-public-data.ethereum_blockchain.transactions`
WHERE EXTRACT(HOUR FROM block_timestamp) = 23 GROUP BY from_address) AS h23
WHERE TRUE 
AND h0.address = h1.address AND h0.address = h2.address AND h0.address = h3.address 
AND h0.address = h4.address AND h0.address = h5.address AND h0.address = h6.address 
AND h0.address = h7.address AND h0.address = h8.address AND h0.address = h9.address 
AND h0.address = h10.address AND h0.address = h11.address AND h0.address = h12.address
AND h0.address = h13.address AND h0.address = h14.address AND h0.address = h15.address 
AND h0.address = h16.address AND h0.address = h17.address AND h0.address = h18.address 
AND h0.address = h19.address AND h0.address = h20.address AND h0.address = h21.address 
AND h0.address = h22.address AND h0.address = h23.address
)
--ORDER BY h0 DESC
)
ORDER BY daemon_distance ASC