📰 2023-12-22: Weekly Prophet!

[andrewhong5297]

This is your weekly summary of 25 PRs merged from 15 wizards. Great job everyone! 🎉
We had 71 added models 🟢 and 94 modified models 🟠 for 14 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
dex	3	0	3	2
labels	2	16	5	1
tokens	2	0	5	2
prices	2	0	2	2
firebird_finance	1	1	0	1
balancer	1	2	24	1
lido	1	1	2	1
_sector	12	20	37	5
aragon	1	2	1	1
dao	1	3	11	1
oneinch	1	23	1	1
hashflow	1	2	1	1
safe	1	1	1	1
datasets	1	0	1	1

SECTOR: dex

toggle to see all model updates

MODEL: dex_aggregator_trades.sql

🟠 Modified by:

🔧 PR: #4907, Added firebird_finance dex
🧙 Author: @ARDev097 on 2023-12-22
📝 Summary: The reference models that were added or removed in the diff are:

• Added: firebird_finance_optimism_trades

MODEL: dex_info.sql

🟠 Modified by:

🔧 PR: #5010, Add ImmortalX to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-19
📝 Summary: In the given SQL model, a new row has been added to the temporary table. The row represents a project named 'ImmortalX' with corresponding values for name, marketplace type, and x_username.

MODEL: dex_trades.sql

🟠 Modified by:

🔧 PR: #4999, Added Optimism Hashflow Dex
🧙 Author: @ARDev097 on 2023-12-18
📝 Summary: The reference models that were added or removed in the diff are:
hashflow_optimism_trades

SECTOR: labels

toggle to see all model updates

MODEL: labels_addresses.sql

🟠 Modified by:

🔧 PR: #4937, [EASY] Add back accidentally removed ref to label_addresses model
🧙 Author: @kaiblade on 2023-12-18
📝 Summary: The reference models that were added or removed in the diff are:
labels_op_retropgf

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: The reference models that were added are: labels_op_attestationstation_attesters, labels_op_attestationstation_creators, labels_op_governance_delegators, labels_op_governance_derived_archetype, labels_op_governance_retropgf_proposal_submitters,labels_op_governance_voters ,labels_ op_perpetual_traders ,labels_ op_traders_derived_archetype and labels_ op_transfers_only. The reference model that was removed is: labels_quest_participants.

MODEL: labels_op_attestationstation_attesters.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table that enables data analysts to query and analyze attestation attestors in the 'optimism' blockchain. It includes information such as the address, name, category, contributor, source, creation date, update date,model name,and label type of each attester. The data is sourced from the 'attestationstation_v1_optimism' table and grouped by attester.

MODEL: labels_op_attestationstation_creators.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a summary of attestation creators in the 'attestationstation_optimism' data source. It provides information such as the blockchain, address, name, category, contributor, source query used to create the model, creation and update timestamps. The model is named 'op_attestationstation_creators' and it focuses on personas by labeling them as 'persona'.

MODEL: labels_op_governance_delegators.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'op_governance_delegators' that contains information about OP Delegators on the Optimism blockchain. It includes columns for the blockchain name, address, name (label), category, contributor, source type, creation and update timestamps. This model enables data analysts to query and analyze data related to OP Delegators in the context of governance on Optimism.

MODEL: labels_op_governance_derived_archetype.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a derived archetype called 'op_governance_derived_archetype' that identifies addresses as 'OP Governance Junkie' if they have more than 2 occurrences in any of the specified governance label models. It provides information such as the blockchain, address, name (label), category, contributor, source, creation and update timestamps. This model enables data analysts to easily identify and categorize users who are highly active in OP governance activities.

MODEL: labels_op_governance_retropgf_proposal_submitters.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'op_governance_retropgf_proposal_submitters' that contains information about RetroPGF proposal submitters on the optimism blockchain. It includes columns for the submitter's address, label (RetroPGF Proposal Submitter), category (op_governance), contributor (kaiblade), source (query), created_at timestamp, updated_at timestamp, model_name ('op_governance_retropgf_proposal_submitters'), and label_type ('persona'). This table enables data analysts to analyze and track RetroPGF proposal submitters on the optimism blockchain.

