User Guide: Understanding Flipside Crypto Data Models

    Welcome to the world of Flipside Crypto data models! These models provide crucial information across many supported networks, helping you analyze and understand the flow of cryptocurrencies. Flipside Crypto provides several schemas, each holding specific types of data. Let’s dive into each one.

    Core Schema
    Writing Basic Queries

    Purpose:

    The core schema contains fundamental blockchain data, including transactions, blocks, and addresses.

    Details:

    The Core Schema provides fundamental insights into blockchain networks and their core functionalities. It includes metrics such as block height, transaction count, and network parameters, allowing users to analyze the foundational aspects of blockchain technology. If you can see your transaction on etherscan, then you can find your transaction here too.

    what we can do with Core schemas:

    • Retrieve the latest block height of a specific blockchain.
    • Count the total number of transactions in the past 24 hours.
    • Calculate the average block time over the past week.
    • List the top 10 most active addresses on the blockchain.

    Context: ""You're interested in analyzing the transaction activity of a specific token address over the past month. Use our core.ez_token_transfers table to filter transactions related to the Token, and calculate the total transaction volume or number of unique users that have interaction with this token."

    Query: Lets Take a look at Transfer activity of a Token on the Ethereum chain in the last 30 days. the goal here is to count number of users who Transferred this token over last 30 days or 1 month.

    SELECT
    date_trunc('day', block_timestamp) as day, 
    count(DISTINCT origin_from_address) as users 
    FROM ethereum.core.ez_token_transfers
    WHERE contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' --- USDC contract
    AND block_timestamp >= current_date - interval '1 month' 
    GROUP BY day 
    

    Query Syntax:

    • date_trunc = Truncates a DATE, TIME, or TIMESTAMP to the specified precision.
    • count = Returns either the number of non-NULL records for the specified columns or the total number of records.
    • distinct = to avoid repetitive results.
    • current_date = Returns the current date of the system You can use interval constants to add or subtract a period of time to/from a date, time, or timestamp
    • group by day = Groups rows with the same group-by-item expressions and computes aggregate functions for the resulting group we used column name as a group by expression
    Loading...
    Loading...