📰 2024-05-17: Weekly Prophet!

[andrewhong5297]

This is your weekly summary of 11 PRs merged from 8 wizards. Great job everyone! 🎉
We had 8 added models 🟢 and 32 modified models 🟠 for 10 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
labels	1	0	4	1
prices	1	0	1	1
oneinch	1	0	3	1
boost	1	2	13	1
_sector	3	2	4	3
balancer	1	1	0	1
cex	1	0	3	1
yield_yak	1	3	1	1
staking	1	0	2	1
rollup_economics	1	0	1	1

SECTOR: labels

toggle to see all model updates

MODEL: labels_balancer_v2_pools_avalanche_c.sql

🟠 Modified by:

🔧 PR: #5924, add new pool types to labels.balancer_v2_pools
🧙 Author: @viniabussafi on 2024-05-15
📝 Summary: Added a new section to the SQL model that creates pools by selecting specific columns from two different sources and performing joins. The logic involves extracting data such as pool ID, token address, normalized weight, symbol, and pool type. Additionally, an inner join is used with conditions on transaction hashes and output pools. A CASE statement was updated in the 'settings' section to include 'managed' as a valid pool_type for further processing based on certain conditions.

MODEL: labels_balancer_v2_pools_base.sql

🟠 Modified by:

🔧 PR: #5924, add new pool types to labels.balancer_v2_pools
🧙 Author: @viniabussafi on 2024-05-15
📝 Summary: Added a section to the SQL model that selects specific columns from two sources and performs various operations like converting data types and extracting values from JSON fields. Additionally, modified a CASE statement in the 'settings' section to include an additional condition for 'pool_type'.

MODEL: labels_balancer_v2_pools_gnosis.sql

🟠 Modified by:

🔧 PR: #5924, add new pool types to labels.balancer_v2_pools
🧙 Author: @viniabussafi on 2024-05-15
📝 Summary: Added logic to the SQL model includes selecting data from different sources based on specific conditions and joining tables using inner joins. The new logic involves extracting token addresses, symbols, pool types, and normalized weights for various pools such as linear, managed, and ECLP pools. Additionally, there is a modification in the settings section where additional pool types are included in a CASE statement for further processing based on certain conditions.

MODEL: labels_balancer_v2_pools_zkevm.sql

🟠 Modified by:

🔧 PR: #5924, add new pool types to labels.balancer_v2_pools
🧙 Author: @viniabussafi on 2024-05-15
📝 Summary: Added logic to extract data from two different sources and join them based on specific conditions. The added logic involves selecting columns like pool_id, token_address, normalized_weight, symbol, and pool_type from the 'Vault_evt_PoolRegistered' source and 'ManagedPoolFactory_call_create' source. Additionally, a condition was added to include the 'managed' pool type in a CASE statement within the settings section when checking for certain values in the pool_type column.

SECTOR: prices

toggle to see all model updates

MODEL: prices_optimism_tokens_curated.sql

🟠 Modified by:

🔧 PR: #5904, Add ankrETH on OPT to prices.usd
🧙 Author: @viniabussafi on 2024-05-13
📝 Summary: One token was added ('aeth-ankreth', 'ankrETH', 0xe05a08226c49b636acf99c40da8dc6af83ce5bb3, 18) and one token was removed ('beets-beethoven-x', 'BEETS', 0xb4bc46bc6cb217b59ea8f4530bae26bf69f677f0, 18).

SECTOR: oneinch

toggle to see all model updates

MODEL: oneinch_ar_trades.sql

🟠 Modified by:

🔧 PR: #5910, 1inch: added fusion to ar_trades & ar and lop macro improvements
🧙 Author: @max-morrow on 2024-05-15
📝 Summary: Added conditions to filter rows where the 'protocol' column is equal to 'AR', or the 'flags' column contains either 'fusion' or 'second_side'.

MODEL: oneinch_lop_own_trades.sql

🟠 Modified by:

🔧 PR: #5910, 1inch: added fusion to ar_trades & ar and lop macro improvements
🧙 Author: @max-morrow on 2024-05-15
📝 Summary: The changes involve updating the conditions in the WHERE clause to check for values in a nested field called 'flags' instead of directly checking boolean columns.

MODEL: oneinch_swaps.sql

🟠 Modified by:

🔧 PR: #5910, 1inch: added fusion to ar_trades & ar and lop macro improvements
🧙 Author: @max-morrow on 2024-05-15
📝 Summary: In the tokens CTE, a condition was modified to check for specific elements in the 'flags' field. In the select statement, columns related to 'fusion', 'contracts_only', and 'second_side' are marked for deletion in the next step.

SECTOR: boost

toggle to see all model updates

MODEL: boost_arbitrum_claimed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new entry for 'zksync' was added, and an entry for 'blast' was also added to the dbt SQL model.

MODEL: boost_arbitrum_deployed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new key-value pair was added to the dictionary.