MODEL: labels_op_governance_retropgf_voters.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'op_governance_retropgf_voters' that contains information about RetroPGF voters on the Optimism blockchain. It includes columns for the voter's address, label (set as 'RetroPGF Voter'), category ('op_governance'), contributor ('kaiblade'), source ('query'), created_at timestamp, updated_at timestamp, and label_type ('persona'). The data is filtered based on certain conditions and grouped by address and label. This model enables data analysts to analyze and track RetroPGF voters in the context of governance activities on the Optimism blockchain.

MODEL: labels_op_governance_voters.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table that categorizes voters on the Optimism blockchain based on their voting behavior. It calculates the number of votes cast by each address and assigns labels such as 'Avid Optimism Voter,' 'Active Optimism Voter,' or 'Casual Optimism Voter' based on predefined percentiles. The resulting table includes columns for blockchain, address, name (label), category, contributor, source, created_at timestamp, updated_at timestamp,model_name,label_type.

MODEL: labels_op_perpetual_traders.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table that categorizes traders based on their trading activity in the 'perpetual_trades' dataset. It calculates the number of trades for each trader, determines percentiles for trade counts, and assigns labels ('Elite Perp Trader', 'Active Perp Trader', or 'Normie Perp Trader') based on these percentiles. The resulting table includes information such as blockchain, trader address, label name, category, contributor, source details (query), timestamps (created_at and updated_at), model name ('op_governance_derived_archetype'), and label type ('persona').

MODEL: labels_op_traders_derived_archetype.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a derived archetype for traders based on their activity in different trading categories. It combines data from three tables and assigns a label to each trader based on their proficiency in NFT, DEX, and perpetual trading. The model outputs the trader's address, label name (such as 'Master Trader' or 'Jack of All Trades'), blockchain name ('optimism'), category ('op_traders'), contributor ('kaiblade'), source type ('query'), creation date, update date, model name ('op_trades_derived_archetype') and label type('persona').

MODEL: labels_op_transfers_only.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'transfers_only' that identifies addresses on the 'optimism' blockchain that have only made transfers in the ERC20 namespace. It includes columns for blockchain, address, name (label), category, contributor, source, created_at timestamp, updated_at timestamp,model_name and label_type. This model enables data analysts to easily query and analyze addresses that have exclusively made transfers on the optimism blockchain.

MODEL: labels_bridges.sql

🟠 Modified by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: In this SQL model, two references were removed: 'labels_bridges_ethereum' and 'labels_bridges_fantom'. Two new references were added: 'labels_op_bridge_users' and 'labels_op_bridge_derived_archetype'. The remaining references in the list are unchanged.

MODEL: labels_op_bridge_derived_archetype.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a derived archetype for 'Prolific Bridge Users' by selecting addresses with at least 3 occurrences from the 'labels_op_bridge_users' table. It then generates a result set with columns representing blockchain, address, name (label), category, contributor, source, created_at timestamp, updated_at timestamp,model_name and label_type. This model enables data analysts to analyze and categorize bridge users based on their usage patterns.

MODEL: labels_op_bridge_users.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'combined_bridge_users' that combines data from multiple sources related to different bridge users on the Optimism blockchain. It includes users from the Optimism Bridge, Celer cBridge, Synapse Bridge, Hop Bridge, Across Bridge, and Multichain Bridge. The table includes columns for blockchain name (Optimism), user address, user label/name/category/contributor/source/created_at/updated_at/model_name/label_type. This model enables data analysts to analyze and understand various types of bridge users on the Optimism blockchain.

MODEL: labels_dex.sql

🟠 Modified by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: In this SQL model, the references to 'labels_trader_age', 'labels_trader_dex_diversity', and 'labels_trader_frequencies' were added. Additionally, a reference to 'labels_op_dex_traders' was also added. The SELECT statement remains unchanged.

