search

duneanalytics / spellbook

SQL views for Dune
Other
1.11k stars 1.02k forks source link

[SPELL DESIGN] Create a New Sector for `dex_solana.bot_trades` #5629

Closed Hosuke closed 1 month ago

Hosuke commented 3 months ago

Description

With the ongoing bot trades contributions to the Spellbook, there's an emerging need to better organize bot trades on Solana. These developments underscore the importance of structured and efficient data handling for bot activities on the Solana blockchain. This issue aims to establish a new sector within Spellbook, specifically targeting dex_solana.bot_trades. cc. @whalehunting

Potential Tasks

Related PRs

bot trades PR:

Next Steps

Feedback and suggestions are crucial for the success of this initiative. Please share your thoughts, propose enhancements, or express your interest in contributing to this sector in the comments below.

Hosuke commented 3 months ago

Suggestion: using the alias bot_trades instead of trades.

whalehunting commented 3 months ago

hey! thanks @Hosuke for drafting this issue, sorry for late response!

Establishing a new sector (e.g. bot_trades) is a very good idea, also discussed this with @jeff-dude and @Hosuke previously. I do want to make it a cross-chain spellbook though, also including EVM chains. Essentially all bots/trades that are included in this dune dashboard should be added over time:

https://dune.com/whale_hunter/dex-trading-bot-wars

I'm starting with Solana bots, because querying the solana.account_activity table is very expensive and the growing amount of data is regularly breaking both the dex-trading-bot-wars dashboard and dashboards of the individual bots (can be found in the Volume Leaderboard table.

jeff-dude commented 3 months ago

hey! thanks @Hosuke for drafting this issue, sorry for late response!

Establishing a new sector (e.g. bot_trades) is a very good idea, also discussed this with @jeff-dude and @Hosuke previously. I do want to make it a cross-chain spellbook though, also including EVM chains. Essentially all bots/trades that are included in this dune dashboard should be added over time:

https://dune.com/whale_hunter/dex-trading-bot-wars

I'm starting with Solana bots, because querying the solana.account_activity table is very expensive and the growing amount of data is regularly breaking both the dex-trading-bot-wars dashboard and dashboards of the individual bots (can be found in the Volume Leaderboard table.

we're in the early stages of cross-chain spells containing both EVM and solana, where some major sector spells do and others do not -- i.e. dex has dex.trades and dex_solana.trades, while prices and nft includes both. of course with the address formats differing, that causes some strict upfront design choices of how to store and query downstream.

edit: with that said, we're open to exploring as long as performance allows it 🤝

whalehunting commented 3 months ago

hey! thanks @Hosuke for drafting this issue, sorry for late response! Establishing a new sector (e.g. bot_trades) is a very good idea, also discussed this with @jeff-dude and @Hosuke previously. I do want to make it a cross-chain spellbook though, also including EVM chains. Essentially all bots/trades that are included in this dune dashboard should be added over time: https://dune.com/whale_hunter/dex-trading-bot-wars I'm starting with Solana bots, because querying the solana.account_activity table is very expensive and the growing amount of data is regularly breaking both the dex-trading-bot-wars dashboard and dashboards of the individual bots (can be found in the Volume Leaderboard table.

we're in the early stages of cross-chain spells containing both EVM and solana, where some major sector spells do and others do not -- i.e. dex has dex.trades and dex_solana.trades, while prices and nft includes both. of course with the address formats differing, that causes some strict upfront design choices of how to store and query downstream.

edit: with that said, we're open to exploring as long as performance allows it 🤝

So what i'm doing in my query tables is to cast all columns of type VARBINARY to VARCHAR, which solves the different address formatting:

Another difference is:

I would propose to add outer_instruction_index, inner_instruction to the EVM specific queries and give them a value of NULL, since these columns are relevant for the ordering multi-hop/multi-route swaps, and may be needed for further analyzing the raw trades

whalehunting commented 3 months ago

Also added some more PR's which you can add to the issue description @Hosuke :

Hosuke commented 3 months ago

Assigning @whalehunting as requested.

jeff-dude commented 3 months ago

So what i'm doing in my query tables is to cast all columns of type VARBINARY to VARCHAR, which solves the different address formatting:

  • user
  • token_bought_address
  • token_sold_address
  • project_contract_address
  • tx_hash

Another difference is:

  • EVM chains only have a tx_hash + event_index column
  • Solana has a tx_id, tx_index, outer_instruction_index, inner_instruction index