MODEL: boost_base_claimed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: Two new entries were added to the dbt SQL model: 'zksync' and 'blast'.

MODEL: boost_base_deployed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new key-value pair was added to the dictionary with the key '81457' and value 'blast'.

MODEL: boost_claimed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new reference to 'boost_zora_claimed' was added. The column 'creator_address' was added to the quest_claims_enriched CTE and unified_claims CTE. Additionally, the logic for joining tables in the SQL query was updated to include this new column in both cases.

MODEL: boost_claimed_legacy.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: Added the 'creator_address' column to the 'receipt_mints' and 'erc20_claims' CTEs by joining with a new model. In the final select statement, included this new column along with other existing columns.

MODEL: boost_completors.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A calculation for 'zora_fee_eth' was added based on the sum of gas_used multiplied by gas_price divided by 1e18.

MODEL: boost_ethereum_claimed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new entry for 'zksync' was added, and an entry for 'blast' was also added to the dbt SQL model.

MODEL: boost_ethereum_deployed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new value 'blast' was added to the dictionary. The line selecting 'base' as reward_network was removed from the SQL query.

MODEL: boost_optimism_claimed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new entry for 'zksync' was added, and an entry for 'blast' was also added to the dbt SQL model.

MODEL: boost_optimism_deployed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new key-value pair was added to the dictionary with the key '81457' and value 'blast'.

MODEL: boost_polygon_claimed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: Added support for the 'zksync' and 'blast' reward networks in the SQL model.

MODEL: boost_polygon_deployed.sql

🟠 Modified by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: A new value 'blast' was added to the dictionary. The SQL query now includes selecting 'polygon' as reward_network and contractAddress as boost_address.

MODEL: boost_zora_claimed.sql

🟢 Added by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: This model creates a dataset that maps rewards and claims data from the Zora platform to different blockchain networks. It enables data analysts to analyze boost rewards, boost IDs and names, claimer addresses, reward amounts in raw form and token addresses. Additionally, it provides information on claim transaction hashes, block times, claim fees in ETH, action types and transaction hashes related to actions on various blockchain networks such as Ethereum, Optimism,Polygon,Zksync,Mantle etc.

MODEL: boost_zora_deployed.sql

🟢 Added by:

🔧 PR: #5920, Added Zora chain to Boost Spells
🧙 Author: @pntemi on 2024-05-15
📝 Summary: This model creates a dataset that maps different blockchain network IDs to their corresponding names. It then selects various fields related to boost rewards from the Zora platform, including addresses, IDs, names, types, times, amounts and participants. The model also includes information about the project name and creator. This mapping enables data analysts to easily identify which blockchain network each boost action belongs to when analyzing Zora's reward data.

SECTOR: _sector

toggle to see all model updates

MODEL: dex_pools_metrics_daily.sql

🟢 Added by:

🔧 PR: #5896, Create daily pool metrics spell
🧙 Author: @viniabussafi on 2024-05-14
📝 Summary: This model combines data from the 'balancer_pools_metrics_daily' table for various blockchain projects. It includes metrics such as swap amount, total value locked (USD and ETH), and fee amount in USD. The model enables analysts to analyze and compare pool performance across different projects on a daily basis, facilitating insights into liquidity provision and trading activity within decentralized exchanges.

MODEL: nft_base_base_trades.sql

🟠 Modified by:

🔧 PR: #5870, Add Magic Eden on Base
🧙 Author: @hildobby on 2024-05-14
📝 Summary: A reference to a new model called 'magiceden_base_base_trades' was added in the SQL code.

MODEL: magiceden_base_base_trades.sql

🟢 Added by:

🔧 PR: #5870, Add Magic Eden on Base
🧙 Author: @hildobby on 2024-05-14
📝 Summary: This model creates a dataset of trades involving NFTs, including buy listings and accepted offers. It also identifies platform and royalty fees associated with these trades. The model categorizes the trades by type, such as 'Buy' or 'Offer Accepted', and provides details on the involved parties, contract addresses, prices, and fee amounts. Additionally, it groups whitelisted trades by sellers to analyze bundled transactions efficiently for further insights into blockchain activities related to NFT trading within the specified time frame starting from February 16th in 2024.

MODEL: labels_counterparty_activity_daily.sql

🟠 Modified by:

🔧 PR: #5921, Labels fix: ignore transfers above 1 trillion
🧙 Author: @0xRobin on 2024-05-14
📝 Summary: Added a condition to filter rows where the amount in USD is less than 10^12.

MODEL: labels_transfer_summary.sql

🟠 Modified by:

🔧 PR: #5921, Labels fix: ignore transfers above 1 trillion
🧙 Author: @0xRobin on 2024-05-14
📝 Summary: A condition was added to filter rows where the amount in USD is less than a certain value.

MODEL: labels_transfer_summary_daily.sql

🟠 Modified by:

🔧 PR: #5921, Labels fix: ignore transfers above 1 trillion
🧙 Author: @0xRobin on 2024-05-14
📝 Summary: Added a condition to filter rows where the 'amount_usd' is less than 10^12. This logic was added for both inner join queries, along with an incremental predicate check based on block time if the query is incremental.

SECTOR: balancer

toggle to see all model updates

MODEL: balancer_pools_metrics_daily.sql

🟢 Added by:

🔧 PR: #5896, Create daily pool metrics spell
🧙 Author: @viniabussafi on 2024-05-14
📝 Summary: This model combines data from three different sources related to Balancer protocol: trades, liquidity, and fees. It calculates various metrics such as swap amount in USD, total value locked (TVL) in USD and ETH, and fee amounts collected. By joining these datasets on relevant fields like block date and project contract address, analysts can analyze the performance of Balancer protocol across different versions and blockchains. The model enables tracking trends in trading activity, liquidity provision, and fee collection for informed decision-making by data analysts within the organization.

SECTOR: cex

toggle to see all model updates

MODEL: cex_bitcoin_addresses.sql

🟠 Modified by:

🔧 PR: #5882, Add more CEX addresses
🧙 Author: @hildobby on 2024-05-14
📝 Summary: A new row for 'XT.com' with corresponding details was added to the existing dataset.

MODEL: cex_evms_addresses.sql

🟠 Modified by:

🔧 PR: #5882, Add more CEX addresses
🧙 Author: @hildobby on 2024-05-14
📝 Summary: [changes too large] The model cex_evms_addresses.sql was modified.

MODEL: cex_tron_addresses.sql

🟠 Modified by:

🔧 PR: #5882, Add more CEX addresses
🧙 Author: @hildobby on 2024-05-14
📝 Summary: Three new rows were added to the existing dataset with information related to different blockchain addresses, CEX names, distinct names, and addition details.

SECTOR: yield_yak

toggle to see all model updates

MODEL: yield_yak_arbitrum_user_yrt_balances.sql

🟢 Added by:

🔧 PR: #5906, Added user level balance tracking for Yield Yak YRT tokens
🧙 Author: @yy-analytics on 2024-05-13
📝 Summary: This SQL model creates a view called yield_yak_user_yrt_balances that enables data analysts to access and analyze user yield balances specific to the Arbitrum blockchain.

MODEL: yield_yak_avalanche_c_user_yrt_balances.sql

🟢 Added by:

🔧 PR: #5906, Added user level balance tracking for Yield Yak YRT tokens
🧙 Author: @yy-analytics on 2024-05-13
📝 Summary: This SQL model creates a view called yield_yak_user_yrt_balances that enables data analysts to access and analyze user yearn token (YRT) balances on the Avalanche C blockchain.

MODEL: yield_yak_user_yrt_balances.sql

🟢 Added by:

🔧 PR: #5906, Added user level balance tracking for Yield Yak YRT tokens
🧙 Author: @yy-analytics on 2024-05-13
📝 Summary: This model creates a unified view of yield yaks' balance data from different blockchain sources. It enables data analysts to easily query and analyze the aggregated yrt balances for users across multiple contracts and time periods.

MODEL: yield_yak_avalanche_c_sources.yml

🟠 Modified by:

🔧 PR: #5906, Added user level balance tracking for Yield Yak YRT tokens
🧙 Author: @yy-analytics on 2024-05-13
📝 Summary: [changes too large] The model yield_yak_avalanche_c_sources.yml was modified.

SECTOR: staking

toggle to see all model updates

MODEL: staking_ethereum_entities_contracts.sql

🟠 Modified by:

🔧 PR: #5915, Add eth staking entities stader
🧙 Author: @sankinyue on 2024-05-13
📝 Summary: Two new contracts were added to the 'contracts' CTE with Stader as the entity and Liquid Staking as the category. The condition for filtering transactions based on block time was removed from the query.

MODEL: staking_ethereum_entities_depositor_addresses.sql

🟠 Modified by:

🔧 PR: #5915, Add eth staking entities stader
🧙 Author: @sankinyue on 2024-05-13
📝 Summary: A new record for 'Daniel Wang 17' was added, and a record for 'danielwang.eth' was removed. The structure of the model remained the same with columns depositor_address, entity, entity_unique_name, and category being selected from different addresses along with their corresponding details.

SECTOR: rollup_economics

toggle to see all model updates

MODEL: l2_revenue.sql

🟠 Modified by:

🔧 PR: #5905, Add linea and polygon revenue to rollup economics
🧙 Author: @Jam516 on 2024-05-13
📝 Summary: Two new SELECT statements were added to the model. Each SELECT statement calculates revenue metrics for different platforms ('linea' and 'polygon zkevm') by joining transaction and price data sources. The logic includes aggregating gas usage multiplied by gas price, converting it to a specific unit, and then multiplying it by the USD price of ETH. The GROUP BY clause groups the results by day and platform name in both cases.