MODEL: labels_op_dex_traders.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'dex_traders' that categorizes traders on the Optimism blockchain based on their trading activity. Traders are labeled as 'Elite DEX Trader,' 'Active DEX Trader,' or 'Normie DEX Trader' depending on the number of trades they have made. The model provides information such as the trader's address, label, blockchain, category, contributor, source, creation and update timestamps. This enables data analysts to analyze and understand different types of traders in the decentralized exchange (DEX) ecosystem.

MODEL: labels_nft.sql

🟠 Modified by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: In this diff, three new references were added to the nft_labels_models list: labels_op_nft_minters, labels_op_nft_traders, and labels_op_habitual_wash_traders. These references were not present before. The SELECT statement remains unchanged.

MODEL: labels_op_nft_minters.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'nft_minters' that categorizes NFT minters on the Optimism blockchain. It calculates the number of mints for each minter and assigns them labels based on their mint count percentile. The labels are 'Voracious NFT Minter' for those in the top 5%, 'Active NFT Minter' for those in the top 35-65%, and 'Normie NFT Minter' for everyone else. The resulting table includes columns like address, name, category, contributor, source, created_at, updated_at,model_name,and label_type to provide information about these minters.

MODEL: labels_op_habitual_wash_traders.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table that identifies habitual NFT wash traders on the optimism blockchain. It calculates the trade count for each trader and determines the 0.95th percentile of trade counts. Traders with a trade count equal to or higher than this threshold are labeled as 'Habitual NFT Wash Trader'. The resulting table includes information such as address, name, category, contributor, source, creation date, update date, model name (op_habitual_wash_traders), and label type (persona).

MODEL: labels_op_nft_traders.sql

🟢 Added by:

🔧 PR: #4937, Add OP Address Archetype Labels
🧙 Author: @kaiblade on 2023-12-15
📝 Summary: This SQL model creates a table called 'nft_traders' that categorizes traders based on their number of trades. It calculates the 95th and 65th percentiles of trade counts, and assigns labels such as 'Elite NFT Trader,' 'Active NFT Trader,' or 'Normie NFT Trader' to each trader based on these thresholds. The resulting table includes columns for blockchain, address, name (label), category, contributor, source, created_at timestamp, updated_at timestamp,model_name,label_type.

SECTOR: tokens

toggle to see all model updates

MODEL: tokens_arbitrum_erc20.sql

🟠 Modified by:

🔧 PR: #5038, add NPM token
🧙 Author: @flashburst on 2023-12-22
📝 Summary: The token symbols that were added or removed are: NPM

MODEL: tokens_bnb_bep20.sql

🟠 Modified by:

🔧 PR: #5038, add NPM token
🧙 Author: @flashburst on 2023-12-22
📝 Summary: In the given SQL model, a new row has been added to select the contract address, symbol, and decimals. The new row includes the contract address 0x57f12FE6A4e5fe819eec699FAdf9Db2D06606bB4 with symbol 'NPM' and decimal value of 18.

MODEL: tokens_ethereum_erc20.sql

🟠 Modified by:

🔧 PR: #5038, add NPM token
🧙 Author: @flashburst on 2023-12-22
📝 Summary: The token symbols that were added or removed are: NPM

MODEL: tokens_polygon_erc20.sql

🟠 Modified by:

🔧 PR: #5038, add NPM token
🧙 Author: @flashburst on 2023-12-22
📝 Summary: The token symbols that were added or removed in the given diff are: ANKR, NSDX, NSFW, NPM, dQUICK, TRADE and BETS.

MODEL: tokens_avalanche_c_erc20.sql

🟠 Modified by:

🔧 PR: #5035, Add PHAR to tokens_avalanche_c_erc20.sql
🧙 Author: @discochuck on 2023-12-22
📝 Summary: The token symbols that were added or removed in the diff are: PHAR

SECTOR: prices

toggle to see all model updates

MODEL: prices_zksync_tokens.sql

🟠 Modified by:

🔧 PR: #5033, Add HoldStation token - zksync
🧙 Author: @holdstation on 2023-12-22
📝 Summary: The token symbols that were added or removed are: HOLD

MODEL: prices_optimism_tokens_curated.sql

🟠 Modified by:

🔧 PR: #4997, Adding velo price
🧙 Author: @ppclunghe on 2023-12-15
📝 Summary: In this diff, a line was removed and another line was added. The removed line had the token address for VELO as 0x3c8b650257cfb5f272f799f5e2b4e65093a11a05, while the added line replaced it with 0x9560e827af36c94d2ac33a39bce1fe78631088db. This suggests that there was an update to the token address for VELO in the model.

SECTOR: firebird_finance

toggle to see all model updates

MODEL: firebird_finance_optimism_trades.sql

🟢 Added by:

🔧 PR: #4907, Added firebird_finance dex
🧙 Author: @ARDev097 on 2023-12-22
📝 Summary: This SQL model creates a table that combines data from multiple sources to analyze transactions on the FireBird Finance project in the Optimism blockchain. It includes information such as block time, token symbols, amounts bought and sold, USD value, addresses involved in the transaction, and more. The model enables data analysts to perform analysis on transaction data for the FireBird Finance project on Optimism.

SECTOR: balancer

toggle to see all model updates

MODEL: balancer_arbitrum_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' was added to the SELECT statement. The rest of the logic remains unchanged.

MODEL: balancer_v2_arbitrum_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of pool liquidity estimates for a specific blockchain (in this case, 'arbitrum'). The model retrieves data from various sources such as price data, swap changes, balance changes, and managed changes. It then calculates cumulative balances and converts them to USD values based on token prices. Finally, it estimates the pool liquidity by summing up the USD values of tokens in each pool and dividing it by their respective normalized weights.

MODEL: balancer_v2_arbitrum_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this diff is the inclusion of a new column 'blockchain' with the value 'arbitrum'. Additionally, a left join is performed between the 'normalized_weights' table and the 'registered' table using their respective pool IDs. The condition for joining is that only 20 characters from BYTEARRAY_SUBSTRING of registered.pool_id should match normalized_weights.pool_id. Finally, rows are filtered where w.pool_id is not null.

MODEL: balancer_avalanche_c_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' was added to the SELECT statement. The rest of the logic remains unchanged.

MODEL: balancer_v2_avalanche_c_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of liquidity estimates for Balancer pools on the Avalanche C blockchain. It retrieves data from various sources such as token prices, pool balances, swaps, and managed changes. The model calculates cumulative balance amounts and converts them to USD values based on token prices. It also estimates pool liquidity by dividing the total pool liquidity in USD by the normalized weight of each token in the pool. The final result includes information about each day's liquidity for each Balancer pool on Avalanche C blockchain.

MODEL: balancer_v2_avalanche_c_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The logic added in this diff is to select the blockchain name as 'avalanche_c', along with pool_id, token_address, and normalized_weight from the normalized_weights table. It also performs a left join with the registered table on matching pool_id values. The WHERE clause filters out any rows where w.pool_id is NULL.

MODEL: balancer_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic of the changes in this SQL model is that a new column called 'protocol_liquidity_eth' was added, and the existing column 'protocol_liquidity_usd' was renamed to 'pool_liquidity_usd'. Additionally, a new column called 'pool_liquidity_eth' was added. The model is also now performing a UNION ALL operation on multiple tables.

MODEL: balancer_pools_tokens_weights.sql

🟢 Added by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: This SQL model creates a view that combines data from multiple Balancer pool token weight tables across different blockchains. It enables data analysts to easily query and analyze the normalized weights of tokens in various Balancer pools on different blockchains.

MODEL: balancer_token_whitelist.sql

🟢 Added by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: This SQL model creates a whitelist of tokens that can be used as pricing assets on liquidity calculations for weighted pools. It includes tokens from various chains such as Arbitrum, Avalanche, Base, Gnosis, Ethereum, Optimism, and Polygon. The whitelist includes token addresses along with their corresponding names and chains they belong to.

MODEL: balancer_base_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' was added to the SELECT statement. The rest of the logic remains unchanged.

MODEL: balancer_v2_base_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of cumulative liquidity for Balancer pools. It calculates the cumulative balance and USD value for each token in a pool over time, taking into account swaps, balance changes, and managed changes. It also estimates the liquidity of each pool by summing up the USD values of all tokens in the pool and dividing it by their normalized weights. The final result includes information such as day, pool ID, token address/symbol/balance/liquidity (both protocol and pool), version number, blockchain type (base), and labels for pools.

MODEL: balancer_v2_base_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' is added to the SELECT statement. The value of this column is set as 'base'. Additionally, a LEFT JOIN is performed between the tables 'normalized_weights' and 'registered', where matching pool IDs are joined based on their first 20 characters. Finally, a filter condition excludes rows where the pool ID in normalized_weights table is NULL.

MODEL: balancer_ethereum_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In this SQL model, a UNION operation was added to combine the results of two SELECT statements. The first SELECT statement retrieves data from the 'balancer_v1_ethereum_pools_tokens_weights' table and includes columns for blockchain, pool_id, token_address, and normalized_weight. The second SELECT statement is similar but does not specify a table name. Both statements cast the normalized_weight column as double before returning the results.

MODEL: balancer_v1_ethereum_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In this diff, the following changes were made to the SQL model:

• The 'prices' CTE was moved up in the query.
• A new CTE called 'eth_prices' was added to calculate average ETH prices.
• The calculation for protocol_liquidity_eth and pool_liquidity_eth was added using eth_price from eth_prices CTE.

MODEL: balancer_v1_ethereum_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The added logic in this SQL model is for the 'norm_weights' table. It selects data from a source table and adds a new column called 'blockchain' with the value 'ethereum'. The other columns selected are pool_address, token_address, and normalized_weight.

MODEL: balancer_v2_ethereum_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of liquidity estimates for Balancer pools on the Ethereum blockchain. It retrieves data from various sources such as token prices, pool balances, swaps, and managed changes. The model calculates cumulative balances and then joins them with other tables to calculate liquidity in USD for each pool. Finally, it selects relevant columns including pool ID, symbol, version information, token address and balance details to generate liquidity estimates for Balancer pools on Ethereum.

MODEL: balancer_v2_ethereum_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The added logic includes selecting the blockchain as 'ethereum', pool_id, token_address, and normalized_weight from the normalized_weights table. It also performs a left join with the registered table on matching pool_id values. The WHERE clause filters out rows where pool_id is null.

MODEL: balancer_gnosis_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' was added to the SELECT statement. The rest of the logic remains unchanged.

MODEL: balancer_v2_gnosis_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of pool liquidity estimates for a specific blockchain (in this case, 'gnosis'). The model retrieves data from various sources such as token prices, pool balances, swaps changes, and managed changes. It then calculates cumulative balance amounts and converts them to USD values based on token prices. Finally, it estimates the liquidity of each pool by summing up the USD values of its tokens weighted by their normalized weights.

MODEL: balancer_v2_gnosis_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a table called 'normalized_weights' is created by dividing the column 'normalized_weight' by 10^18 from the 'weighted_pool_v2_factory' table. The SELECT statement retrieves data from this new table along with additional columns: 'gnosis' as blockchain, r.pool_id, w.token_address, and w.normalized_weight. It performs a left join between normalized_weights and registered tables based on matching pool IDs (with some byte array manipulation) and filters out rows where pool_id is null.

MODEL: balancer_optimism_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' was added to the SELECT statement. The rest of the logic remains unchanged.

MODEL: balancer_v2_optimism_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of pool liquidity estimates for a specific blockchain (in this case, 'optimism'). The model retrieves data from various sources such as token prices, pool balances, swaps changes, and managed changes. It then calculates cumulative balance amounts and converts them to USD values based on token prices. Finally, it estimates the liquidity of each pool by summing up the USD values of its tokens weighted by their normalized weights.

MODEL: balancer_v2_optimism_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a table called 'normalized_weights' is created by dividing the column 'normalized_weight' by 10^18 from the 'weighted_pool_v2_factory' table. The SELECT statement retrieves data from this new table along with additional columns: 'optimism' as blockchain, r.pool_id, w.token_address, and w.normalized_weight. It performs a left join between normalized_weights and registered tables based on matching pool IDs to retrieve records where pool_id is not null.

MODEL: balancer_polygon_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' was added to the SELECT statement. The rest of the query remains unchanged.

