📰 2023-12-29: Weekly Prophet!

[andrewhong5297]

This is your weekly summary of 4 PRs merged from 4 wizards. Great job everyone! 🎉
We had 0 added models 🟢 and 37 modified models 🟠 for 5 Sectors.

Sector	🔧 # PRs	🟢 # Added	🟠 # Modified	🧙 # Authors
dex	1	0	1	1
tokens	1	0	1	1
_sector	1	0	1	1
chainlink	1	0	30	1
oneinch	1	0	4	1

SECTOR: dex

toggle to see all model updates

MODEL: dex_info.sql

🟠 Modified by:

🔧 PR: #5048, Revert adding ImmortalX to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-28
📝 Summary: In the given SQL model, a new row has been added to the temporary table. The row represents a project called 'WardenSwap' with corresponding values for name, marketplace_type, and x_username.

SECTOR: tokens

toggle to see all model updates

MODEL: tokens_avalanche_c_erc20.sql

🟠 Modified by:

🔧 PR: #5060, Add FLD to tokens_avalanche_c_erc20.sql
🧙 Author: @IAmAGithubSir on 2023-12-28
📝 Summary: The token symbols that were added or removed are: ANKR, gmdUSDC, PHAR, FLD

SECTOR: _sector

toggle to see all model updates

MODEL: dex_celo_base_trades.sql

🟠 Modified by:

🔧 PR: #5048, Revert adding ImmortalX to dex.trades_beta
🧙 Author: @tomfutago on 2023-12-28
📝 Summary: The model added a reference to the 'carbonhood_celo_base_trades' table and removed references to the 'immortalx_celo_base_trades' table.

SECTOR: chainlink

toggle to see all model updates

MODEL: chainlink_arbitrum_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic that was added in this SQL model is the creation of a CTE called 'ccip_fulfilled_transactions'. This CTE selects data from the 'chainlink_arbitrum_ccip_send_traces' table and filters it based on certain conditions. It then joins this data with another table called 'arbitrum_usd' using a left join. The final SELECT statement retrieves specific columns from the CTE and renames some of them before returning the result set.

MODEL: chainlink_arbitrum_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In the given diff, the previous logic for calculating daily requests in the ccip_request_daily_meta CTE has been replaced with a new logic using subqueries. The ethereum_agg CTE calculates the count of fulfilled and reverted transactions for each date_start value. Then, in the ccip_request_daily CTE, these counts are aggregated by month and totalled to get daily request statistics. Finally, there is a SELECT statement that retrieves data from this final CTE including blockchain and date_start columns.

MODEL: chainlink_arbitrum_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is to retrieve data from the chainlink_arbitrum_ccip_send_traces table and join it with the arbitrum_usd table. The query selects specific columns from these tables, including block_time, date_start, node_address, tx_hash, and trace_address. It also includes conditions such as filtering for unsuccessful transactions (tx.tx_success = false) and applying incremental filtering based on the value of the variable 'incremental_interval'.

MODEL: chainlink_arbitrum_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table in the 'arbitrum' source and performs a left join with the 'chainlink_ccip_network_meta' reference on matching values of chain_selector. The existing column name 'destination' is retained, while an unused column named 'blockchain as destination' was removed.

MODEL: chainlink_avalanche_c_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the logic that was added includes:

• A new CTE called 'ccip_fulfilled_transactions' which selects specific columns from the 'chainlink_avalanche_c_ccip_send_traces' table and applies some transformations.
• The WHERE clause filters out rows where 'tx_success' is false.
• The SELECT statement now includes the column 'date_start'.
• The column names for token_amount and usd_amount were removed.

MODEL: chainlink_avalanche_c_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The logic added in this SQL model is to create a CTE called 'ethereum_agg' that calculates the count of fulfilled and reverted requests for each date_start value from two different tables. It also includes conditional statements based on whether it is incremental or not. Another CTE called 'ccip_request_daily' selects specific columns from the 'ethereum_agg' CTE and assigns values to them. The final SELECT statement retrieves data from the 'ccip_request_daily' CTE along with additional columns.

MODEL: chainlink_avalanche_c_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is to retrieve information about reverted transactions in the Avalanche blockchain. It selects the transaction hash, block time, start date, node address, and trace address for each reverted transaction. The model also includes a CTE (Common Table Expression) called ccip_reverted_transactions that joins with other tables to gather additional data such as token amount and USD amount associated with each transaction. The result is then returned as a table containing relevant information about the reverted transactions in Avalanche blockchain.

MODEL: chainlink_avalanche_c_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table and performs a left join with the 'chainlink_ccip_network_meta' table based on matching values in the 'chain_selector' columns. The existing column name 'blockchain' was aliased as 'destination'.

MODEL: chainlink_base_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the 'base_usd' CTE has been removed. The 'ccip_fulfilled_transactions' CTE has been modified to include additional columns and a WHERE clause to filter for successful transactions. The final SELECT statement includes the blockchain, block_time, date_start, node_address, tx_hash, and trace_address columns from the 'ccip_fulfilled_transactions' table.

