blockhackathon19/DEBUG Python 3 notebook at master · cnukaus/blockhackathon19 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
orig_debug=raw_input("Debug (1/0). Anything else defaults to 0.\nDEBUG> ")
if orig_debug=="1":
    DEBUG=True
else:
    DEBUG=False

from bq_helper import BigQueryHelper
bq_assistant = BigQueryHelper("bigquery-public-data","crypto_ethereum") #NEW NAME, old name in examples doesn't work
# not actually useful! also data not in Kaggle
                              # bigquery-public-data","crypto_ethereum_classic") #bigquery-public-data.crypto_ethereum_classic.logs
# "bigquery-public-data", "github_repos")
#'', project='bigquery-public-data'

# What else people paid after they paid to the ENS contract.

FROM_BLOCK_TIMESTAMP='2019-01-01 05:41:19'
TO_BLOCK_TIMESTAMP='2019-03-01 05:41:19'

if DEBUG:
    print("Query 1 Start")
QUERY="""
SELECT trans_down.*
	,RANK() OVER (
		PARTITION BY trans.from_address
		,trans.block_timestamp ORDER BY trans_down.block_timestamp
		) AS rank
	,datetime_diff(cast(trans_down.block_timestamp AS DATETIME), cast(trans.block_timestamp AS DATETIME), hour) AS HoursGap
FROM `bigquery - PUBLIC - data.crypto_ethereum.transactions` AS trans
INNER JOIN `bigquery - PUBLIC - data.crypto_ethereum.transactions` AS trans_down ON trans.from_address = trans_down.from_address
	AND trans_down.to_address NOT IN ({contract_list})
	AND (trans_down.block_timestamp > trans.block_timestamp)
WHERE (
		trans.block_timestamp >= {from_timestamp}

		)
AND trans.to_address IN ({contract_list})
""".format(from_timestamp=FROM_BLOCK_TIMESTAMP, to_timestamp=TO_BLOCK_TIMESTAMP,
           contract_list="'0x314159265dd8dbb310642f98f50c066173c1259b'")#",'0xA62142888ABa8370742bE823c1782D17A0389Da1','0xa62142888aba8370742be823c1782d17a0389da1','0xf056F435Ba0CC4fCD2F1B17e3766549fFc404B94'")
if DEBUG:
    print("Query 1 Estimated size:")
bq_assistant.estimate_query_size(QUERY) #estimate_gigabytes_scanned("SELECT Id FROM `bigquery-public-data.hacker_news.stories`", client)

transactions = bq_assistant.query_to_pandas_safe(QUERY)
if DEBUG:
    print("Query 1 - print(transactions):")
print(transactions)


if DEBUG:
    print("Query 1 End, Query 2 Start")
# What they did before paying to the ENS contract
QUERY2="""
SELECT trans_up.*
	,trans_down.to_address AS Destination_address
FROM `bigquery - PUBLIC - data.crypto_ethereum.transactions` AS trans
INNER JOIN `bigquery - PUBLIC - data.crypto_ethereum.transactions` AS trans_down ON trans.from_address = trans_down.from_address
	AND (
		trans_down.block_timestamp >= '2019-01-01 05:41:19'
		AND trans_down.block_timestamp <= '2019-04-08 05:41:19'
		)
INNER JOIN `bigquery - PUBLIC - data.crypto_ethereum.transactions` AS trans_up ON trans.from_address = trans_up.to_address
	AND (
		trans_up.block_timestamp >= '2018-12-01 05:41:19'
		AND trans_up.block_timestamp <= '2019-03-08 05:41:19'
		)
WHERE (
		trans.block_timestamp >= {from_timestamp}
  AND trans.block_timestamp <= {to_timestamp}

		)
AND trans.to_address IN ({contract_list})

  AND from_address IN ({contract_list})
""".format(from_timestamp=FROM_BLOCK_TIMESTAMP, to_timestamp=TO_BLOCK_TIMESTAMP, contract_list="'0x314159265dd8dbb310642f98f50c066173c1259b'")
# ",'0xA62142888ABa8370742bE823c1782D17A0389Da1','0xa62142888aba8370742be823c1782d17a0389da1','0xf056F435Ba0CC4fCD2F1B17e3766549fFc404B94'")
if DEBUG:
    print("Query 2 Estimated size:")
bq_assistant.estimate_query_size(QUERY2) #estimate_gigabytes_scanned("SELECT Id FROM `bigquery-public-data.hacker_news.stories`", client)

transactions2 = bq_assistant.query_to_pandas_safe(QUERY2)
if DEBUG:
    print("Query 2 - print(transactions):")
transactions2