MODEL: balancer_v2_polygon_liquidity.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of liquidity estimates for Balancer pools on the Polygon blockchain. It retrieves data from various sources such as pool labels, prices, dex prices, bpt prices, swaps changes, balance changes, managed changes and calculates cumulative balances and USD liquidity values. The final result includes information about the day, pool ID and addresss (substring), pool symbol/version/blockchain/token address/symbol/balance/liquidity in USD for each token within a pool.

MODEL: balancer_v2_polygon_pools_tokens_weights.sql

🟠 Modified by:

🔧 PR: #4960, Balancer Liquidity Update
🧙 Author: @viniabussafi on 2023-12-22
📝 Summary: In the given SQL model, a new column 'blockchain' is added to the SELECT statement. The value of this column is set as 'polygon'. Additionally, a normalization calculation for the 'normalized_weight' column has been removed. The model now selects data from the table 'normalized_weights', performs a left join with table 'registered', and filters out rows where pool_id is null.

SECTOR: lido

toggle to see all model updates

MODEL: lido_liquidity.sql

🟠 Modified by:

🔧 PR: #5009, lido.liquidity adding new pools
🧙 Author: @ppclunghe on 2023-12-22
📝 Summary: A reference to the 'lido_liquidity_ethereum_solidly_pools' table was added to the SQL model.

MODEL: lido_liquidity_ethereum_balancer_pools.sql

🟠 Modified by:

🔧 PR: #5009, lido.liquidity adding new pools
🧙 Author: @ppclunghe on 2023-12-22
📝 Summary: In this SQL model, the following changes were made: - Two new rows were added to the 'values' section.

• A new CTE (Common Table Expression) called 'wusdm_rate' was added. It calculates the average rate by dividing a value by a power of 10 and groups it by date.
• The existing CTE 'tokens_prices_daily' was modified. A UNION ALL clause was added to include additional data from the 'wusdm_rate' CTE. Overall, these changes involve adding new rows and modifying existing logic related to calculating rates and token prices based on certain conditions.

MODEL: lido_liquidity_ethereum_solidly_pools.sql

🟢 Added by:

🔧 PR: #5009, lido.liquidity adding new pools
🧙 Author: @ppclunghe on 2023-12-22
📝 Summary: [changes too large] The model lido_liquidity_ethereum_solidly_pools.sql was added.

SECTOR: _sector

toggle to see all model updates

MODEL: mirror_base_rewards.sql

🟢 Added by:

🔧 PR: #5024, Add Mirror on OP and Base to the referral.rewards spell
🧙 Author: @datadanne on 2023-12-22
📝 Summary: This SQL model creates a table called 'mirror_referral_rewards' that enables data analysts to track and analyze referral rewards distributed in the blockchain. The data source for this analysis is the 'WritingEditions_evt_RewardsDistributed' table from the 'mirror_base' database.

MODEL: mirror_optimism_rewards.sql

🟢 Added by:

🔧 PR: #5024, Add Mirror on OP and Base to the referral.rewards spell
🧙 Author: @datadanne on 2023-12-22
📝 Summary: This SQL model creates a table called 'mirror_referral_rewards' that enables data analysts to track and analyze referral rewards distributed in the Optimism blockchain for writing editions on Mirror.

MODEL: referral_staging_rewards.sql

🟠 Modified by:

🔧 PR: #5024, Add Mirror on OP and Base to the referral.rewards spell
🧙 Author: @datadanne on 2023-12-22
📝 Summary: In this SQL model, the reference to 'mintfun_zora_rewards' was added, while references to 'mirror_optimism_rewards' and 'mirror_base_rewards' were also added. The SELECT statement remains unchanged.

MODEL: tokens_arbitrum_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='arbitrum', transfers_base = ref('tokens_arbitrum_base_transfers'), and native_symbol = 'ETH'. The removed logic includes a simple SELECT statement that selects all columns from the table referenced by 'tokens_transfers' where the column 'blockchain' is equal to 'arbitrum'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The logic that was added in this diff is a SQL query that selects all columns from the 'tokens_transfers' table where the blockchain is 'arbitrum'. The previous logic, which was removed, involved calling a function called 'transfers_enrich' with specific parameters related to the blockchain and native symbol.

