📰 2023-06-23: Weekly Prophet!

[andrewhong5297]

This is your weekly summary of 24 PRs merged from 12 wizards. Great job everyone! 🎉
We had 21 added models 🟢 and 57 modified models 🟠 for 15 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
dex	1	0	1	1
nft	2	0	8	1
tokens	4	0	6	2
timeswap	2	0	2	1
cow_protocol	1	0	1	1
hashflow	1	4	0	1
lido	1	1	1	1
balancer	1	4	6	1
account_abstraction	1	1	0	1
gas	5	3	8	1
xchange	2	5	0	1
tigris	1	0	12	1
evms	1	0	1	1
opensea	3	3	8	1
seaport	1	0	3	1

SECTOR: dex

toggle to see all model updates

MODEL: dex_trades.sql

🟠 Modified by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: One reference model was added: 'xchange_trades'. No reference models were removed.

SECTOR: nft

toggle to see all model updates

MODEL: nft_ethereum_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Wash trading blend fix
🧙 Author: @hildobby on 2023-06-23
📝 Summary: The diff shows that a filter has been added to exclude transactions where the buyer or seller is a specific address. The filter applies to both buying and selling transactions, and is included in an existing WITH clause for each transaction type. Additionally, there are conditional statements that limit the time range of data being queried if it's an incremental load.

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The diff shows changes made to a SQL model that filters data from two tables, nft_trades and dex_flashloans. The first change replaces a conditional statement with a constant value in the same_buyer_seller column. The second change does the same for the back_and_forth_trade column. A new filter is added to identify flashloan trades by joining on matching blockchain, block time and transaction hash values between both tables. Finally, some conditions are added to limit results based on incremental updates or non-null unique trade IDs in Ethereum blockchain transactions only.

MODEL: nft_arbitrum_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The SQL model has been modified to simplify the logic of three filters. The first filter now only selects trades where the buyer and seller are the same, while the second filter identifies back-and-forth trades by joining on a self-reference table. Finally, in filter 5, flashloan detection is simplified by removing an unnecessary left join and checking for matching blockchain type instead of null values. All three filters have had their output columns changed from boolean to true/false literals for simplicity purposes.

MODEL: nft_avalanche_c_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The diff shows changes made to a SQL model that filters data from two tables, nft_trades and dex_flashloans. The logic added includes filtering for trades where the buyer is also the seller (same_buyer_seller) and back-and-forth trades (back_and_forth_trade). Additionally, it identifies flash loans by joining on matching blockchain type, block time and transaction hash.

MODEL: nft_bnb_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The diff shows changes made to a SQL model that filters data from two tables, nft_trades and dex_flashloans. The first change replaces a conditional statement with a constant value in the same_buyer_seller column. The second change does the same for the back_and_forth_trade column. A new filter is added to identify flashloan trades by joining on matching blockchain, block time and transaction hash values between both tables. Finally, some redundant code is removed from this filter by removing an unnecessary left join condition.

MODEL: nft_gnosis_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The diff shows changes made to a SQL model that filters data from two tables, nft_trades and dex_flashloans. The first change replaces a conditional statement with a constant value in the same_buyer_seller column. The second change does the same for the back_and_forth_trade column. A new filter is added to create another column called  flashloan, which checks if there was any flash loan activity during trades by joining both tables on specific conditions. Finally, some redundant code is removed from this filter block.

MODEL: nft_optimism_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The diff shows changes made to a SQL model that filters data from two tables, nft_trades and dex_flashloans. The logic added includes filtering for trades where the buyer is also the seller (same_buyer_seller) and trades where there are back-and-forth transactions between buyers and sellers (back_and_forth_trade). Additionally, it identifies flash loans by joining on matching blockchain type, block time, and transaction hash.

MODEL: nft_polygon_wash_trades.sql

🟠 Modified by:

🔧 PR: #3588, Fix wash filter's filter 5 (flash loans) & optimise 1&2's runtime
🧙 Author: @hildobby on 2023-06-22
📝 Summary: The diff shows changes made to a SQL model that filters NFT trades on the Polygon blockchain. The first filter identifies trades where the buyer and seller are the same, while the second filter identifies back-and-forth trades between two parties. A new filter has been added to identify flashloans by joining with another table based on matching block time and transaction hash values. All three filters have been updated to set their respective boolean flags as true instead of using conditional statements for simplicity. Finally, incremental filtering has also been added based on trade timestamps within a week of current time if applicable.

