-
Notifications
You must be signed in to change notification settings - Fork 1.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Pika Protocol v4 added for optimism #7427
Merged
jeff-dude
merged 24 commits into
duneanalytics:main
from
Lampros-Tech:pika-protocol-v4-optimism
Jan 29, 2025
+223
−5
Merged
Changes from all commits
Commits
Show all changes
24 commits
Select commit
Hold shift + click to select a range
4aa160b
Pika Protocol v4 added for optimism
PatelPrinci bfbcd20
Merge branch 'main' into pika-protocol-v4-optimism
PatelPrinci 2a2f5a9
changed post_hook format and replaced the incremental_predicate for i…
PatelPrinci 5baece0
Applied the new post_hook in the config and changed the incremental_p…
PatelPrinci 5733c18
Seed File Data added for V4
PatelPrinci 53390ef
Fixed valid DATE literal issue
PatelPrinci aef0807
Fixing evt block time time column issue
PatelPrinci 93150e6
adding previous version of incremental filter
PatelPrinci f5acf5b
adding previous version of incremental filter
PatelPrinci 758fb23
Merge branch 'main' into pika-protocol-v4-optimism
PatelPrinci d4f8a83
nothing changed
PatelPrinci 6fee002
Merge branch 'main' into pika-protocol-v4-optimism
PatelPrinci cd8eab6
added incremental predicate
PatelPrinci 7908520
added incremental filters
PatelPrinci 9f7007c
removed unwanted character
PatelPrinci 7377220
added incremental filter in last
PatelPrinci d5e6af4
Merge branch 'main' into pika-protocol-v4-optimism
PatelPrinci 6169aec
fix space indentication error
PatelPrinci 39a9598
fix space indentication error
PatelPrinci 1c59300
fix product id issue
PatelPrinci 79f5b12
added cast to else part of product id case
PatelPrinci a9aa419
changed unique keys to simplify
PatelPrinci e73ce23
changed the contributors name
PatelPrinci 215893c
Merge branch 'main' into pika-protocol-v4-optimism
PatelPrinci File filter
Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
There are no files selected for viewing
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
166 changes: 166 additions & 0 deletions
166
...ook/models/_sector/perpetual/projects/pika/optimism/pika_v4_optimism_perpetual_trades.sql
Hosuke marked this conversation as resolved.
Show resolved
Hide resolved
|
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Original file line number | Diff line number | Diff line change |
|---|---|---|
| @@ -0,0 +1,166 @@ | ||
| {{ config( | ||
| schema = 'pika_v4_optimism', | ||
| alias = 'perpetual_trades', | ||
| partition_by = ['block_month'], | ||
| materialized = 'incremental', | ||
| file_format = 'delta', | ||
| incremental_strategy = 'merge', | ||
| unique_key = ['tx_hash', 'evt_index'], | ||
| post_hook='{{ expose_spells(blockchains = \'["optimism"]\', | ||
| spell_type = "project", | ||
| spell_name = "pika", | ||
| contributors = \'["principatel"]\') }}' | ||
| )}} | ||
|
|
||
| {% set project_start_date = '2021-11-22' %} | ||
|
|
||
| WITH positions AS ( | ||
| SELECT | ||
| positionId | ||
| ,user AS user | ||
| ,productId | ||
| ,CAST(isLong AS VARCHAR) AS isLong | ||
| ,price | ||
| ,oraclePrice | ||
| ,margin | ||
| ,leverage | ||
| ,fee | ||
| ,contract_address | ||
| ,evt_tx_hash | ||
| ,evt_index | ||
| ,evt_block_time | ||
| ,evt_block_number | ||
| ,'4' AS version | ||
| FROM {{ source('pika_protocol_v4_optimism', 'PikaPerpV4_evt_NewPosition') }} | ||
| {% if is_incremental() %} | ||
| WHERE {{incremental_predicate('evt_block_time')}} | ||
| {% endif %} | ||
|
|
||
| UNION ALL | ||
| --closing positions | ||
| SELECT | ||
| positionId | ||
| ,user | ||
| ,productId | ||
| ,'close' AS action | ||
| ,price | ||
| ,entryPrice | ||
| ,margin | ||
| ,leverage | ||
| ,fee | ||
| ,contract_address | ||
| ,evt_tx_hash | ||
| ,evt_index | ||
| ,evt_block_time | ||
| ,evt_block_number | ||
| ,'4' AS version | ||
| FROM {{ source('pika_protocol_v4_optimism', 'PikaPerpV4_evt_ClosePosition') }} | ||
| {% if is_incremental() %} | ||
| WHERE {{incremental_predicate('evt_block_time')}} | ||
| {% endif %} | ||
| ), | ||
|
|
||
| perps AS ( | ||
| SELECT | ||
| evt_block_time AS block_time | ||
| ,evt_block_number AS block_number | ||
|
|
||
| ,CASE | ||
| WHEN productId = UINT256 '1' OR productId = UINT256 '16' THEN 'ETH' | ||
| WHEN productId = UINT256 '2' OR productId = UINT256 '17' THEN 'BTC' | ||
| WHEN productId = UINT256 '3' OR productId = UINT256 '18' THEN 'LINK' | ||
| WHEN productId = UINT256 '4' OR productId = UINT256 '19' THEN 'SNX' | ||
| WHEN productId = UINT256 '5' OR productId = UINT256 '20' THEN 'SOL' | ||
| WHEN productId = UINT256 '6' OR productId = UINT256 '21' THEN 'AVAX' | ||
| WHEN productId = UINT256 '7' OR productId = UINT256 '22' THEN 'MATIC' | ||
| WHEN productId = UINT256 '8' THEN 'LUNA' | ||
| WHEN productId = UINT256 '9' OR productId = UINT256 '23' THEN 'AAVE' | ||
| WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE' | ||
| WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS' | ||
| WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI' | ||
| ELSE 'product_id_' || CAST(productId AS VARCHAR) | ||
| END AS virtual_asset | ||
| ,CASE | ||
| WHEN productId = UINT256 '1' OR productId = UINT256 '16' THEN 'ETH' | ||
| WHEN productId = UINT256 '2' OR productId = UINT256 '17' THEN 'BTC' | ||
| WHEN productId = UINT256 '3' OR productId = UINT256 '18' THEN 'LINK' | ||
| WHEN productId = UINT256 '4' OR productId = UINT256 '19' THEN 'SNX' | ||
| WHEN productId = UINT256 '5' OR productId = UINT256 '20' THEN 'SOL' | ||
| WHEN productId = UINT256 '6' OR productId = UINT256 '21' THEN 'AVAX' | ||
| WHEN productId = UINT256 '7' OR productId = UINT256 '22' THEN 'MATIC' | ||
| WHEN productId = UINT256 '8' THEN 'LUNA' | ||
| WHEN productId = UINT256 '9' OR productId = UINT256 '23' THEN 'AAVE' | ||
| WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE' | ||
| WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS' | ||
| WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI' | ||
| ELSE 'product_id_' || CAST(productId AS VARCHAR) | ||
| END AS underlying_asset | ||
| ,CASE | ||
| WHEN productId = UINT256 '1' OR productId = UINT256 '16' THEN 'ETH' | ||
| WHEN productId = UINT256 '2' OR productId = UINT256 '17' THEN 'BTC' | ||
| WHEN productId = UINT256 '3' OR productId = UINT256 '18' THEN 'LINK' | ||
| WHEN productId = UINT256 '4' OR productId = UINT256 '19' THEN 'SNX' | ||
| WHEN productId = UINT256 '5' OR productId = UINT256 '20' THEN 'SOL' | ||
| WHEN productId = UINT256 '6' OR productId = UINT256 '21' THEN 'AVAX' | ||
| WHEN productId = UINT256 '7' OR productId = UINT256 '22' THEN 'MATIC' | ||
| WHEN productId = UINT256 '8' THEN 'LUNA-USD' | ||
| WHEN productId = UINT256 '9' OR productId = UINT256 '23' THEN 'AAVE' | ||
| WHEN productId = UINT256 '10' OR productId = UINT256 '24' THEN 'APE' | ||
| WHEN productId = UINT256 '11' OR productId = UINT256 '25' THEN 'AXS' | ||
| WHEN productId = UINT256 '12' OR productId = UINT256 '26' THEN 'UNI' | ||
| ELSE 'product_id_' || CAST(productId AS VARCHAR) | ||
| END AS market | ||
|
|
||
| ,contract_address AS market_address | ||
| ,(margin/1e8) * (leverage/1e8) AS volume_usd | ||
| ,fee/1e8 AS fee_usd | ||
| ,margin/1e8 AS margin_usd | ||
|
|
||
| ,CASE | ||
| WHEN isLong = 'true' THEN 'long' | ||
| WHEN isLong = 'false' THEN 'short' | ||
| ELSE CAST(isLong as VARCHAR) | ||
| END AS trade | ||
|
|
||
| ,'Pika' AS project | ||
| ,version | ||
| ,'Pika' AS frontend | ||
| ,user AS trader | ||
| ,margin * leverage AS volume_raw | ||
| ,evt_tx_hash AS tx_hash | ||
| ,evt_index | ||
| FROM positions | ||
| ) | ||
|
|
||
| SELECT | ||
| 'optimism' AS blockchain | ||
| ,CAST(date_trunc('DAY', perps.block_time) AS date) AS block_date | ||
| ,CAST(date_trunc('MONTH', perps.block_time) AS date) AS block_month | ||
| ,perps.block_time | ||
| ,perps.virtual_asset | ||
| ,perps.underlying_asset | ||
| ,perps.market | ||
| ,perps.market_address | ||
| ,perps.volume_usd | ||
| ,perps.fee_usd | ||
| ,perps.margin_usd | ||
| ,perps.trade | ||
| ,perps.project | ||
| ,perps.version | ||
| ,perps.frontend | ||
| ,perps.trader | ||
| ,CAST(perps.volume_raw as UINT256) as volume_raw | ||
| ,perps.tx_hash | ||
| ,tx."from" AS tx_from | ||
| ,tx."to" AS tx_to | ||
| ,perps.evt_index | ||
| FROM perps | ||
| INNER JOIN {{ source('optimism', 'transactions') }} AS tx | ||
| ON perps.tx_hash = tx.hash | ||
| AND perps.block_number = tx.block_number | ||
| {% if not is_incremental() %} | ||
| AND tx.block_time >= DATE '{{project_start_date}}' | ||
| {% endif %} | ||
| {% if is_incremental() %} | ||
| AND {{incremental_predicate('tx.block_time')}} | ||
| {% endif %} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Oops, something went wrong.
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others. Learn more.
To match the simplified
unique_key: