# Jupiter Assignment — Nati
---
## Overview
Two problem statements from Kishen at Jupiter, both using on-chain Solana data via Dune Analytics.
**P01** — DEX Aggregator Market Share: measure Jupiter's market share against competitors, identify meaningful fluctuations.
**P02** — Rewards Hub Cohort Analysis: measure the impact of Jupiter's first Rewards Hub campaign on claimant swap behavior.
Platform: Dune Analytics (Trino engine). All queries run against public on-chain data — no internal access required.
---
# P01: DEX Aggregator Market Share
## The Question
Market share analysis between DEX aggregators on Solana. Are there meaningful fluctuations attributable to given reasons?
## Table Choice: `dex_solana.trades` over individual aggregator tables
I'm using `dex_solana.trades` as the single source of truth instead of querying each aggregator's decoded table separately (e.g. `jupiter_v6_solana.jupiter_evt_swapevent`, `dflow_solana.swap_orchestrator_evt_swapevent`).
Reasons:
1. **Unified schema.** One table, same columns, same USD pricing methodology across all aggregators. No need to reconcile different column names, decimal handling, or pricing approaches.
2. **Coverage.** Several competitors (OKX, Titan, Axiom, GMGN) don't have pre-built Dune tables. `dex_solana.trades` already parses them via `trade_source`.
3. **Apples to apples.** If I pull Jupiter from one table and OKX from raw instructions, any difference could be methodology (indexing/parsing methodology), not market share.
## The Routing Problem: Why My Numbers Differ From the Blogpost
The Solana Floor / Dune blogpost that Kishen referenced likely used aggregator-level swap event tables (e.g. `jupiter_v6_solana.jupiter_evt_swapevent`). My results show different market share numbers. These are possible reasons why:
**Verified: a single user transaction produces multiple rows in both tables — but more in the swap event table.**
When a user swaps SOL → USDC through Jupiter, the aggregator might route that trade through multiple AMM pools for best execution:
```
User intent: 1 trade (SOL → USDC)
Routing: SOL → RAY (via Raydium) → USDC (via Orca)
Swap events: 2 events emitted
```
To test this, I ran a verification query comparing row counts per `tx_id` across both tables for the same set of transactions (one day of Jupiter V6 activity):
```
SOURCE total_rows unique_tx_ids rows_per_tx
────────────────────────────────────────────────────────────────────
dex_solana.trades 1,282,343 941,166 1.36
jupiter_evt_swapevent 2,537,107 1,416,025 1.79
```
Two findings:
1. **The swap event table inflates more.** At 1.79 rows per transaction vs 1.36, the swap event table produces ~32% more rows per transaction than `dex_solana.trades`. This confirms that using swap event tables for market share comparison would bias results toward aggregators with more complex routing.
2. **Neither table is perfectly 1:1 with user intent.** `dex_solana.trades` at 1.36 rows per `tx_id` suggests it also records some sub-trade granularity — likely individual AMM-level fills within a single transaction, though fewer than the full routing legs captured by the swap event table.
The practical implication: `dex_solana.trades` is the better table for cross-aggregator comparison because it inflates less and applies consistent methodology across all aggregators. But for precise volume attribution, neither public table perfectly deduplicates to net user trades. This would not be an issue with internal data, where the exact mapping from user intent to on-chain execution is known.
**Impact on market share:**
|Metric|Swap event tables|`dex_solana.trades`|
|---|---|---|
|What it counts|Routing legs (~1.79/tx)|AMM fills (~1.36/tx)|
|Volume attribution|Most inflated|Less inflated|
|Cross-aggregator comparison|Biased by routing complexity|More consistent|
An aggregator that routes through more pools (better execution, more splitting) would appear to have higher volume in the swap event table even if the user traded the same dollar amount. This penalizes simpler routers and inflates complex ones.
**This likely explains the discrepancy with the blogpost.** If the blogpost used swap event tables, Jupiter's market share would appear different than when measured via `dex_solana.trades`.
## Aggregators Tracked
Identified via `trade_source` column in `dex_solana.trades`:
|Aggregator|Program Address(es)|Notes|
|---|---|---|
|Jupiter v6|`JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4`|Main aggregator|
|Jupiter LO v1|`j1o2qRpjcyUwEvwtcfhEQefh773ZgjxcVRry7LDqg5X`|Limit orders|
|Jupiter DCA|`DCA265Vj8a9CEuX1eb1LWRnDT7uK6q1xMipnNyatn23M`|Dollar cost averaging|
|OKX|`6m2CDdhRgxpH4WjvdzxAYbGxwdGUz5MziiL5jek2kBma`, `proVF4pMXVaYqmy4NjniPh4pqKNfMmsihgd4wdkCX3u`|Two program addresses|
|DFlow|`DF1ow4tspfHX9JwWJsAb9epbkA8hmpSEAtxXy1V27QBH`||
|Titan|`T1TANpTeScyeqVzzgNViGDNrkQ6qHz9KrSBS4aNXvGT`, `TitanLozLMhczcwrioEguG2aAmiATAPXdYpBg3DbeKK`|Main + limit orders|
|Axiom|`FLASHX8DrLbgeR8FcfNV1F5krxYcYMUdBkrP1EPBtxB9`|Limited Dune resources|
|GMGN|`GMGNreQcJFufBiCTLDBgKhYEfEe9B454UjpDr5CaSLA1`||
Note: GMGN is not in Kishen's original problem statement but appears in `dex_solana.trades` as a meaningful trade source. Included for completeness.
## The Ultra Caveat
Jupiter Ultra routes trades through OKX and DFlow for best execution. On-chain, these appear as OKX/DFlow trades because the inner swap instruction is executed by their program. But the trade was initiated by Jupiter's Ultra router.
**This means OKX and DFlow volumes in `dex_solana.trades` are overstated.** A portion of their volume is actually Jupiter-originated.
To detect this we would have to check the raw `solana.transactions` table and isolate all transactions that call the `jitodontfront11111111111JustUseJupiterU1tra` account — this is very compute-heavy and not feasible on the current setup.
Solana enforces a 64-account limit per transaction, so Ultra cannot nest Jupiter's aggregator instruction around OKX/DFlow — there aren't enough account slots. Instead, Ultra appends the OKX/DFlow program as an additional account within a single instruction. Identifying these requires checking whether Jupiter's Ultra program address appears alongside OKX/DFlow program addresses in the same transaction's account list — possible but expensive.
**For this analysis:** I report OKX and DFlow volumes as-is, with this caveat documented. The "true" Jupiter market share is higher than what the numbers show, and the "true" OKX/DFlow share is lower. Quantifying the exact split would be a follow-up analysis.
## Address-Level Overlap Analysis
Inspired by Kishen's CatLumpurr presentation, I track address-level overlap between aggregators per week. This uses on-chain addresses as the unit of analysis (not resolved wallet identities):
- Jup ∩ OKX — addresses using both Jupiter and OKX in the same week
- Jup ∩ DFlow — addresses using both Jupiter and DFlow
- Jup ∩ Titan — addresses using both Jupiter and Titan
- Jup ∩ OKX ∩ Titan — three-way overlap
- Jup ∩ DFlow ∩ Titan — three-way overlap
- Jup ∩ DFlow ∩ OKX ∩ Titan — four-way overlap
This answers: are users loyal to one aggregator, or shopping around? Is overlap increasing (commoditization) or decreasing (stickiness)?
The overlap rows are tagged with `row_type = 'overlap'` so they can be filtered out when viewing pure market share charts.
## P01: Dashboard
![[p01_dash main.png]]
[View on Dune →](https://dune.com/natitaw/chain-nati)
## P01: What I'd Do With More Time
1. **Resolve Ultra attribution.** Identify transactions where Jupiter's Ultra program appears alongside OKX/DFlow in the same account list, and reclassify that volume as Jupiter-originated. This is the single biggest methodological improvement available.
2. **Blue-chip token split.** Run market share separately for tokens above a certain liquidity/volume threshold (blue-chip tokens with reliable pricing) vs long-tail tokens (less reliable USD values). If market share differs between tiers, that's a finding.
3. **Bot filtering.** Some addresses are arbitrage bots generating high transaction counts but not representing real user activity. Filtering by transactions-per-minute or known bot patterns would give a cleaner picture.
4. **Round-trip filtering.** Remove trades where input and output tokens are the same (e.g. SOL → USDC → SOL). The Jupiter frontend does not allow this behavior, so these are likely programmatic/bot activity and should be excluded for a cleaner picture of organic market share.
5. **Perps.** Jupiter Perps generates ~60% of revenue but doesn't appear in `dex_solana.trades` (spot swaps only). However, this is beyond the scope of this analysis — a perps market position analysis would require including other perps providers (Drift, etc.) and cannot be meaningfully compared against spot aggregator share.
## P01: Queries
### P01_main: Market Share + Address Overlap
```sql
WITH trades AS (
SELECT
date_trunc('week', block_date) AS week,
trader_id,
trade_source,
amount_usd
FROM dex_solana.trades
WHERE block_date >= date_add('month', -6, current_date)
AND block_date < date_trunc('week', current_date)
AND trade_source IN (
'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4',
'j1o2qRpjcyUwEvwtcfhEQefh773ZgjxcVRry7LDqg5X',
'DCA265Vj8a9CEuX1eb1LWRnDT7uK6q1xMipnNyatn23M',
'6m2CDdhRgxpH4WjvdzxAYbGxwdGUz5MziiL5jek2kBma',
'proVF4pMXVaYqmy4NjniPh4pqKNfMmsihgd4wdkCX3u',
'DF1ow4tspfHX9JwWJsAb9epbkA8hmpSEAtxXy1V27QBH',
'T1TANpTeScyeqVzzgNViGDNrkQ6qHz9KrSBS4aNXvGT',
'TitanLozLMhczcwrioEguG2aAmiATAPXdYpBg3DbeKK',
'FLASHX8DrLbgeR8FcfNV1F5krxYcYMUdBkrP1EPBtxB9',
'GMGNreQcJFufBiCTLDBgKhYEfEe9B454UjpDr5CaSLA1'
)
),
agg AS (
SELECT
week,
CASE trade_source
WHEN 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4' THEN 'Jupiter'
WHEN 'j1o2qRpjcyUwEvwtcfhEQefh773ZgjxcVRry7LDqg5X' THEN 'Jupiter'
WHEN 'DCA265Vj8a9CEuX1eb1LWRnDT7uK6q1xMipnNyatn23M' THEN 'Jupiter'
WHEN '6m2CDdhRgxpH4WjvdzxAYbGxwdGUz5MziiL5jek2kBma' THEN 'OKX'
WHEN 'proVF4pMXVaYqmy4NjniPh4pqKNfMmsihgd4wdkCX3u' THEN 'OKX'
WHEN 'DF1ow4tspfHX9JwWJsAb9epbkA8hmpSEAtxXy1V27QBH' THEN 'DFlow'
WHEN 'T1TANpTeScyeqVzzgNViGDNrkQ6qHz9KrSBS4aNXvGT' THEN 'Titan'
WHEN 'TitanLozLMhczcwrioEguG2aAmiATAPXdYpBg3DbeKK' THEN 'Titan'
WHEN 'FLASHX8DrLbgeR8FcfNV1F5krxYcYMUdBkrP1EPBtxB9' THEN 'Axiom'
WHEN 'GMGNreQcJFufBiCTLDBgKhYEfEe9B454UjpDr5CaSLA1' THEN 'GMGN'
END AS aggregator,
SUM(amount_usd) AS volume_usd,
COUNT(*) AS trade_count,
approx_distinct(trader_id) AS unique_wallets
FROM trades
GROUP BY 1, 2
),
individual AS (
SELECT
week,
aggregator,
'aggregator' AS row_type,
SUM(volume_usd) AS volume_usd,
SUM(trade_count) AS trade_count,
SUM(unique_wallets) AS unique_wallets,
CAST(SUM(trade_count) AS double) / SUM(unique_wallets) AS trades_per_wallet
FROM agg
GROUP BY 1, 2
),
wallet_flags AS (
SELECT
week,
trader_id,
MAX(CASE WHEN trade_source IN (
'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4',
'j1o2qRpjcyUwEvwtcfhEQefh773ZgjxcVRry7LDqg5X',
'DCA265Vj8a9CEuX1eb1LWRnDT7uK6q1xMipnNyatn23M'
) THEN 1 ELSE 0 END) AS jup,
MAX(CASE WHEN trade_source IN (
'6m2CDdhRgxpH4WjvdzxAYbGxwdGUz5MziiL5jek2kBma',
'proVF4pMXVaYqmy4NjniPh4pqKNfMmsihgd4wdkCX3u'
) THEN 1 ELSE 0 END) AS okx,
MAX(CASE WHEN trade_source = 'DF1ow4tspfHX9JwWJsAb9epbkA8hmpSEAtxXy1V27QBH'
THEN 1 ELSE 0 END) AS dflow,
MAX(CASE WHEN trade_source IN (
'T1TANpTeScyeqVzzgNViGDNrkQ6qHz9KrSBS4aNXvGT',
'TitanLozLMhczcwrioEguG2aAmiATAPXdYpBg3DbeKK'
) THEN 1 ELSE 0 END) AS titan
FROM trades
GROUP BY 1, 2
),
overlaps AS (
SELECT week, 'Jup ∩ OKX' AS aggregator, 'overlap' AS row_type,
NULL AS volume_usd, NULL AS trade_count, COUNT(*) AS unique_wallets, NULL AS trades_per_wallet
FROM wallet_flags WHERE jup = 1 AND okx = 1 GROUP BY 1
UNION ALL
SELECT week, 'Jup ∩ DFlow', 'overlap', NULL, NULL, COUNT(*), NULL
FROM wallet_flags WHERE jup = 1 AND dflow = 1 GROUP BY 1
UNION ALL
SELECT week, 'Jup ∩ Titan', 'overlap', NULL, NULL, COUNT(*), NULL
FROM wallet_flags WHERE jup = 1 AND titan = 1 GROUP BY 1
UNION ALL
SELECT week, 'Jup ∩ OKX ∩ Titan', 'overlap', NULL, NULL, COUNT(*), NULL
FROM wallet_flags WHERE jup = 1 AND okx = 1 AND titan = 1 GROUP BY 1
UNION ALL
SELECT week, 'Jup ∩ DFlow ∩ Titan', 'overlap', NULL, NULL, COUNT(*), NULL
FROM wallet_flags WHERE jup = 1 AND dflow = 1 AND titan = 1 GROUP BY 1
UNION ALL
SELECT week, 'Jup ∩ DFlow ∩ OKX ∩ Titan', 'overlap', NULL, NULL, COUNT(*), NULL
FROM wallet_flags WHERE jup = 1 AND dflow = 1 AND okx = 1 AND titan = 1 GROUP BY 1
)
SELECT * FROM individual
UNION ALL
SELECT * FROM overlaps
ORDER BY 1 DESC, 3, 4 DESC NULLS LAST
```
### P01_verify: Routing-Leg Verification
Compares row counts per `tx_id` across `dex_solana.trades` and `jupiter_evt_swapevent` for the same set of transactions. Confirms that the swap event table inflates ~32% more per transaction.
```sql
WITH evt_txs AS (
SELECT DISTINCT evt_tx_id AS tx_id
FROM jupiter_v6_solana.jupiter_evt_swapevent
WHERE evt_block_time >= current_date - interval '1' day
AND evt_block_time < current_date
),
evt_counts AS (
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT s.evt_tx_id) AS unique_tx_ids
FROM jupiter_v6_solana.jupiter_evt_swapevent s
INNER JOIN evt_txs t ON s.evt_tx_id = t.tx_id
WHERE s.evt_block_time >= current_date - interval '1' day
AND s.evt_block_time < current_date
),
dex_counts AS (
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT d.tx_id) AS unique_tx_ids
FROM dex_solana.trades d
INNER JOIN evt_txs t ON d.tx_id = t.tx_id
WHERE d.block_date = current_date - interval '1' day
)
SELECT
'jupiter_evt_swapevent' AS source,
e.total_rows,
e.unique_tx_ids,
CAST(e.total_rows AS double) / e.unique_tx_ids AS rows_per_tx
FROM evt_counts e
UNION ALL
SELECT
'dex_solana.trades' AS source,
d.total_rows,
d.unique_tx_ids,
CAST(d.total_rows AS double) / d.unique_tx_ids AS rows_per_tx
FROM dex_counts d
```
---
# P02: Rewards Hub Cohort Analysis
## The Question
1. What was the swap volume and activity of claimants before and during the campaign?
2. Were claimants new users or existing Jupiter users?
3. How would you conduct cohort analysis to measure campaign impact?
## Campaign Details
- **Program:** `GenieRGuCtgfDGThwjp2GLreQMFtJoG1fqFE8MF1gAzG`
- **Campaign ID:** `BwqmeRojjQwQ8qESc4JABen8NsXJA1Bgt6xA81CcaiAm`
- **Period:** Dec 9, 2025 — Jan 31, 2026
- **Claim discriminator:** `0x3ec6d6c1d59f6cd2` (first 8 bytes of instruction data)
- **Total claim events:** 126,899
- **Unique claimant wallets:** 56,522
- **Average claims per wallet:** 2.2
## Approach Evolution
### Phase 1: Wallet-Level Swap History via Dune SQL
Started with `jupiter_v6_solana.jupiter_evt_swapevent` — per-wallet swap counts across PRE/DURING/POST periods with a 90-day pre-window. This worked but was expensive: the swap event table has no `block_date` partition column, so every query scanned months of unpartitioned timestamp data. Several iterations debugging column name conventions (`tx_signer` vs `evt_tx_signer`, `block_time` vs `evt_block_time`) compounded the cost. Approximately $80 in Dune credits consumed before arriving at a stable query.
The swap event table also has no USD columns — only raw token amounts in native decimals (1 SOL = 1,000,000,000). Swap count became the primary metric, not volume.
### Phase 2: Python Pipeline via Dune API
Pivoted to pulling query results via `dune_client` Python SDK, saving CSVs locally, and running segmentation/visualization in pandas + matplotlib. This separated the expensive Dune execution (run once, cache) from the iterative analysis (run locally, free). The notebook (`02_reb.ipynb`) became the analytical workbench.
### Phase 3: Unified Table + Control Group
Switched from V6-only swap events to `jupiter_solana.aggregator_swaps` which covers all Jupiter products and includes pre-computed `input_usd`/`output_usd`. Also built a non-claimant control group for difference-in-differences analysis using `dex_solana.trades`.
**Key lesson:** On Dune, query cost is dominated by table scans. Partition columns (`block_date`) and tight date ranges are the main levers. The analytical grain (wallet vs day vs period) matters less than which table you hit and how much data the engine reads.
## The Visibility Gap
Not all 56,522 claimant wallets appear in Jupiter's aggregator swap tables:
|Segment|Count|% of Total|
|---|---|---|
|Had aggregator swaps (visible)|22,744|40.2%|
|Zero aggregator swaps (invisible)|33,778|59.8%|
The "invisible" 60% did not appear in `jupiter_solana.aggregator_swaps`. This is not Perps activity — Perps is not part of this campaign's eligibility rules. Limit Order v1 and DCA are also excluded from campaign eligibility, and Ultra swaps (routed through OKX/DFlow) do not appear in the aggregator swap table.
The most likely explanation is JupiterZ — Jupiter's off-chain request-for-quote (RFQ) system that connects traders directly with market makers for better pricing. JupiterZ trades bypass the on-chain aggregator routing entirely, so they generate claim eligibility but leave no trace in the aggregator swap tables. Other possibilities include high-volume programmatic activity routed through pathways not captured by the aggregator decoder.
A useful follow-up would be to examine how much the invisible group claimed in aggregate — if their claim amounts are disproportionately large, that would support the market-maker hypothesis.
## Claimant Segmentation
Of the 22,744 visible wallets:
|Cohort|Definition|Count|%|
|---|---|---|---|
|Dormant|Had PRE swaps, none DURING|15,586|68.5%|
|Retained|Had PRE and DURING swaps|5,305|23.3%|
|New|No PRE swaps, active DURING|1,555|6.8%|
|Invisible|No aggregator swaps at all|33,778|59.8% of total|
**68.5% of visible claimants were dormant** — they had Jupiter swap history before the campaign but showed zero activity during it. They claimed the reward but exhibited no behavioral change.
## Retained Wallets: Activity Comparison
For the 5,305 wallets active both before and during the campaign:
|Metric|PRE (90d)|DURING (54d)|
|---|---|---|
|Total swaps|6,962,410|4,087,529|
|Total USD volume|$6.01B|$1.29B|
|Daily swap rate|77,360|75,695|
|Median swaps/wallet|15|3|
|Median USD/wallet|$2,139|$338|
The daily swap rate barely changed (-2.2%), but the median dropped sharply (15 → 3 swaps). This means a few power users maintained high frequency while the majority of retained wallets reduced activity significantly. The mean hides what the median reveals.
## New User Funnel
1,555 wallets had zero PRE activity and first appeared during the campaign:
- **Median swaps during campaign:** 2
- **Median USD volume:** $200
- **Retained post-campaign:** 231 / 1,555 = **14.9%**
- **Median POST swaps (retained):** 3
- **Median POST USD (retained):** $231
14.9% post-campaign retention is low but not zero. The retained new users actually increased their median activity slightly ($200 → $231), suggesting a small but genuine cohort found value in the product. Important caveat: a second Rewards Hub campaign launched immediately after this one ended, so part of the observed post-campaign retention may be attributable to the subsequent campaign rather than lasting behavioral change from this one. Disentangling the two effects would require isolating wallets that were eligible for only one campaign.
## Difference-in-Differences: Claimants vs Market
Difference-in-Differences (DiD) is a causal inference technique that compares the change in outcomes over time between a treatment group and a control group. By measuring how much each group changed from the PRE to DURING period, DiD controls for time trends (e.g. market-wide declines) that affect both groups equally, isolating the incremental effect of the treatment (the campaign).
Built a non-claimant control group using all Jupiter V6 swap wallets that did NOT claim from Rewards Hub.
```
DAILY SWAP RATES: CLAIMANTS vs NON-CLAIMANTS
============================================================
Group PRE (/day) DURING (/day) Change
------------------------------------------------------------
Non-claimants (ctrl) 3,393,431 2,226,877 -34.4%
Claimants (treatment) 77,360 75,695 -2.2%
Difference-in-Differences: +32.2 percentage points
```
The broader market declined 34.4% in daily swap rate during the campaign period. Claimants only declined 2.2%. The campaign appears to have insulated ~32 percentage points of activity decline — claimants were significantly more resilient than the market baseline.
However, this is observational, not causal. Several factors complicate interpretation:
- **Selection bias.** Claimants self-selected into claiming — they were engaged enough to notice and act on the rewards. The DiD controls for time trends but not for this selection effect. The true campaign effect is somewhere between 0% (pure selection) and 32.2% (pure treatment).
- **Ultra swap exclusion.** The non-claimant group may include wallets whose activity is primarily through Ultra (routed via OKX/DFlow) and therefore not eligible for the campaign. These wallets would depress the control group's activity metrics without being a true comparison group.
- **Volume thresholds.** Campaign eligibility required meeting certain volume thresholds. Non-claimant wallets below these thresholds were never eligible, making them a structurally different population. A more rigorous control would match on PRE-period activity levels.
## Market Conditions Caveat
This campaign ran during a Solana bear market. SOL dropped from ~$190 in late November to ~$100 by late January, with overall DeFi activity declining across the ecosystem. This matters:
- **Declining swap counts may not mean the campaign failed.** The entire market contracted significantly. A claimant cohort that only declined 2.2% was actually outperforming.
- **USD volumes are doubly affected.** Volume = swaps × size × token price. In a falling market, all three decline simultaneously. Flat USD volume during a bear market is a bullish signal.
- **New user retention benchmarks are depressed.** Acquiring users during a downturn means their first experience is in a low-activity environment. 14.9% retention in a bear market may be better than it looks.
- **The indexed view matters more than raw numbers.** See the campaign-normalized chart — when both groups are rebased to campaign start = 100, claimant decline is visually dramatic but the control group also falls. The gap between the two lines is the signal.
## P02: Dashboard
![[p02_dune_screenshot.png]]
[View on Dune →](https://dune.com/natitaw/p02-chainnatish)
## P02: What I'd Do With More Time
1. **Investigate the invisible 60%.** These wallets are most likely JupiterZ (RFQ / market maker) activity. Verifying this would require joining against JupiterZ-specific tables or internal routing logs. Examining their aggregate claim amounts would test the market-maker hypothesis — if they claimed disproportionately more, that's consistent with high-volume RFQ activity.
2. **Claim timing analysis.** When in the campaign did each wallet first claim? Early claimants vs late claimants may have different behavioral profiles.
3. **Token analysis.** What were claimants trading? Memecoins vs stables vs majors reveals user type and intent.
4. **Longer POST window.** Two weeks of POST data is short. Re-run in March for a proper 30-day retention read. Longer-term (3-4 months post-campaign) retention would reveal whether the campaign created lasting behavioral change or only temporary engagement.
5. **Isolate per-campaign causal impact.** With multiple campaigns running in sequence, attribute behavioral changes to specific campaigns at the wallet level — which campaign actually moved the needle for wallet_i?
6. **Cross-campaign wallet behavior.** Identify wallets that claim across multiple campaigns. Are these habitual reward-seekers (low incremental value) or genuinely engaged users that campaigns successfully retain?
7. **Propensity score matching.** Instead of raw DiD with self-selected claimants vs all non-claimants, match claimant wallets to non-claimants with similar PRE activity levels. This controls for selection bias more rigorously.
## P02: Queries
### P2_02: Wallet-Level Swap History (V6)
The initial query — per wallet per period, 90-day PRE window. Expensive but produced the segmentation dataset.
```sql
WITH claimants AS (
SELECT DISTINCT tx_signer AS wallet
FROM solana.instruction_calls
WHERE executing_account = 'GenieRGuCtgfDGThwjp2GLreQMFtJoG1fqFE8MF1gAzG'
AND SUBSTR(data, 1, 8) = 0x3ec6d6c1d59f6cd2
AND block_time >= TIMESTAMP '2025-12-01'
AND block_time < TIMESTAMP '2026-02-12'
),
swaps AS (
SELECT
c.wallet,
s.evt_block_time,
s.evt_tx_id,
CASE
WHEN s.evt_block_time < TIMESTAMP '2025-12-09' THEN 'PRE'
WHEN s.evt_block_time < TIMESTAMP '2026-02-01' THEN 'DURING'
ELSE 'POST'
END AS period
FROM claimants c
INNER JOIN jupiter_v6_solana.jupiter_evt_swapevent s
ON c.wallet = s.evt_tx_signer
WHERE s.evt_block_time >= TIMESTAMP '2025-09-11'
AND s.evt_block_time < TIMESTAMP '2026-02-12'
)
SELECT
wallet,
period,
COUNT(DISTINCT evt_tx_id) AS swap_count,
MIN(evt_block_time) AS first_swap,
MAX(evt_block_time) AS last_swap
FROM swaps
GROUP BY wallet, period
ORDER BY wallet, period
```
### P2_foundations: Daily Aggregates with USD (Final Query)
The optimized query — daily grain, USD volume via `aggregator_swaps`, tighter date windows.
```sql
WITH claimants AS (
SELECT DISTINCT tx_signer AS wallet
FROM solana.instruction_calls
WHERE executing_account = 'GenieRGuCtgfDGThwjp2GLreQMFtJoG1fqFE8MF1gAzG'
AND SUBSTR(data, 1, 8) = 0x3ec6d6c1d59f6cd2
AND block_time >= TIMESTAMP '2025-12-01'
AND block_time < TIMESTAMP '2026-01-31'
),
swaps AS (
SELECT
s.tx_signer AS wallet,
date_trunc('day', s.block_time) AS day,
s.tx_id,
s.input_usd,
s.output_usd,
(s.input_usd + s.output_usd) / 2.0 AS avg_usd,
CASE
WHEN date_trunc('day', s.block_time) < date '2025-12-09' THEN 'PRE'
WHEN date_trunc('day', s.block_time) <= date '2026-01-31' THEN 'DURING'
ELSE 'POST'
END AS period
FROM jupiter_solana.aggregator_swaps s
WHERE s.tx_signer IN (SELECT wallet FROM claimants)
AND s.block_time >= TIMESTAMP '2025-11-21'
AND s.block_time < TIMESTAMP '2026-02-16'
)
SELECT
day,
period,
COUNT(DISTINCT wallet) AS active_wallets,
COUNT(DISTINCT tx_id) AS swap_count,
CAST(COUNT(DISTINCT tx_id) AS double) / COUNT(DISTINCT wallet) AS swaps_per_wallet,
SUM(input_usd) AS input_volume_usd,
SUM(output_usd) AS output_volume_usd,
SUM(avg_usd) AS avg_volume_usd,
SUM(avg_usd) / COUNT(DISTINCT wallet) AS avg_volume_per_wallet
FROM swaps
GROUP BY day, period
ORDER BY day
```