SECTOR: tokens

toggle to see all model updates

MODEL: tokens_arbitrum_erc20.sql

🟠 Modified by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: The token symbol 'GETH' was added to the model.

MODEL: tokens_bnb_bep20.sql

🟠 Modified by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: A new row was added to the SQL model that creates a temporary table with four columns: contract_address, symbol, decimals and a hardcoded list of values. The first three columns are selected from another table and the fourth column is created by specifying tuples containing hardcoded values for each row. The purpose of this change is unclear without additional context about how this temporary table will be used in subsequent queries or data transformations.

MODEL: tokens_polygon_erc20.sql

🟠 Modified by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: Based on the given SQL model, it is not possible to determine which token symbols were added or removed. The model only selects data from a table and creates a temporary table with additional values. There are no indications of any changes made to the existing tokens or their symbols. Therefore, there is no comma-separated list that can be provided for this query.

MODEL: tokens_ethereum_erc20.sql

🟠 Modified by:

🔧 PR: #3554, Add xchange dex trades objects for ethereum
🧙 Author: @mike-x7f on 2023-06-19
📝 Summary: The following token symbols were added: SnakeEyes, LLD, XPEPE, REALM and XINU. No tokens were removed.

🔧 PR: #3554, [ERC20] Add 150 Missing Tokens
🧙 Author: @bh2smith on 2023-06-19
📝 Summary: [changes too large] The model tokens_ethereum_erc20.sql was modified.

🔧 PR: #3554, [Erc20] Add 177 Missing Tokens
🧙 Author: @bh2smith on 2023-06-18
📝 Summary: [changes too large] The model tokens_ethereum_erc20.sql was modified.

SECTOR: timeswap

toggle to see all model updates

MODEL: timeswap_arbitrum_pools.sql

🟠 Modified by:

🔧 PR: #3581, added apt pool to timeswap
🧙 Author: @vhawk19 on 2023-06-23
📝 Summary: The diff of the SQL model shows that a new row was added to an existing table. The row contains information about a trading pair between APT and USDC tokens on the Arbitrum network, including contract addresses, decimals, unique identifier strings for the pair and network details.

🔧 PR: #3581, feat: add new arbitrum pools
🧙 Author: @vhawk19 on 2023-06-21
📝 Summary: The diff shows the addition of two rows to a table with columns such as token symbol, contract addresses, decimals and other details. The added rows contain information about ARB-USDC pair on Arbitrum chain including its symbol, contract addresses for lending and borrowing tokens along with their decimals. The removed lines only include closing brackets at the end of the table definition.

SECTOR: cow_protocol

toggle to see all model updates

MODEL: cow_protocol_ethereum_solvers.sql

🟠 Modified by:

🔧 PR: #3587, [CoW Protocol] Add Enso solver
🧙 Author: @harisang on 2023-06-22
📝 Summary: The SQL model defines a table called known_solver_metadata with columns for address, environment, and name. The existing rows in the table are solvers named Raven, Barter and PropellerHeads running on production environment or barn. Two new solvers named Enso have been added to the table - one running on production environment and another running on barn.

SECTOR: hashflow

toggle to see all model updates

MODEL: hashflow_avalanche_c_crosschain_trades.sql

🟢 Added by:

🔧 PR: #3397, Hashflow cross chain trades
🧙 Author: @BroderickBonelli on 2023-06-21
📝 Summary: This SQL model creates a view called cross_chain_trades that combines data from two tables in the Hashflow Avalanche C database to track cross-chain trades. It then joins this view with other tables to provide additional information such as token symbols, amounts, and prices in USD. The resulting table enables data analysts to analyze cross-chain trading activity on Avalanche C and its connected chains (Ethereum, Arbitrum, Optimism, Polygon and Binance Smart Chain).

MODEL: hashflow_bnb_crosschain_trades.sql

🟢 Added by:

🔧 PR: #3397, Hashflow cross chain trades
🧙 Author: @BroderickBonelli on 2023-06-21
📝 Summary: This SQL model creates a view called cross_chain_trades that combines data from two tables in the hashflow_bnb database and maps destination chain IDs to their respective names. It then joins this view with other tables to retrieve information about token symbols, prices, and amounts for cross-chain trades involving Binance Smart Chain (BSC). The resulting table provides data analysts with insights into the volume of cross-chain trading activity on BSC and its associated costs in USD.

MODEL: hashflow_ethereum_crosschain_trades.sql

🟢 Added by:

🔧 PR: #3397, Hashflow cross chain trades
🧙 Author: @BroderickBonelli on 2023-06-21
📝 Summary: This SQL model creates a view called 'cross_chain_trades' that combines data from two tables in the Hashflow Ethereum database to track cross-chain trades. It then joins this view with other tables to provide information on token symbols, amounts, prices and chains involved in each trade. This enables data analysts to analyze cross-chain trading activity across different blockchains and tokens traded, as well as calculate the USD value of these trades over time.

MODEL: hashflow_crosschain_trades.sql

🟢 Added by:

🔧 PR: #3397, Hashflow cross chain trades
🧙 Author: @BroderickBonelli on 2023-06-21
📝 Summary: This SQL model creates a union of three cross-chain trade tables for Hashflow on Avalanche, Ethereum, and Binance Smart Chain. The resulting table includes information such as the date and time of the block in which each trade occurred, symbols for tokens bought and sold, amounts of tokens bought/sold (both raw values and USD equivalents), addresses associated with each token involved in the transaction, trader information including their wallet address or ID number if available. This model enables data analysts to easily query all cross-chain trades across multiple chains at once.

SECTOR: lido

toggle to see all model updates

MODEL: lido_liquidity.sql

🟠 Modified by:

🔧 PR: #3578, lido liquidity velodrome
🧙 Author: @ppclunghe on 2023-06-21
📝 Summary: In this diff of a dbt SQL model, the reference to 'lido_liquidity_optimism_curve_pools' was removed and then added back. Additionally, a new reference to 'lido_liquidity_optimism_velodrome_pools' was added. The overall logic seems to be related to liquidity pools on different platforms (Arbitrum, Optimism) for Lido Finance.

MODEL: lido_liquidity_optimism_velodrome_pools.sql

🟢 Added by:

🔧 PR: #3578, lido liquidity velodrome
🧙 Author: @ppclunghe on 2023-06-21
📝 Summary: [changes too large] The model lido_liquidity_optimism_velodrome_pools.sql was added.

SECTOR: balancer

toggle to see all model updates

MODEL: balancer_arbitrum_trades.sql

🟠 Modified by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model is for a Balancer V2 Arbitrum trade table. The previous version of the code had two CTEs, one to extract data from the Vault_evt_Swap and another to join it with Vault_evt_PoolRegistered. In this new version, there are no CTEs; instead, a list of models is defined at the beginning using ref(). Then all these models are unioned together in one SELECT statement. Finally, some columns have been added or removed from this final SELECT statement compared to before.

MODEL: balancer_v2_arbitrum_trades.sql

🟢 Added by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze swap transactions on the Balancer V2 Arbitrum pools. The view includes information such as blockchain, project, version, block date and time, token pair traded in the transaction along with their symbols and addresses. It also provides details about amounts of tokens bought/sold in raw form as well as converted into USD value using different sources like prices from external APIs or median prices from BPT (Balancer Pool Token) contracts. Additionally it shows taker/maker addresses involved in each transaction along with other relevant fields for analysis purposes.

MODEL: balancer_v1_ethereum_trades.sql

🟠 Modified by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model is a diff of a merged pull request. The changes include converting all lowercase letters to uppercase, changing the name of one table from prices to prices_usd, and adding some new columns such as amount_usd. There are also some minor formatting changes like adding or removing spaces between lines. Overall, these changes seem to be aimed at improving readability and consistency in the code.

MODEL: balancer_v2_ethereum_trades.sql

🟠 Modified by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: [changes too large] The model balancer_v2_ethereum_trades.sql was modified.

MODEL: balancer_gnosis_trades.sql

🟠 Modified by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model is for a Balancer DEX. The diff shows that the previous version of the model was using Balancer V2 Gnosis trades to get token swap data, and then joining it with price data from another source. In the new version, multiple balancer models are being used instead of just one (Balancer V2 Gnosis trades). These models are unioned together to create a single table containing all relevant trade information. The rest of the logic remains mostly unchanged - this includes getting prices and joining them with trade data based on timestamps and contract addresses etc.

MODEL: balancer_v2_gnosis_trades.sql