MODEL: tokens_avalanche_c_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that a SQL query was removed, which selected all columns from the 'tokens_transfers' table where the blockchain is 'avalanche_c'. Instead, it was replaced with a macro call to 'transfers_enrich', passing in parameters for blockchain, transfers_base (referencing another table), and native_symbol.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that the transfers_enrich macro was removed and replaced with a simple SQL query. The new query selects all columns from the tokens_transfers table where the blockchain is 'avalanche_c'.

MODEL: tokens_base_transfers.sql

🟢 Added by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: This SQL model creates and enables data analysts to enrich transfer data from the base blockchain by adding information about native symbol 'ETH'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: This SQL model creates a union of multiple base transfer tables for different blockchains. It combines the data from these tables into one consolidated table, allowing data analysts to easily query and analyze transfers across various blockchains. The resulting table includes information such as unique keys, blockchain names, block dates and times, transaction hashes, token standards, sender and receiver addresses, contract addresses,and transfer amounts.

MODEL: tokens_bnb_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='bnb', transfers_base = ref('tokens_bnb_base_transfers'), and native_symbol = 'BNB'. This will enrich the data from the 'tokens_transfers' table by joining it with additional information based on these parameters. The removed logic includes a simple select statement that retrieves all columns from 'tokens_transfers' where blockchain is equal to 'bnb'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The logic that was added in this diff is a SQL query that selects all columns from the 'tokens_transfers' table where the blockchain is 'bnb'. The previous logic, which was removed, involved calling a macro called 'transfers_enrich' with specific parameters.

MODEL: tokens_celo_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='celo', transfers_base = ref('tokens_celo_base_transfers'), and native_symbol = 'CELO'. The removed logic includes a SELECT statement that selects all columns from the table referenced by 'tokens_transfers' where the blockchain is equal to 'celo'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic retrieves all rows from the 'tokens_transfers' table where the blockchain is 'celo'. The removed logic was a call to a macro named 'transfers_enrich' with specific parameters related to the Celo blockchain.

MODEL: tokens_ethereum_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters specifying the blockchain as 'ethereum', the base transfers table as tokens_ethereum_base_transfers, and native symbol as 'ETH'. The removed logic includes a simple SELECT statement that selects all columns from the tokens_transfers table where blockchain is 'ethereum'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that the transfers_enrich macro was removed and replaced with a simple SQL query. The new query selects all columns from the tokens_transfers table where the blockchain is 'ethereum'.

MODEL: tokens_fantom_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='fantom', transfers_base = ref('tokens_fantom_base_transfers'), and native_symbol = 'FTM'. The removed logic includes a SELECT statement that selects all columns from the table referenced by 'tokens_transfers' where the blockchain is equal to 'fantom'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic retrieves all columns from the 'tokens_transfers' table where the blockchain is 'fantom'. The removed logic was a call to a macro called 'transfers_enrich' with specific parameters for the blockchain, transfers_base, and native_symbol.

MODEL: tokens_gnosis_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='gnosis', transfers_base = ref('tokens_gnosis_base_transfers'), and native_symbol = 'xDAI'. The removed logic includes a SELECT statement that selects all columns from the table referenced by 'tokens_transfers' where the blockchain is equal to 'gnosis'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that the transfers_enrich macro with specific parameters for blockchain, transfers_base, and native_symbol was removed. Instead, a simple SELECT statement was added to retrieve all columns from the tokens_transfers table where the blockchain is 'gnosis'.

MODEL: tokens_optimism_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='optimism', transfers_base = ref('tokens_optimism_base_transfers'), and native_symbol = 'ETH'. The removed logic includes a SELECT statement that retrieves all columns from the table referenced by 'tokens_transfers' where the blockchain is equal to 'optimism'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic retrieves all rows from the 'tokens_transfers' table where the blockchain is set to 'optimism'. The removed logic involved a call to a macro called 'transfers_enrich' with specific parameters related to the 'optimism' blockchain and native symbol.