MODEL: chainlink_base_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is the creation of a CTE called 'ethereum_agg' which calculates the count of fulfilled and reverted requests for each date_start value. This is done by querying the 'chainlink_base_ccip_fulfilled_transactions' and 'chainlink_base_ccip_reverted_transactions' tables based on the date_start value. The result of this calculation is then used to populate another CTE called 'ccip_request_daily', which includes additional columns like blockchain, date_month, and total_requests. Finally, a SELECT statement retrieves data from ccip_request_daily including blockchain and date_start columns.

MODEL: chainlink_base_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the base_usd CTE has been removed. The logic for joining with base_usd and calculating the token_amount and usd_amount has also been removed. Instead, a new CTE called ccip_reverted_transactions has been added to select data from the table 'chainlink_base_ccip_send_traces'. The columns selected include 'tx_hash', 'block_time', 'date_start', 'node_address' and 'trace_address'. Finally, in the main query, some column names have changed but overall it selects data from the newly added CTE.

MODEL: chainlink_base_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table and performs a left join with the 'chainlink_ccip_network_meta' table based on matching values in the 'chain_selector' column. The existing column name 'destination' is retained, while an unused column named 'blockchain as destination' was removed.

MODEL: chainlink_bnb_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the logic that was added includes:

• A CTE called 'ccip_fulfilled_transactions' which selects specific columns from the 'chainlink_bnb_ccip_send_traces' table and applies some transformations.
• The main SELECT statement retrieves data from the 'ccip_fulfilled_transactions' CTE and renames some columns.

MODEL: chainlink_bnb_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The logic that was added in this SQL model is as follows:

• A new CTE called 'ethereum_agg' was created, which calculates the count of fulfilled and reverted requests for each date_start value from the chainlink_bnb_ccip_fulfilled_transactions and chainlink_bnb_ccip_reverted_transactions tables.
• Another CTE called 'ccip_request_daily' was created, which selects relevant columns from the ethereum_agg CTE and adds a column for total_requests.
• The final SELECT statement retrieves data from ccip_request_daily.

MODEL: chainlink_bnb_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is to retrieve data from the chainlink_bnb_ccip_send_traces table and join it with the bnb_usd table. The query selects specific columns such as tx_hash, block_time, and calculates values like token_amount. It also includes conditions based on whether it is an incremental run or not. Finally, it returns a result set with columns like 'blockchain', 'date_start', 'node_address', etc., from the joined tables.

MODEL: chainlink_bnb_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table and performs a left join with the 'chainlink_ccip_network_meta' table based on matching values in the 'chain_selector' column. The existing column name 'destination' is retained, while an unused column named 'blockchain as destination' was removed.

MODEL: chainlink_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic of the changes in this SQL model is that the column 'date_month' was removed and replaced with a new column called 'date_start'. Additionally, two columns named 'token_amount' and 'usd_amount' were removed, while a new column called 'trace_address' was added. The rest of the code remains unchanged.

MODEL: chainlink_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic of the changes in this SQL model is that the column 'date_month' was removed and replaced with a new column called 'date_start'. Additionally, three columns ('token_amount', 'usd_amount', and 'trace_address') were removed, while two new columns ('trace_address' and 'tx_hash') were added. The UNION ALL statement remains unchanged.

MODEL: chainlink_ethereum_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is to select data from the ccip_send_traces table and join it with the ethereum_usd table (which was removed). The selected columns include transaction hash, block time, start date, node address, and trace address. The query also includes conditions for filtering based on successful transactions and incremental intervals. Finally, the result is returned with additional columns such as blockchain name (ethereum) and trace address.

MODEL: chainlink_ethereum_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is the creation of a CTE called 'ethereum_agg' which calculates the count of fulfilled and reverted requests for each date_start value. This is done by using subqueries to count records from two different tables based on matching date_start values. The results are then used to populate another CTE called 'ccip_request_daily' which includes additional columns such as blockchain, date_month, and total_requests. Finally, the SELECT statement retrieves data from ccip_request_daily including blockchain and date_start columns.

MODEL: chainlink_ethereum_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the ethereum_usd CTE has been removed. The ccip_reverted_transactions CTE now includes the block_time, which is cast as a date and assigned to the variable date_start. The columns for token amount and USD amount have been removed from this CTE. Additionally, a new column called trace_address has been added to capture data from the table referenced by 'chainlink_ethereum_ccip_send_traces'. Finally, in the main query, instead of using date_month, it now uses date_start.

MODEL: chainlink_ethereum_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table in the 'ethereum' source and performs a left join with the 'chainlink_ccip_network_meta' reference on matching values of chain_selector. The existing column name 'destination' is retained, while an unused column named 'blockchain as destination' was removed.

MODEL: chainlink_optimism_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the 'optimism_usd' CTE has been removed. The 'ccip_fulfilled_transactions' CTE has been modified to include additional columns and a WHERE clause to filter for successful transactions. The final SELECT statement includes the blockchain name, block time, start date of the transaction, node address, transaction hash and trace address from the 'ccip_fulfilled_transactions' table.