🟢 Added by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze Balancer V2 Gnosis pool swap transactions. The view includes information such as the blockchain, project, version, block date and time, token pair traded in the transaction (including their symbols), amount of tokens bought/sold (in both raw and converted values), USD value of the trade at that point in time based on various sources including BPT prices for each token involved in the trade. It also includes details about fees paid by traders for each transaction along with other relevant metadata like taker/maker addresses etc.

MODEL: balancer_optimism_trades.sql

🟠 Modified by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This diff of a dbt SQL model adds support for multiple Balancer DEX models and removes the filtering by project start date. The new code defines a list of Balancer DEX models to be queried, then selects data from each model using UNION ALL. It also changes the output schema to include additional columns such as token_pair and amount_usd, while removing some redundant columns like maker. Finally, it joins with an external table containing price information instead of calculating prices within this query itself.

MODEL: balancer_v2_optimism_trades.sql

🟢 Added by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze Balancer V2 Optimism pools swap transactions. The view includes information such as the blockchain, project, version, block date and time of the transaction, token symbols for bought and sold tokens in each swap transaction along with their amounts in raw form as well as converted into decimals. It also provides details about taker/maker addresses involved in each trade along with pool ID's where trades were executed. Additionally it calculates fees associated with swaps based on different scenarios including edge cases while taking into account prices of tokens at different times during a day or week depending on whether incremental updates are being made or not.

MODEL: balancer_polygon_trades.sql

🟠 Modified by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model is for a Balancer DEX on the Polygon blockchain. It selects data from two sources: balancer_v2_polygon_trades and prices. The former provides information about trades, including token addresses, amounts bought/sold, contract address of the project (pool), transaction hash etc. The latter provides USD prices for tokens at different times. These are joined to create a table with columns such as blockchain name (polygon), project name (balancer), version number (2) and more details about each trade like token symbols, amounts in raw form or converted to decimals/USD values etc., taker/maker addresses etc.

MODEL: balancer_v2_polygon_trades.sql

🟢 Added by:

🔧 PR: #3530, Important updates to Balancer Trades to improve dex.trades
🧙 Author: @thetroyharris on 2023-06-21
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze Balancer V2 Polygon pool swap transactions. The view includes information such as the blockchain, project, version, block date and time, token pair traded in the transaction (including their symbols), amounts of tokens bought and sold (in both raw form and converted to decimals), amount in USD at the time of transaction (if available), taker/maker addresses involved in each trade along with other relevant details like contract address for each pool used for trading.

SECTOR: account_abstraction

toggle to see all model updates

MODEL: account_abstraction_erc4337_userops_seed.csv

🟢 Added by:

🔧 PR: #3524, add erc4337 userops model
🧙 Author: @0xbitfly on 2023-06-21
📝 Summary: [changes too large] The model account_abstraction_erc4337_userops_seed.csv was added.

SECTOR: gas

toggle to see all model updates

MODEL: gas_gnosis_fees_traces.sql

🟢 Added by:

🔧 PR: #3565, Create gas_gnosis.fees_traces (#3549)
🧙 Author: @jeff-dude on 2023-06-21
📝 Summary: This SQL model creates a table that enables data analysts to analyze the gas usage of transactions on the Gnosis blockchain. The table includes information such as block time, block number, transaction hash, trace from/to addresses and methods used in traces and transactions. It also calculates various percentages related to gas usage and fees spent in USD for both original gas used by traces as well as traced gas. Additionally it joins with other tables to provide more context around each transaction including prices for specific contracts at specific times.

MODEL: gas_fantom_fees_traces.sql

🟢 Added by:

🔧 PR: #3564, Create gas_fantom.fees_traces (#3548)
🧙 Author: @jeff-dude on 2023-06-21
📝 Summary: This SQL model creates a table that enables data analysts to analyze transaction traces on the Fantom blockchain. The table includes information such as block time, block number, transaction hash, trace from/to addresses and methods used in the trace. It also calculates gas usage percentages and fees spent in both original and traced transactions. Additionally, it joins with other tables to provide more context such as USD prices for gas fees at specific times.

MODEL: gas_avalanche_c_fees_traces.sql

🟢 Added by:

🔧 PR: #3563, Create gas_avalanche_c.fees_traces (#3547)
🧙 Author: @jeff-dude on 2023-06-21
📝 Summary: This SQL model creates a table that enables data analysts to analyze the gas usage of transactions on the Avalanche blockchain. The table includes information such as block time, block number, transaction hash, trace from/to addresses and methods used in traces and transactions. It also calculates various percentages related to gas usage and fees spent in both original traces and their corresponding transactions. Additionally, it joins with other tables for further analysis of prices related to these transactions.

MODEL: gas_arbitrum_fees_traces.sql

🟠 Modified by:

🔧 PR: #3560, update incremental interval on fees_traces (#3556)
🧙 Author: @jeff-dude on 2023-06-18
📝 Summary: The diff shows changes made to a SQL model that queries data from the 'traces' and 'transactions' tables in an Arbitrum database. The changes involve modifying the time range of data being queried, specifically changing it from one week ago to one day ago. Additionally, there is a condition added that filters out any trace with an empty address field. Finally, there is a left join with another table called 'prices'.

🔧 PR: #3560, revert gas_blockchain.fees_traces
🧙 Author: @jeff-dude on 2023-06-16
📝 Summary: The diff shows changes made to a SQL model that queries data from two sources. The first change is in the traces common table expression (CTE) where the filter condition for block_time has been changed from 1 day ago to 1 week ago. The second change is in the join with another source called transactions, where again, the filter condition for block time has been changed from 1 day ago to 1 week ago. Finally, there's a left join with another source called 'prices' on minute and blockchain columns.

MODEL: gas_ethereum_fees_traces.sql

🟠 Modified by:

🔧 PR: #3560, update incremental interval on fees_traces (#3556)
🧙 Author: @jeff-dude on 2023-06-18
📝 Summary: The diff shows changes made to a SQL model that queries Ethereum blockchain data. The changes involve modifying the time range for which data is queried, from one week ago to one day ago. This change was applied in three different parts of the query where block_time is used as a filter condition. Additionally, there was an added filter condition that only includes traces with trace_address greater than zero and an inner join with another table called 'prices'.

🔧 PR: #3560, revert gas_blockchain.fees_traces
🧙 Author: @jeff-dude on 2023-06-16
📝 Summary: The diff shows changes to a SQL model that queries Ethereum blockchain data. The changes involve updating the time range for which data is queried, from one day ago to one week ago. This change applies to three different parts of the query: filtering traces by block_time, joining transactions on block_time and hash, and left joining prices on minute and blockchain. Additionally, a filter was added in two places requiring trace_address cardinality greater than zero before grouping by tx_hash, trace type (call or create), block time and number.

MODEL: gas_optimism_fees_traces.sql

🟠 Modified by:

🔧 PR: #3560, update incremental interval on fees_traces (#3556)
🧙 Author: @jeff-dude on 2023-06-18
📝 Summary: The diff shows changes made to a SQL model that queries data from the 'traces' and 'transactions' tables in an Optimism database. The changes involve modifying the time range of data being queried, specifically changing it from one week ago to one day ago. Additionally, there is a filter added for traces with non-zero trace addresses and left join with another table called 'prices'.

🔧 PR: #3560, revert gas_blockchain.fees_traces
🧙 Author: @jeff-dude on 2023-06-16
📝 Summary: The diff shows changes made to a SQL model that queries data from the 'traces' and 'transactions' tables of an Optimism blockchain source. The changes involve updating the time range for which data is queried, specifically changing it from one day ago to one week ago. Additionally, there is a filter added that only includes rows where the cardinality of trace_address column in traces table is greater than zero. Finally, there's a left join with another table called prices on minute and blockchain columns.

MODEL: gas_polygon_fees_traces.sql

🟠 Modified by:

🔧 PR: #3560, update incremental interval on fees_traces (#3556)
🧙 Author: @jeff-dude on 2023-06-18
📝 Summary: The diff shows changes made to a SQL model that queries the traces and transactions tables from the Polygon blockchain. The changes involve modifying the time range for which data is queried, specifically changing it from one week ago to one day ago. This change was made in three different places within the code where timestamps are compared against a specific date range using an interval function. Additionally, there is now a condition added that filters out any rows with empty trace addresses before grouping results by transaction hash, trace type, block time and number. Finally, there's also an outer join with another table called 'prices' on minute-level granularity of block times for transactions on Polygon blockchain only (blockchain='polygon').

