medvedev1088

#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, [email protected]
with double_entry_book as (
    -- debits
    select to_address as address, value as value, block_timestamp, block_number
    from `bigquery-public-data.ethereum_blockchain.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)

medvedev1088

#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, [email protected]
with double_entry_book as (
    -- debits
    select to_address as address, value as value, block_timestamp
    from `bigquery-public-data.crypto_ethereum.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)

medvedev1088

with ether_emitted_by_date  as (
  select date(block_timestamp) as date, sum(value) as value
  from `bigquery-public-data.crypto_ethereum.traces`
  where trace_type in ('genesis', 'reward')
  group by date(block_timestamp)
)
select date, sum(value) OVER (ORDER BY date) / power(10, 18) AS supply
from ether_emitted_by_date

medvedev1088

medvedev1088

medvedev1088

medvedev1088

WITH traces_with_status AS (
    -- Find all nested traces of failed traces
    WITH nested_failed_traces AS (
        SELECT distinct child.transaction_hash, child.trace_address
        FROM `bigquery-public-data.ethereum_blockchain.traces` parent
        JOIN `bigquery-public-data.ethereum_blockchain.traces` child
        ON (parent.trace_address IS NULL OR starts_with(child.trace_address, concat(parent.trace_address, ',')))
        AND child.transaction_hash = parent.transaction_hash
        where parent.trace_type IN ('call', 'create')
        AND parent.error IS NOT NULL

medvedev1088

#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, [email protected]
with double_entry_book as (
    -- debits
    select to_address as address, value as value
    from `bigquery-public-data.crypto_ethereum.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)

medvedev1088

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

## @params: [JOB_NAME]
args = getResolvedOptions(sys.argv, ['JOB_NAME'])

medvedev1088

# The below partitioning tries to make each partition of equal size.
# The first million blocks are in a single partition.
# The next 3 million blocks are in 100k partitions.
# The next 1 million blocks are in 10k partitions.
# Note that there is a limit in Data Pipeline on the number of objects, which can be
# increased in the Support Center
# https://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-limits.html
EXPORT_PARTITIONS = [(0, 999999)] + \
                    [(start, end) for start, end in split_to_batches(1000000, 1999999, 100000)] + \
                    [(start, end) for start, end in split_to_batches(2000000, 2999999, 100000)] + \