Skip to content

Instantly share code, notes, and snippets.

View medvedev1088's full-sized avatar

Evgeny Medvedev medvedev1088

278 followers · 1 following
View GitHub Profile
@medvedev1088
medvedev1088 / ethereum_hodl_waves.sql
Last active April 29, 2020 11:52
with source_table as (
select * from `your-projet.you-dataset.ages`
),
supply_table as (
select date, sum(balance) as supply
from source_table
group by date
),
wave1d_table as (
select date, sum(balance) as sum1d
@medvedev1088
medvedev1088 / maker_governance_parsing.sql
Created June 12, 2020 12:43
CREATE TEMP FUNCTION
PARSE_LOGNOTE(data STRING, topics ARRAY<STRING>)
RETURNS STRUCT<`sig` STRING, `guy` STRING, `foo` STRING, `bar` STRING, `wad` STRING, `fax` STRING>
LANGUAGE js AS """
var parsedEvent = {"anonymous": true, "inputs": [{"indexed": true, "name": "sig", "type": "bytes4"}, {"indexed": true, "name": "guy", "type": "address"}, {"indexed": true, "name": "foo", "type": "bytes32"}, {"indexed": true, "name": "bar", "type": "bytes32"}, {"indexed": false, "name": "wad", "type": "uint256"}, {"indexed": false, "name": "fax", "type": "bytes"}], "name": "lognote", "type": "event"}
return abi.decodeEvent(parsedEvent, data, topics, false);
"""
OPTIONS
( library="https://storage.googleapis.com/ethlab-183014.appspot.com/ethjs-abi.js" );
@medvedev1088
medvedev1088 / maker-governance-graphql-to-csv.py
Last active June 18, 2020 15:37
import csv
import json
import requests
url = 'https://api.thegraph.com/subgraphs/name/protofire/makerdao-governance'
file = open("output.csv", 'w')
writer = csv.DictWriter(file, fieldnames=['spell_id', 'id', 'timestamp', 'transactionHash', 'sender', 'type', 'locked',
'wad'], extrasaction='ignore')
@medvedev1088
medvedev1088 / maker-governance.csv
Last active June 18, 2020 15:37
We can't make this file beautiful and searchable because it's too large.
spell_id,id,timestamp,transactionHash,sender,type,locked,wad
0x043c52c8ff76c088646c8d2630eddf1a8e33ba4c,ADD-0xa3844136e8b2e79d9121c982ad1f0211e57d1eecbe7c5dd1df120857d3ffd79e-171,1571014430,0xa3844136e8b2e79d9121c982ad1f0211e57d1eecbe7c5dd1df120857d3ffd79e,0x6f2a8ee9452ba7d336b3fba03cac27f7818aead6,AddAction,1850,
0x043c52c8ff76c088646c8d2630eddf1a8e33ba4c,ADD-ARRAY-0x00bccd137decd2d44d4d3fe8a55d4be2c0abd1e91575dbb065219ecd658625a7-60,1571162495,0x00bccd137decd2d44d4d3fe8a55d4be2c0abd1e91575dbb065219ecd658625a7,0x4f2161c7eb1dc40d6f0eb24db81bf4a6eb0c3f30,AddAction,3,
0x043c52c8ff76c088646c8d2630eddf1a8e33ba4c,ADD-ARRAY-0x04882faa79988f8b002d140e3b1a2d53462231264dd6fc313b8f29798440ba2c-112,1571155382,0x04882faa79988f8b002d140e3b1a2d53462231264dd6fc313b8f29798440ba2c,0x2b528c8a31dbd7c57d8b42003262300c011d044b,AddAction,10,
0x043c52c8ff76c088646c8d2630eddf1a8e33ba4c,ADD-ARRAY-0x0783b1d2ebb15df5d762964616e5d377014f3480c9a7d9bea74fd3d1e78eca16-46,1570824997,0x0783b1d2ebb15df5d762964616e5d377014f3480c9a7d9bea74fd3d1
@medvedev1088
medvedev1088 / ocean-protocol-squid.py
Created August 14, 2020 07:35
import os
import time
import uuid
from ocean_keeper.utils import get_account
from ocean_keeper.contract_handler import ContractHandler
from squid_py import Ocean, ConfigProvider, Config
from ocean_utils.agreements.service_types import ServiceTypes
from ocean_utils.agreements.service_agreement import ServiceAgreement
@medvedev1088
medvedev1088 / ethereum_burns.sql
Created August 18, 2020 14:01
-- Pads indexes in trace_address to 5 so trace addresses can be chronologically ordered
CREATE TEMPORARY FUNCTION normalize_trace_address(trace_address STRING)
RETURNS STRING AS ((
SELECT ARRAY_TO_STRING(ARRAY_AGG(lpadded), ',') as arr
FROM
(
SELECT LPAD(addr, 5, '0') as lpadded
FROM UNNEST((
SELECT SPLIT(trace_address, ','))
) AS addr
@medvedev1088
medvedev1088 / parse_logs.sql
Created August 21, 2020 12:21
CREATE TEMP FUNCTION PARSE_LOG(data STRING, topics ARRAY<STRING>) RETURNS STRUCT<`from` STRING, `to` STRING, `value` STRING, `data` STRING> LANGUAGE js
OPTIONS (library=["gs://blockchain-etl-bigquery/ethers.js"]) AS """
var abi = {"anonymous": false, "inputs": [{"indexed": true, "name": "from", "type": "address"}, {"indexed": true, "name": "to", "type": "address"}, {"indexed": false, "name": "value", "type": "uint256"}, {"indexed": true, "name": "data", "type": "bytes"}], "name": "Transfer", "type": "event"}
var interface_instance = new ethers.utils.Interface([abi]);
var parsedLog = interface_instance.parseLog({topics: topics, data: data});
var parsedValues = parsedLog.values;
@medvedev1088
medvedev1088 / parse_logs_2.sql
Created August 21, 2020 15:35
CREATE TEMP FUNCTION PARSE_LOG(data STRING, topics ARRAY<STRING>) RETURNS STRUCT<`from` STRING, `to` STRING, `value` STRING> LANGUAGE js
OPTIONS (library=["gs://blockchain-etl-bigquery/ethers.js"]) AS """
var abi = {"anonymous": false, "inputs": [{"indexed": true, "name": "from", "type": "address"}, {"indexed": true, "name": "to", "type": "address"}, {"indexed": false, "name": "value", "type": "uint256"}], "name": "Transfer", "type": "event"}
var interface_instance = new ethers.utils.Interface([abi]);
var parsedLog = interface_instance.parseLog({topics: topics, data: data});
var parsedValues = parsedLog.values;
var transformParams = function(params, abiInputs) {
var result = {};
if (params && params.length >= abiInputs.length) {
for (var i = 0; i < abiInputs.length; i++) {
@medvedev1088
medvedev1088 / dexes_contracts.sql
Last active October 26, 2020 05:54
select *
from `bigquery-public-data.crypto_ethereum.logs`
where (address in ('0x39755357759ce0d7f32dc8dc45414cca409ae24e', '0x794e6e91555438afc3ccf1c5076a74f42133d08d', '0x8fd3121013a07c57f0d69646e86e7a4880b467b7', '0x4572f2554421bd64bef1c22c8a81840e8d496bea', '0x4aea7cf559f67cedcad07e12ae6bc00f07e8cf65', '0x8d12a197cb00d4747a1fe03395095ce2a5cc6819', '0x818e6fecd516ecc3849daf6845e3ec868087b755', '0x12459c951127e0c374ff9105dda097662a027093', '0x4f833a24e1f95d70f028921e27040ca56e09ab0b', '0x080bf510fcbf18b91105470639e9561022937712', '0x61935cbdd02287b511119ddb11aeb42f1593b7ef', '0x79a8c46dea5ada233abaffd40f3a0a2b1e5a4f27', '0x79a8c46dea5ada233abaffd40f3a0a2b1e5a4f27', '0xa2b47e3d5c44877cca798226b7b8118f9bfb7a56', '0xa2b47e3d5c44877cca798226b7b8118f9bfb7a56', '0x06364f10b501e868329afbc005b3492902d6c763', '0x06364f10b501e868329afbc005b3492902d6c763', '0x93054188d876f558f4a66b2ef1d97d16edf0895b', '0x7fc77b5c7614e1533320ea6ddc2eb61fa00a9714', '0xa5407eae9ba41422680e2e00537571bcc53efbfd', '0xa5407eae9ba41422680e2e00
@medvedev1088
medvedev1088 / block_ 11536174_tx_0x00a474690ea6365d7dc8fdc9b1f6ce36b96294b2c9f7abd5c4c583d16f02e4cf.json
Created January 4, 2021 13:19
This file has been truncated, but you can view the full file.
{
"output": "0x00000000000000000000000000000000000000000000000000000000000000200000000000000000000000000000000000000000000000000000000000000002000000000000000000000000000000000000000000000000eea53b8816488d370000000000000000000000000000000000000000000000af09962b3550500000",
"stateDiff": {
"0x1f9840a85d5af5bf1d1762f925bdaddc4201f984": {
"balance": "=",
"code": "=",
"nonce": "=",
"storage": {
"0x6efc1de1d1f97ee83932fac447be36dc1d497877c59023ee23571a0d5ba443fa": {
"*": {