🔧 PR: #3560, revert gas_blockchain.fees_traces
🧙 Author: @jeff-dude on 2023-06-16
📝 Summary: The diff shows changes made to a SQL model that queries data from the Polygon blockchain. The changes involve modifying the time range of data being queried, specifically changing it from one day to one week ago. This change is applied in three different parts of the query where block_time is used as a filter condition. Additionally, there's an added filter condition that ensures only traces with trace_address are selected and then grouped by tx_hash, trace type, block_time and number before being joined with another table called transactions using their respective hash values. Finally, there's also a left join operation on another table called prices based on minute and blockchain criteria for further analysis purposes

SECTOR: xchange

toggle to see all model updates

MODEL: xchange_arbitrum_trades.sql

🟢 Added by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: This SQL model creates a view of transaction data for the Xchange project on the Arbitrum blockchain. It includes information such as token symbols, amounts bought and sold, USD value of transactions, contract addresses involved in each transaction, and more. This enables data analysts to analyze trading activity on Xchange within the context of other projects on Arbitrum or across different blockchains. The model also supports incremental updates to keep up with new transactions over time.

MODEL: xchange_bnb_trades.sql

🟢 Added by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze transaction data from the Xchange project on the Binance Smart Chain. The view includes information such as token symbols, amounts bought and sold, USD value of transactions, contract addresses involved in each transaction, and more. It also joins with other tables to retrieve additional information about tokens and prices at specific times. This allows for deeper analysis of trading activity on Xchange within a specified time frame.

MODEL: xchange_polygon_trades.sql

🟢 Added by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze transaction data from the Xchange project on the Polygon blockchain. The view includes information such as token symbols, amounts bought and sold, USD value of transactions, contract addresses involved in each transaction, and more. It also joins with other tables to provide additional context such as token prices at the time of each transaction. This model is designed for incremental updates but can be modified for full rebuilds if necessary.

MODEL: xchange_trades.sql

🟢 Added by:

🔧 PR: #3576, Add poly, arb, and bnb xchange dex trades models and include xchange trades in dex.trades
🧙 Author: @mike-x7f on 2023-06-20
📝 Summary: This SQL model creates a view that combines trade data from multiple decentralized exchanges (DEXs) including Ethereum, Arbitrum, Polygon and Binance Smart Chain. The view includes information such as the tokens bought and sold in each trade, their amounts and prices in USD, as well as transaction details like block date/time and addresses involved. This enables data analysts to easily query across DEXs for insights into trading activity on different blockchain networks.

MODEL: xchange_ethereum_trades.sql

🟢 Added by:

🔧 PR: #3535, Add xchange dex trades objects for ethereum
🧙 Author: @mike-x7f on 2023-06-19
📝 Summary: This SQL model creates a view that enables data analysts to query and analyze transaction data from the Xchange project on the Ethereum blockchain. The view includes information such as token symbols, amounts bought and sold, USD value of transactions, contract addresses involved in each transaction, and more. It also joins with other tables to provide additional context such as token prices at the time of each transaction. This model is designed for incremental updates but can be modified for full rebuilds if necessary.

SECTOR: tigris

toggle to see all model updates

MODEL: tigris_v2_arbitrum_events_add_margin.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: The diff shows changes made to a SQL model that involves adding two new subqueries, add_margin_v3 and its union with the existing queries. The new subquery selects specific columns from a source table and applies some transformations on them. The resulting data is then used in the final query along with other versions of add_margin. An optional filter condition has also been added to both incremental and non-incremental modes for all three versions of add_margin.

MODEL: tigris_v2_arbitrum_events_close_position.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: This diff of a dbt SQL model adds and removes code related to the close_position_v2 table. The new version, close_position_v3, is created by selecting specific columns from another table called TradingV3_evt_PositionClosed. The data in this new version is unioned with the previous versions (v2.1 and v2.2) to create a final output that includes all three versions of the data with an added column indicating which version each row belongs to (v2.x).

MODEL: tigris_v2_arbitrum_events_limit_order.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: The SQL model adds a new table called TradingV3_evt_LimitOrderExecuted to the existing list of tables in the limit_order_trading_evt_tables variable. This variable is then used in a CTE (common table expression) called pairs, which joins data from two different versions of limit order executed events. The logic for this join remains unchanged from previous versions, with both tables being joined on their respective event IDs and timestamps.

