duneanalytics · jeff-dude · Jun 3, 2024 · May 23, 2024 · May 23, 2024 · May 24, 2024
diff --git a/models/_sector/nft/trades/chains/old/nft_old_base_trades.sql b/models/_sector/nft/trades/chains/old/nft_old_base_trades.sql
@@ -8,11 +8,9 @@
 
 -- while we refactor more marketplace models, they should be removed here and added to the chain specific base_trades unions.
 {% set nft_models = [
-ref('magiceden_solana_events')
-,ref('nftearth_optimism_events')
+ref('nftearth_optimism_events')
 ,ref('mooar_polygon_events')
 ,ref('oneplanet_polygon_events')
-,ref('opensea_solana_events')
 ,ref('quix_seaport_optimism_events')
 ] %}
 

diff --git a/models/_sector/nft/trades/chains/solana/nft_solana_base_trades.sql b/models/_sector/nft/trades/chains/solana/nft_solana_base_trades.sql
@@ -0,0 +1,41 @@
+{{ config(
+    schema = 'nft_solana',
+    alias = 'base_trades',
+    materialized = 'view'
+    )
+}}
+
+-- transformative view to get the solana trades into nft.trades as much as possible
+-- columns are filled on a best effort basis, but many will probably hold null values..
+-- the main goal is to get transaction count and overall volume correct
+-- any values cast to varbinary will be wrongly encoded but will still hold equality and uniqueness constraints
+
+
+SELECT
+    blockchain,
+    project,
+    version as project_version,
+    block_date,
+    block_month,
+    block_time,
+    block_slot as block_number,
+    cast(tx_id as varbinary) as tx_hash,
+    cast(project_program_id as varbinary) as project_contract_address,
+    trade_category,
+    trade_type,
+    cast(buyer as varbinary) as buyer,
+    cast(seller as varbinary) as seller,
+    cast(null as varbinary) as nft_contract_address,
+    cast(null as uint256) as nft_token_id,
+    coalesce(try(cast(number_of_items as uint256)), uint256 '0') as nft_amount,
+    coalesce(try(cast(amount_raw as uint256)), uint256 '0') as price_raw,
+    case when currency_symbol = 'SOL' then 0x069b8857feab8184fb687f634618c035dac439dc1aeb3b5598a0f00000000001 else null end as currency_contract,
+    coalesce(try(cast(taker_fee_amount_raw + maker_fee_amount_raw as uint256)), uint256 '0') as platform_fee_amount_raw,
+    coalesce(try(cast(royalty_fee_amount_raw as uint256)), uint256 '0') as royalty_fee_amount_raw,
+    cast(null as varbinary) as platform_fee_address,
+    cast(null as varbinary) as royalty_fee_address,
+    cast(null as varbinary) as tx_from,
+    cast(null as varbinary) as tx_to,
+    cast(null as varbinary) as tx_data_marker,                                                  -- forwarc compatibility with aggregator marker matching
+    row_number() over (partition by tx_id order by leaf_id) as sub_tx_trade_id       -- intermediate fix to fill this column
+FROM {{ ref('nft_solana_trades') }}
diff --git a/models/_sector/nft/trades/chains/solana/nft_solana_old_trades.sql b/models/_sector/nft/trades/chains/solana/nft_solana_old_trades.sql
@@ -0,0 +1,70 @@
+{{ config(
+    schema = 'nft_solana',
+    alias = 'old_trades',
+    materialized = 'view'
+    )
+}}
+
+
+-- while we refactor more marketplace models, they should be removed here and added to the chain specific base_trades unions.
+{% set nft_models = [
+ref('magiceden_solana_events')
+,ref('opensea_solana_events')
+] %}
+
+
+-- we have to do some column wrangling here to convert the old schema to the new schema
+-- lots of columns will hold null values..
+SELECT * FROM  (
+{% for nft_model in nft_models %}
+    SELECT
+        blockchain,
+        project,
+        version,
+        cast(date_trunc('day', block_time) as date) as block_date,
+        cast(date_trunc('month', block_time) as date) as block_month,
+        block_time,
+        case when evt_type = 'Mint' then 'primary' else 'secondary' end as trade_type,
+        number_of_items,
+        trade_category,
+        cast(buyer as varchar) as buyer,
+        cast(seller as varchar) as seller,
+        amount_raw,
+        amount_original,
+        amount_usd,
+        currency_symbol,
+        cast(currency_contract as varchar) as currency_address,
+        cast(null as varchar) as account_merkle_tree,
+        cast(null as uint256) as leaf_id,
+        cast(null as varchar) as account_mint,
+        cast(null as varchar) as project_program_id,
+        aggregator_name,
+        cast(aggregator_address as varchar) as aggregator_address,
+        cast(tx_hash as varchar) as tx_id,
+        block_number as block_slot,
+        cast(null as varchar) as tx_signer,
+        cast(null as double) as taker_fee_amount_raw,
+        cast(null as double)  as taker_fee_amount,
+        cast(null as double)  as taker_fee_amount_usd,
+        cast(null as double)  as taker_fee_percentage,
+        cast(null as double)  as maker_fee_amount_raw,
+        cast(null as double)  as maker_fee_amount,
+        cast(null as double)  as maker_fee_amount_usd,
+        cast(null as double)  as maker_fee_percentage,
+        royalty_fee_amount_raw,
+        royalty_fee_amount,
+        royalty_fee_amount_usd,
+        royalty_fee_percentage,
+        cast(null as double)  as amm_fee_amount_raw,
+        cast(null as double)  as amm_fee_amount,
+        cast(null as double)  as amm_fee_amount_usd,
+        cast(null as double)  as amm_fee_percentage,
+        cast(null as varchar) as instruction,
+        cast(null as integer) as outer_instruction_index,
+        cast(null as integer) as inner_instruction_index
+    FROM {{ nft_model }}
+    {% if not loop.last %}
+    UNION ALL
+    {% endif %}
+    {% endfor %}
+)
diff --git a/models/_sector/nft/trades/chains/solana/nft_solana_trades.sql b/models/_sector/nft/trades/chains/solana/nft_solana_trades.sql
@@ -1,7 +1,7 @@
 {{
     config(
         schema = 'nft_solana'
-        
+
         , alias = 'trades'
         , materialized = 'view'
         ,post_hook='{{ expose_spells(\'["solana"]\',
@@ -17,6 +17,7 @@
     , ref('magiceden_mmm_solana_trades')
     , ref('tensorswap_v1_solana_trades')
     , ref('tensorswap_v2_solana_trades')
+    , ref('nft_solana_old_trades')
 ] %}
 
 
@@ -71,4 +72,4 @@ FROM {{ marketplace }}
 {% if not loop.last %}
 UNION ALL
 {% endif %}
-{% endfor %}
+{% endfor %}
diff --git a/models/_sector/nft/trades/chains/solana/nft_solana_trades_schema.yml b/models/_sector/nft/trades/chains/solana/nft_solana_trades_schema.yml
@@ -23,7 +23,7 @@ models:
      - &block_time
        name: block_time
        description: "UTC event block time"
-     - &block_date 
+     - &block_date
        name: block_date
      - &block_month
        name: block_month
@@ -135,4 +135,18 @@ models:
        description: "outer index of instruction call"
      - &inner_instruction_index
        name: inner_instruction_index
-       description: "inner index of instruction call"
+       description: "inner index of instruction call"
+
+  - name: nft_solana_base_trades
+    meta:
+      blockchain: solana
+      sector: nft
+      contributors: [ 0xRob ]
+    config:
+      tags: [ 'solana','nft' ]
+    description: >
+      nft solana trades with the schema of nft_trades, mainly used to include volume and tx counts into nft.trades
+    tests:
+      - check_columns_nft_base_trades
+      - dbt_utils.unique_combination_of_columns:
+          combination_of_columns: [ 'block_number','tx_hash','sub_tx_trade_id' ]
diff --git a/models/_sector/nft/trades/chains/solana/old/_schema.yml b/models/_sector/nft/trades/chains/solana/old/_schema.yml
@@ -0,0 +1,29 @@
+version: 2
+
+models:
+  - name: magiceden_solana_events
+    meta:
+      blockchain: solana
+      project: magiceden
+      contributors: soispoke
+    config:
+      tags: ['magiceden','solana','events']
+    description: >
+        Magic Eden events on Solana
+    tests:
+      - check_columns_nft_old_events
+      - dbt_utils.unique_combination_of_columns:
+          combination_of_columns:
+            - unique_trade_id
+
+  - name: opensea_solana_events
+    meta:
+      blockchain: solana
+      project: opensea
+      contributors: rchen8, soispoke
+    config:
+      tags: [ 'solana','opensea','events' ]
+    description: >
+      OpenSea events on Solana
+    tests:
+      - check_columns_nft_old_events
diff --git a/models/_sector/nft/trades/chains/solana/old/magiceden_solana_schema.yml b/models/_sector/nft/trades/chains/solana/old/magiceden_solana_schema.yml
diff --git a/models/_sector/nft/trades/chains/solana/old/opensea_solana_schema.yml b/models/_sector/nft/trades/chains/solana/old/opensea_solana_schema.yml