MODEL: chainlink_optimism_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is the creation of a CTE called 'ethereum_agg' which calculates the count of fulfilled and reverted requests for each date_start value from two different tables. This CTE is then used to create another CTE called 'ccip_request_daily' which includes additional columns like blockchain, date_month, and total_requests. The final SELECT statement retrieves data from the 'ccip_request_daily' table along with some other columns.

MODEL: chainlink_optimism_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is to retrieve data from the chainlink_optimism_ccip_send_traces table and join it with the optimism_usd table based on certain conditions. The selected columns include blockchain, block_time, date_start, node_address, tx_hash, and trace_address. The query filters for transactions where tx_success is false and optionally filters by incremental_interval if specified.

MODEL: chainlink_optimism_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table in the 'optimism' source and performs a left join with the 'chainlink_ccip_network_meta' reference on matching values of chain_selector. The existing column name 'destination' is retained, while an unused column named 'blockchain as destination' was removed.

MODEL: chainlink_polygon_ccip_fulfilled_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic that was added in this SQL model is a new CTE called 'ccip_fulfilled_transactions'. It selects the transaction hash, block time, start date, node address, and trace address from the 'chainlink_polygon_ccip_send_traces' table. The WHERE clause filters for successful transactions. The final SELECT statement retrieves the blockchain name (set as 'polygon'), block time, start date, node address and trace address from the ccip_fulfilled_transactions CTE.

MODEL: chainlink_polygon_ccip_request_daily.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: The main logic added in this SQL model is the creation of a CTE called 'ethereum_agg' which calculates the count of fulfilled and reverted requests for each date_start value. This is done by querying the 'chainlink_polygon_ccip_fulfilled_transactions' and 'chainlink_polygon_ccip_reverted_transactions' tables. The results are then used to populate another CTE called 'ccip_request_daily', which includes additional columns like blockchain, date_month, and total_requests. Finally, a SELECT statement retrieves data from the ccip_request_daily table with columns blockchain and date_start.

MODEL: chainlink_polygon_ccip_reverted_transactions.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, the 'polygon_usd' CTE is removed. The 'ccip_reverted_transactions' CTE now includes the block_time as date_start and trace_address columns. The WHERE clause in ccip_reverted_transactions is simplified to only include transactions with tx_success = false. The final SELECT statement includes blockchain, block_time, date_start, node_address, tx_hash, and trace_address columns from ccip_reverted_transactions table.

MODEL: chainlink_polygon_ccip_send_traces.sql

🟠 Modified by:

🔧 PR: #5034, update ccip requests
🧙 Author: @AnonJon on 2023-12-22
📝 Summary: In this SQL model, a new column called 'trace_address' was added to the SELECT statement. It retrieves data from the 'traces' table and performs a left join with the 'chainlink_ccip_network_meta' table based on matching values in the 'chain_selector' column. The existing column name 'destination' is retained, while an unused column named 'blockchain as destination' was removed.

SECTOR: oneinch

toggle to see all model updates

MODEL: oneinch_blockchains.sql

🟠 Modified by:

🔧 PR: #5028, 1inch: lineage refactoring part 3
🧙 Author: @grkhr on 2023-12-22
📝 Summary: The added logic in this SQL model is a loop that iterates over a list of exposed blockchains. For each blockchain, it calls the oneinch_blockchain_macro macro passing the blockchain as an argument. The result of each macro call is then unioned together using union all.

MODEL: oneinch_calls.sql

🟠 Modified by:

🔧 PR: #5028, 1inch: lineage refactoring part 3
🧙 Author: @grkhr on 2023-12-22
📝 Summary: The main logic added in this diff is a loop that iterates over the oneinch_exposed_blockchains_list() and for each blockchain, it executes the oneinch_calls_macro(blockchain) SQL macro. The results of each iteration are combined using a union all operation.

MODEL: oneinch_fusion_settlements.sql

🟠 Modified by:

🔧 PR: #5028, 1inch: lineage refactoring part 3
🧙 Author: @grkhr on 2023-12-22
📝 Summary: A new SQL model called 'settlements' was added. It has two columns: contract_address and blockchain. One row of data is inserted into the model with a contract address value of 0x7F069df72b7A39bCE9806e3AfaF579E54D8CF2b9 and a blockchain value of 'base'. The SELECT statement at the end retrieves all rows from the settlements table.

MODEL: oneinch_swaps.sql

🟠 Modified by:

🔧 PR: #5028, 1inch: lineage refactoring part 3
🧙 Author: @grkhr on 2023-12-22
📝 Summary: The main logic added in this SQL model is the calculation of various amounts and values related to calls made in a blockchain. It includes joining different tables to get information about tokens, prices, and transfers. The model calculates amounts in native tokens as well as their equivalent values in USD. It also calculates the total number of transfers and distinct tokens involved in those transfers. Additionally, it includes columns for user-specific amount calculations based on whether they are sending or receiving tokens.