MODEL: tigris_v2_arbitrum_events_liquidate_position.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: This diff adds a new SQL model called 'liquidate_position_v3' that selects specific columns from the 'TradingV3_evt_PositionLiquidated' table and renames them. It also includes a filter to only select rows where evt_block_time is within the last week if it's an incremental update. The final SELECT statement combines data from both liquidate_position_v2 and liquidate_position_v3 models, adding version numbers for each one.

MODEL: tigris_v2_arbitrum_events_modify_margin.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: This diff adds a new SQL model called modify_margin_v3 that joins three tables to calculate the margin change for each position. The result includes columns such as date, transaction hash, block time, position ID, modify type (add or remove), margin change amount and trader. This model is then appended to the existing models using UNION ALL with version numbers assigned to each output row. Additionally, an incremental filter has been added in both modify_margin_v2 and modify_margin_v3.

MODEL: tigris_v2_arbitrum_events_open_position.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: The diff shows changes made to a SQL model that creates a view of open trading positions. The new version, open_positions_v3, includes additional columns such as margin, leverage and volume in USD. It also joins with another table called pairs. The final output is the union of two views: open_positions_v2 and the newly added open_positions_v3.

MODEL: tigris_v2_polygon_events_add_margin.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: The diff shows changes made to a SQL model that adds two new subqueries, add_margin_v2 and add_margin_v3, which are then included in the final query using UNION ALL. The first subquery (add_margin_v2) is empty and has an optional incremental filter. The second subquery (add_margin_v3) selects specific columns from a source table with an additional trader column, converts some values to decimals, and also has an optional incremental filter. Finally, the updated model includes all versions of both subqueries in the final output with their respective version numbers as a string literal value appended to each row.

MODEL: tigris_v2_polygon_events_close_position.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: This diff of a dbt SQL model adds and removes code related to the close_position table. The new version, v2.3, includes additional columns such as payout, percent closed, and trader from the source table TradingV3_evt_PositionClosed. The previous version is removed (v2.2) while keeping its data in a union with the original version (v2.1). An incremental clause is added to both versions 2 and 3 for filtering by date range based on event block time in order to optimize performance when querying large datasets over time periods that have already been processed previously.

MODEL: tigris_v2_polygon_events_limit_order.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: The SQL model added a new table called TradingV3_evt_LimitOrderExecuted to the existing list of tables in the limit_order_trading_evt_tables variable. This variable is used in a subsequent CTE called limit_orders. The purpose of this change was likely to include data from the newly added table into the final output generated by this model.

MODEL: tigris_v2_polygon_events_liquidate_position.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: This diff adds a new SQL model called 'liquidate_position_v3' that selects specific columns from the 'TradingV3_evt_PositionLiquidated' table and renames them. It also includes a filter to only select rows where evt_block_time is within the last week if it's an incremental run. The final SELECT statement combines data from both v2.2 and v2.1 models, as well as adding data from the newly created v2.3 model using UNION ALL statements, with each subquery being labeled with its corresponding version number in a new column called 'version'.

MODEL: tigris_v2_polygon_events_modify_margin.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: This diff adds a new SQL model called modify_margin_v3 that joins three tables to calculate the margin change for each position. It also includes some conditional statements to filter data based on whether it is incremental or not. The final query selects all columns from both modify_margin_v1 and modify_margin_v2, as well as the newly added columns in modify_margin_v3. Finally, it appends a version column indicating which version of the model was used for each row.

MODEL: tigris_v2_polygon_events_open_position.sql

🟠 Modified by:

🔧 PR: #3557, add tigris trading v2.3 contracts
🧙 Author: @henrystats on 2023-06-20
📝 Summary: The diff shows changes made to a SQL model that creates a view of open trading positions. The new version, open_positions_v3, includes additional columns such as margin, leverage and volume in USD. It also joins with another table called pairs. The final output is the union of two views: open_positions_v1 and the updated versions (v2.2 and v2.3). If incremental mode is enabled, only records from the past week are included in both views v2.x

SECTOR: evms

toggle to see all model updates

MODEL: evms_transactions.sql

🟠 Modified by:

🔧 PR: #3579, Fix evms.transactions arbitrum duplicates
🧙 Author: @hildobby on 2023-06-20
📝 Summary: The removed section of the SQL model was selecting data from a source named 'arbitrum' and included various columns such as block hash, gas limit, success status, value etc. The added section only selects data from a source named 'optimism' and includes two columns: l1_timestamp and effective_gas_price.