MODEL: tokens_polygon_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='polygon', transfers_base = ref('tokens_polygon_base_transfers'), and native_symbol = 'MATIC'. The removed logic includes a SELECT statement that selects all columns from the table referenced by 'tokens_transfers' where the blockchain is equal to 'polygon'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that the transfers_enrich macro with specific parameters for blockchain, transfers_base, and native_symbol was removed. Instead, a simple SELECT statement was added to retrieve all columns from the tokens_transfers table where the blockchain is 'polygon'.

MODEL: tokens_zksync_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that a SQL query was removed, which selected all columns from the 'tokens_transfers' table where the blockchain is 'zksync'. In its place, a macro called 'transfers_enrich' was added with parameters for blockchain ('zksync'), transfers_base (referencing another table), and native_symbol ('ETH').

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The diff shows that the transfers_enrich macro with specific parameters for blockchain, transfers_base, and native_symbol was removed. Instead, a SELECT statement was added to retrieve all columns from the tokens_transfers table where the blockchain is 'zksync'.

MODEL: tokens_zora_transfers.sql

🟠 Modified by:

🔧 PR: #5025, Revert 'initiate cross chain transfers (#5025)'
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The added logic includes a call to the transfers_enrich macro with parameters blockchain='zora', transfers_base = ref('tokens_zora_base_transfers'), and native_symbol = 'ETH'. The removed logic includes a SELECT statement that selects all columns from the table referenced by 'tokens_transfers' where the blockchain is equal to 'zora'.

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The logic that was added in this diff is a SQL query that selects all columns from the 'tokens_transfers' table where the blockchain is 'zora'. The previous logic, which was removed, involved calling a function called 'transfers_enrich' with specific parameters.

MODEL: tokens_base_transfers_view.sql

🟢 Added by:

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: This SQL model creates a view that selects all columns from the 'tokens_transfers' table where the blockchain is set to 'base'. It enables data analysts to easily access and analyze transfer data specific to the base blockchain.

MODEL: tokens_transfers.sql

🟢 Added by:

🔧 PR: #5025, initiate cross chain transfers
🧙 Author: @jeff-dude on 2023-12-19
📝 Summary: The transfers_enrich model is created to enrich the base transfers data with additional information from other models. It takes in the tokens_base_transfers, tokens_erc20, prices, and evms_info models as inputs. This model enables data analysts to analyze transfer data with enriched details such as token information, prices in USD, and EVMs info.

MODEL: dex_bnb_base_trades.sql

🟠 Modified by:

🔧 PR: #4922, Add wombat to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-19
📝 Summary: The model added a reference to a table called 'wombat_bnb_base_trades' in addition to the existing references to 'biswap_v3_bnb_base_trades', 'babyswap_bnb_base_trades', and 'mdex_bnb_base_trades'.

🔧 PR: #4922, Add mdex to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-18
📝 Summary: In this diff, a reference to the 'mdex_bnb_base_trades' table was added. This table is now included in the base_union common table expression (CTE).

🔧 PR: #4922, Add fraxswap to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-15
📝 Summary: In this diff, a reference to the 'fraxswap_bnb_base_trades' table was added to the SQL model.

MODEL: wombat_bnb_base_trades.sql

🟢 Added by:

🔧 PR: #5015, Add wombat to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-19
📝 Summary: This SQL model creates a table that combines swap event data from multiple tables in the 'wombat_bnb' database. It includes information such as block number, block time, token amounts bought and sold, token addresses, transaction hash, taker and maker addresses. This model enables data analysts to analyze swap activity on the Wombat project's Binance Smart Chain blockchain.

MODEL: dex_ethereum_base_trades.sql

🟠 Modified by:

🔧 PR: #4922, Add verse_dex to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-19
📝 Summary: In this diff, a reference to the model 'verse_dex_ethereum_base_trades' was added. This reference is included in a list of other models that are being referenced.

🔧 PR: #4922, Add fraxswap to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-15
📝 Summary: In this diff, a reference to the model 'fraxswap_ethereum_base_trades' was added.

MODEL: verse_dex_ethereum_base_trades.sql

🟢