ethereum-sessions.sql

WITH sessions AS (
SELECT
    block_timestamp,
    block_number,
    transaction_index,
    value,
    from_address,
    CASE WHEN trace_type IS NULL THEN 0 ELSE 1 END AS is_from_contract,
    trace_type,
    SUM(is_new_session) OVER (ORDER BY from_address, block_timestamp) AS global_session_id,
    SUM(is_new_session) OVER (PARTITION BY from_address ORDER BY block_timestamp) AS user_session_id
FROM (
    SELECT *,
        CASE WHEN UNIX_SECONDS(block_timestamp) - UNIX_SECONDS(last_event) >= (60 * 10) 
             OR last_event IS NULL 
             THEN 1 
             ELSE 0 
        END AS is_new_session
    FROM (
        SELECT * FROM
        (
            SELECT 
                *,
                LAG(block_timestamp,1) OVER (PARTITION BY from_address ORDER BY block_timestamp) AS last_event
            FROM (
                SELECT DISTINCT
                    from_address,
                    block_timestamp,
                    block_number,
                    transaction_index,
                    value,
                    trace_type
                FROM `bigquery-public-data.crypto_ethereum.traces`
            ) AS x
            WHERE TRUE
              AND from_address LIKE '0x000%'
              AND from_address IN (SELECT address FROM `bigquery-public-data.crypto_ethereum.contracts`)
            UNION ALL 
            SELECT 
                from_address,
                block_timestamp,
                block_number,
                transaction_index,
                value,
                CAST(NULL AS STRING) AS trace_type, 
                LAG(block_timestamp,1) OVER (PARTITION BY from_address ORDER BY block_timestamp) AS last_event
            FROM `bigquery-public-data.crypto_ethereum.transactions`
            WHERE TRUE
              AND from_address LIKE '0x000%'
              AND from_address NOT IN (SELECT address FROM `bigquery-public-data.crypto_ethereum.contracts`)
        ) AS txunion
    ) last
) final
),
session_segments AS (
  SELECT ss.global_session_id, ss.user_session_id, MIN(ss.block_timestamp) AS start_time, MAX(ss.block_timestamp) AS end_time, COUNT(ss.block_timestamp) AS events
  FROM sessions AS ss
  GROUP BY global_session_id, user_session_id
)