SECTOR: opensea

toggle to see all model updates

MODEL: opensea_v3_polygon_events.sql

🟠 Modified by:

🔧 PR: #3542, Fix opensea_v3_polygon.events to include OS trades
🧙 Author: @sohwak on 2023-06-20
📝 Summary: The added code is commented out and it seems to be a condition that filters data based on the values of two columns, fee_wallet_name and right_hash. The removed lines show that this condition was previously being used in the query.

🔧 PR: #3542, Create OpenSea v3/v4 Optimism
🧙 Author: @sohwak on 2023-06-20
📝 Summary: The diff of the SQL model shows that a line was removed and another line was added. The removed line contained a condition to filter data based on fee_wallet_name and right_hash values, while the added line is empty. Therefore, it seems that this change does not affect the logic of the query as no new conditions were introduced or existing ones modified.

MODEL: opensea_ethereum_events.sql

🟠 Modified by:

🔧 PR: #3567, Fix table structures and misinterpretation on opensea_v3_ethereum
🧙 Author: @sohwak on 2023-06-20
📝 Summary: The SQL model is a UNION ALL of four tables with the same columns. The added table has more columns than the others, so it's placed first in the union. Some column names have been changed and some calculations have been simplified or removed.

MODEL: opensea_ethereum_schema.yml

🟠 Modified by:

🔧 PR: #3567, Fix table structures and misinterpretation on opensea_v3_ethereum
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_ethereum_schema.yml was modified.

MODEL: opensea_v3_ethereum_events.sql

🟠 Modified by:

🔧 PR: #3567, Fix table structures and misinterpretation on opensea_v3_ethereum
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_v3_ethereum_events.sql was modified.

MODEL: opensea_v4_ethereum_events.sql

🟠 Modified by:

🔧 PR: #3567, Fix table structures and misinterpretation on opensea_v3_ethereum
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_v4_ethereum_events.sql was modified.

MODEL: opensea_v4_ethereum_trades.sql

🟠 Modified by:

🔧 PR: #3567, Fix table structures and misinterpretation on opensea_v3_ethereum
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_v4_ethereum_trades.sql was removed.

MODEL: opensea_optimism_events.sql

🟠 Modified by:

🔧 PR: #3542, Create OpenSea v3/v4 Optimism
🧙 Author: @sohwak on 2023-06-20
📝 Summary: The SQL model has been modified to include a UNION ALL statement that combines two tables. The SELECT statement now includes additional columns such as currency_decimals, platform_fee_receive_address, royalty_fee_receive_address_1-5 and royalty_fee_amount_raw_1-5. Some of the original columns have also been removed from the SELECT statement but are still present in both tables being combined.

MODEL: opensea_v3_optimism_events.sql

🟢 Added by:

🔧 PR: #3542, Create OpenSea v3/v4 Optimism
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_v3_optimism_events.sql was added.

MODEL: opensea_v4_optimism_events.sql

🟢 Added by:

🔧 PR: #3542, Create OpenSea v3/v4 Optimism
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_v4_optimism_events.sql was added.

MODEL: opensea_optimism_seaport_trades_samples.csv

🟢 Added by:

🔧 PR: #3542, Create OpenSea v3/v4 Optimism
🧙 Author: @sohwak on 2023-06-20
📝 Summary: [changes too large] The model opensea_optimism_seaport_trades_samples.csv was added.

SECTOR: seaport

toggle to see all model updates

MODEL: seaport_ethereum_schema.yml

🟠 Modified by:

🔧 PR: #3568, Remove unused models on seaport schema
🧙 Author: @sohwak on 2023-06-19
📝 Summary: [changes too large] The model seaport_ethereum_schema.yml was modified.

MODEL: seaport_ethereum_transfers.sql

🟠 Modified by:

🔧 PR: #3568, Remove unused models on seaport schema
🧙 Author: @sohwak on 2023-06-19
📝 Summary: [changes too large] The model seaport_ethereum_transfers.sql was removed.

MODEL: seaport_ethereum_view_transactions.sql

🟠 Modified by:

🔧 PR: #3568, Remove unused models on seaport schema
🧙 Author: @sohwak on 2023-06-19
📝 Summary: [changes too large] The model seaport_ethereum_view_transactions.sql was removed.