I would propose to add outer_instruction_index, inner_instruction to the EVM specific queries and give them a value of NULL, since these columns are relevant for the ordering multi-hop/multi-route swaps, and may be needed for further analyzing the raw trades

there are performance considerations with casting EVM addresses to varchar upfront. we may want to consider multiple fields per chain (evm vs. solana). we can look into how it looks to store the fields separately, then coalesce() in queries downstream which build the dashboards. we could even create final views in spellbook which do that for us, so queries remain as simple as possible.

it'll take a bit of back and forth to test out best performance setup, but we'll make it work.

for now, let's focus on getting a solid foundation with the solana bots

jeff-dude commented 3 months ago

let's start with #5633

jeff-dude commented 3 months ago

heads up, to make it a bit easier, i'm going to finalize / merge open PRs as-is, then follow up with a PR to add all of them to dex_solana.bot_trades right after

whalehunting commented 3 months ago

hey guys, thanks for the reviews and merges! result looks very good so far, have started to integrate the new spellbooks into my dashboards 🔥 regarding dex_solana.bot_trades, i saw that the PR from @Hosuke is already merged, can i help in any way with this? otherwise i would start building the EVM bot trades spellbooks!

jeff-dude commented 3 months ago

hey guys, thanks for the reviews and merges! result looks very good so far, have started to integrate the new spellbooks into my dashboards 🔥 regarding dex_solana.bot_trades, i saw that the PR from @Hosuke is already merged, can i help in any way with this? otherwise i would start building the EVM bot trades spellbooks!

i think you're all set to try out adding an EVM spell. for simplicity, please start with just one PR on one EVM example

whalehunting commented 3 months ago

sure, i'll pick one and make it pretty 😄

I just tested dex_solana.bot_trades in a dune query, this stuff is super cool! we should probably add a bot column that allows wizards to identify which project the trade belongs to

Something else: I developed a improved methodology for calculating "real" volume (especially for multi-route trades). Could this be something that also belongs in this/a spellbook, or should this rather be a downstream query? you can see an example here: https://dune.com/queries/3372734

this would essentially be a replacement for the is_last_trade_in_transaction column, which only works for single-route + multi-hop trades, but not for multi-route + multi-hop (Jupiter does a lot of these on Solana)

jeff-dude commented 3 months ago

we should probably add a bot column that allows wizards to identify which project the trade belongs to

yes, for sure, didn't realize we weren't adding that. it'll be similar to dex/nft to let you know which project each row comes from

I developed a improved methodology for calculating "real" volume (especially for multi-route trades). Could this be something that also belongs in this/a spellbook, or should this rather be a downstream query? you can see an example here: https://dune.com/queries/3372734

this would essentially be a replacement for the is_last_trade_in_transaction column, which only works for single-route + multi-hop trades, but not for multi-route + multi-hop (Jupiter does a lot of these on Solana)

would you want to see this output in dex_solana.bot_trades directly? or would you rather keep that spell as-is, then build a new spell on top of it to handle this logic?

i notice your example is on trojan_solana.bot_trades, but i imagine same applies to all of dex_solana.bot_trades

whalehunting commented 3 months ago

would you want to see this output in dex_solana.bot_trades directly? or would you rather keep that spell as-is, then build a new spell on top of it to handle this logic?

* if first option, then maybe we convert existing `dex_solana.bot_trades` to a staging table, rename to `dex_solana.base_bot_trades`, then build final spell on top which includes this logic and outputs the `dex_solana.bot_trades`

* if second option, keep current spells as-is, introduce new final one

i guess leaving the current spellbook as-is + building a new version on top makes sense, i'm not 100% sure this logic correct, would appreciate some feedback on it (edge cases that come to my mind are e.g. tax coin sells, that usually frontrun users in the same tx on EVM)

i notice your example is on trojan_solana.bot_trades, but i imagine same applies to all of dex_solana.bot_trades

exactly, would apply to all solana bots

jeff-dude commented 3 months ago

i guess leaving the current spellbook as-is + building a new version on top makes sense, i'm not 100% sure this logic correct, would appreciate some feedback on it (edge cases that come to my mind are e.g. tax coin sells, that usually frontrun users in the same tx on EVM)

i'd recommend trying to build one on top and experimenting first. leave out the post-hook to expose it on data explorer until you're comfortable. tbh, not sure i'll be much help on testing the logic. i'd recommend building and sharing with other analysts for feedback and iterate from there

jeff-dude commented 1 month ago

calling this one done, thanks for the work here 🚀