SELECT * FROM (
SELECT 
    DISTINCT
    ss.global_session_id, 
    ss.user_session_id,
    segments.start_time AS session_start_time,
    segments.end_time AS session_end_time,
    UNIX_SECONDS(segments.end_time) - UNIX_SECONDS(segments.start_time) AS session_duration,
    segments.events AS session_event_count,

--     -- xxx
--     -- xx0                                     non-token transfer
--     CASE WHEN token_transfers.transaction_hash IS NULL THEN 0 ELSE 1 END AS is_non_token_transfer,
    -- xx1                                         token transfer
    CASE WHEN token_transfers.transaction_hash IS NOT NULL THEN 0 ELSE 1 END AS is_token_transfer,
--     -- x0x                   to non-contract
--     CASE WHEN to_contracts.address IS NULL THEN 1 ELSE 0 END AS is_to_non_contract,
--     -- x00                   to non-contract, non-token_transfer
--     CASE WHEN to_contracts.address IS NULL AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_to_non_contract_non_token_transfer,
--     -- x01                   to non-contract,     token_transfer
--     CASE WHEN to_contracts.address IS NULL AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_to_non_contract_token_transfer,
    -- x1x                   to     contract
    CASE WHEN to_contracts.address IS NOT NULL THEN 0 ELSE 1 END AS is_to_contract,
--     -- x10                   to     contract, non-token_transfer
--     CASE WHEN to_contracts.address IS NOT NULL AND token_transfers.transaction_hash IS NULL THEN 0 ELSE 1 END AS is_to_contract_non_token_transfer,
--     -- x11                   to     contract,     token_transfer
--     CASE WHEN to_contracts.address IS NOT NULL AND token_transfers.transaction_hash IS NOT NULL THEN 0 ELSE 1 END AS is_to_contract_token_transfer,
--     -- 0xx from non-contract
--     CASE WHEN is_from_contract = 0 THEN 1 ELSE 0 END AS is_from_non_contract,
--     -- 0x0 from non-contract,                  non-token_transfer
--     CASE WHEN is_from_contract = 0 AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_from_non_contract_non_token_transfer,
--     -- 0x1 from non-contract,                      token_transfer
--     CASE WHEN is_from_contract = 0 AND token_transfers.transaction_hash IS NOT NULL THEN 1 ELSE 0 END AS is_from_non_contract_token_transfer,
--     -- 00x from non-contract, to non-contract
--     CASE WHEN is_from_contract = 0 AND to_contracts.address IS NULL THEN 1 ELSE 0 END AS is_from_non_contract_to_non_contract,
--     -- 000 from non-contract, to non-contract, non-token transfer
--     CASE WHEN is_from_contract = 0 AND to_contracts.address IS NULL AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_from_non_contract_to_non_contract_non_token_transfer,
--     -- 001 from non-contract, to non-contract,     token transfer
--     CASE WHEN is_from_contract = 0 AND to_contracts.address IS NULL AND token_transfers.transaction_hash IS NOT NULL THEN 1 ELSE 0 END AS is_from_non_contract_to_non_contract_token_transfer,
--     -- 01x from non-contract, to     contract
--     CASE WHEN is_from_contract = 0 AND to_contracts.address IS NOT NULL THEN 1 ELSE 0 END AS is_from_non_contract_to_contract,
--     -- 010 from non-contract, to     contract, non-token transfer
--     CASE WHEN is_from_contract = 0 AND to_contracts.address IS NOT NULL AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_from_non_contract_to_contract_non_token_transfer,
--     -- 011 from non-contract, to     contract,     token transfer
--     CASE WHEN is_from_contract = 0 AND to_contracts.address IS NOT NULL AND token_transfers.transaction_hash IS NOT NULL THEN 1 ELSE 0 END AS is_from_non_contract_to_contract_token_transfer,
    -- 1xx from     contract
    is_from_contract,
--     -- 1x0 from     contract,                  non-token_transfer   
--     CASE WHEN is_from_contract = 1 AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_from_contract_non_token_transfer,
--     -- 1x1 from     contract,                      token_transfer
--     CASE WHEN is_from_contract = 1 AND token_transfers.transaction_hash IS NOT NULL THEN 1 ELSE 0 END AS is_from_contract_token_transfer,
--     -- 10x from     contract, to non-contract
--     CASE WHEN is_from_contract = 1 AND to_contracts.address IS NULL THEN 1 ELSE 0 END AS is_from_contract_to_non_contract,
--     -- 100 from     contract, to non-contract, non-token transfer
--     CASE WHEN is_from_contract = 1 AND to_contracts.address IS NULL AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_from_contract_to_non_contract_non_token_transfer,
--     -- 101 from     contract, to non-contract,     token transfer
--     CASE WHEN is_from_contract = 1 AND to_contracts.address IS NULL AND token_transfers.transaction_hash IS NOT NULL THEN 1 ELSE 0 END AS is_from_contract_to_non_contract_token_transfer,
--     -- 11x from     contract, to     contract
--     CASE WHEN is_from_contract = 1 AND to_contracts.address IS NOT NULL THEN 1 ELSE 0 END AS is_from_contract_to_contract,
--     -- 110 from     contract, to     contract, non-token transfer
--     CASE WHEN is_from_contract = 1 AND to_contracts.address IS NOT NULL AND token_transfers.transaction_hash IS NULL THEN 1 ELSE 0 END AS is_from_contract_to_contract_non_token_transfer,
--     -- 111 from     contract, to     contract,     token transfer
--     CASE WHEN is_from_contract = 1 AND to_contracts.address IS NOT NULL AND token_transfers.transaction_hash IS NOT NULL THEN 1 ELSE 0 END AS is_from_contract_to_contract_token_transfer,

    tx.block_timestamp,
    ss.block_number, 
    ss.transaction_index, 
    tx.from_address, 
    tx.to_address, 
    tx.receipt_gas_used, 
    ss.value/POWER(10,18) AS value, 
    CASE WHEN tx.to_address IS NULL THEN 'create' ELSE ss.trace_type END AS trace_type,
    LENGTH(tx.input)/1024 AS input_kb
FROM 
    sessions AS ss,
    session_segments AS segments,
    `bigquery-public-data.crypto_ethereum.transactions` AS tx
    LEFT JOIN `bigquery-public-data.crypto_ethereum.contracts` AS to_contracts ON tx.to_address = to_contracts.address
    LEFT JOIN `bigquery-public-data.crypto_ethereum.token_transfers` AS token_transfers ON tx.hash = token_transfers.transaction_hash
WHERE TRUE
    AND ss.block_number = tx.block_number
    AND ss.transaction_index = tx.transaction_index
    AND ss.global_session_id = segments.global_session_id
    AND ss.user_session_id = segments.user_session_id
)