T-Stream Signal Test Specification

Objective

Calculate 14 T-Stream Transaction Fingerprint signals for a specific token across 20 time intervals.

---

Test Parameters

Parameter	Value
Token Mint	DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump
Creation Slot	381957447
Test Range	1500 slots from creation
End Slot	381958947
Interval Size	75 slots
Number of Intervals	20
Database	solana_staging_dev
Table	pumpfun_trades

Excluded Wallets

The following 4 wallets must be excluded from ALL calculations (non-bot wallets):

6WarevyxtaMdKHAhUDWdKar63G7sAcxj34gG4Z4nzuAT
54BzBQ2eBNm7JToQJSeLWjYv4RQggVkXcXEtPSyR8EZu
7H73ZXC8SqShEUXvYT51sLf8z7Cj85g5wXsUkza6coWg
G1u7a3yymwhSAz2PPSzktDbGyHWprn7BwbYfoanYRFQM

---

Interval Definition

Each interval represents absolute data within that specific slot range.

Formula:

• start_slot = creation_slot + ((interval_num - 1) × 75)
• end_slot = creation_slot + (interval_num × 75)

Where creation_slot = 381957447 and interval_num ranges from 1 to 20.

---

Signals to Calculate (14 Total)

Group 1: Dust & Buy/Sell Patterns (3 signals)

Signal ID	Name	Description	Formula
T3	Dust Transaction Count	Count of transactions with amount < 0.005 SOL	count(tx WHERE sol_amount < 5000000)
T4	Dust Transaction Percentage	Ratio of dust transactions	T3 / total_transactions
T5	Buy Volume Ratio	Buy proportion of total volume	sum(buy_sol_amount) / (sum(buy_sol_amount) + sum(sell_sol_amount))

Notes:

• Dust threshold: 0.005 SOL = 5,000,000 lamports
• sol_amount column is stored as string, convert to numeric
• is_buy = 1 indicates buy, is_buy = 0 indicates sell

---

Group 2: Timing Fingerprinting (5 signals)

Signal ID	Name	Description	Formula
T13d	Mean Transactions Per Slot	Average transactions per active slot	total_tx / unique_slots
T13e	Gap Coefficient of Variation	Normalized consistency measure	stddev(tx_per_slot) / mean(tx_per_slot)
T13f	Burst Block Count	Slots with high activity	count(slots WHERE tx_count >= 5)
T13g	Max Transactions Per Block	Peak transactions in single slot	max(tx_count per slot)
T13h	Max Transactions Per 10 Blocks	Peak transactions in 10-slot window	max(sum of tx_count over 10 consecutive slots)

Notes:

• Group transactions by slot column first
• T13h uses a sliding window of 10 consecutive slots (by slot order, not slot number)

---

Group 3: Wallet Behavior Patterns (6 signals)

Signal ID	Name	Description	Formula
T23	Blocks to First 10 Transactions	Slots from creation to 10th transaction	slot_of_10th_tx - 381957447
T24	Amount Coefficient of Variation	Transaction amount consistency	stddev(sol_amount) / mean(sol_amount)
T25	Repeat Buyer Percentage	Wallets with buy-sell-buy pattern	wallets_with_pattern / total_wallets
T26	Transactions Per Wallet	Average transactions per wallet	total_transactions / unique_wallets
T27	Sell Transaction Percentage	Ratio of sell transactions	count(sells) / total_transactions
T28	Unique Wallet Count	Distinct wallet addresses	count(DISTINCT user)

Notes:

• T23: Find the slot where the 10th transaction occurs (from token creation)
• T25: A wallet has "repeat buyer pattern" if it has at least one buy AND at least one sell AND total transactions >= 3
• T27: Count where is_buy = 0

---

Signal Normalization

All raw signal values must be normalized to a 0.0 to 1.0 range using the formulas below:

Signal ID	Field Name	Method	Normalization Formula
T3	t3_dust_transaction_count	Log + Scale	log10(value + 1) / 3.0
T4	t4_dust_transaction_percentage	Direct	Already 0.0 to 1.0 (no transformation needed)
T5	t5_buy_volume_ratio	Direct	Already 0.0 to 1.0 (no transformation needed)
T13d	t13d_mean_transaction_gap	Log + Cap	min(log10(value + 1) / 3.0, 1.0)
T13e	t13e_gap_coefficient_of_variation	Ratio + Cap	min(value / 2.0, 1.0)
T13f	t13f_burst_block_count	Log + Cap	min(log10(value + 1) / 2.0, 1.0)
T13g	t13g_max_transactions_per_block	Log + Cap	min(log10(max(value, 1)) / 2.0, 1.0)
T13h	t13h_max_transactions_per_10_blocks	Log + Cap	min(log10(max(value, 1)) / 2.5, 1.0)
T23	t23_blocks_to_first_10_transactions	Log + Scale	log10(value + 1) / 3.0
T24	t24_amount_coefficient_of_variation	Cap	min(value / 2.0, 1.0)
T25	t25_repeat_buyer_percentage	Direct	Already 0.0 to 1.0 (no transformation needed)
T26	t26_transactions_per_wallet	Log + Scale	log10(value) / 1.5
T27	t27_sell_transaction_percentage	Direct	Already 0.0 to 1.0 (no transformation needed)
T28	t28_unique_wallet_count	Log + Scale	log10(value + 1) / 3.0

Normalization Examples:

Signal	Raw Value	Calculation	Normalized Value
T3	50	log10(51) / 3.0	0.5690
T4	0.15	0.15 (direct)	0.1500
T5	0.909	0.909 (direct)	0.9090
T13f	8	min(log10(9) / 2.0, 1.0)	0.4771
T13g	15	min(log10(15) / 2.0, 1.0)	0.5880
T13h	30	min(log10(30) / 2.5, 1.0)	0.5908
T23	30 blocks	log10(31) / 3.0	0.4969
T26	3.0	log10(3.0) / 1.5	0.3181
T28	30	log10(31) / 3.0	0.4969

Important Notes:

• All normalized values must be capped at 1.0 (values > 1.0 should be set to 1.0)
• All normalized values must be floored at 0.0 (negative values should be set to 0.0)
• For T26, if value is 0 or negative, set normalized value to 0.0 (log of 0 is undefined)
• Round final normalized values to 4 decimal places

---

Required Output Format

Output: Excel or JSON

Create an Excel file (.xlsx) or JSON file (.json) with the following structure:

Column	Description	Example
mint	Token mint address	DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump
signal_name	Signal identifier	T3, T4, T5, T13d, etc.
slot_group	Interval sequence number (1-20)	1, 2, 3, ... 20
signal_value	Calculated value	0.5086

Expected Row Count: 14 signals × 20 intervals = 280 rows

Sample Output Structure:

mint	signal_name	slot_group	signal_value
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t3_dust_transaction_count	1
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t3_dust_transaction_count	2
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t3_dust_transaction_count	3
...	...	...	...
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t3_dust_transaction_count	20
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t4_dust_transaction_percentage	1
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t4_dust_transaction_percentage	2
...	...	...	...
DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump	t28_unique_wallet_count	20

Signal Names (field names for Excel):

Signal ID	Field Name
T3	t3_dust_transaction_count
T4	t4_dust_transaction_percentage
T5	t5_buy_volume_ratio
T13d	t13d_mean_transaction_gap
T13e	t13e_gap_coefficient_of_variation
T13f	t13f_burst_block_count
T13g	t13g_max_transactions_per_block
T13h	t13h_max_transactions_per_10_blocks
T23	t23_blocks_to_first_10_transactions
T24	t24_amount_coefficient_of_variation
T25	t25_repeat_buyer_percentage
T26	t26_transactions_per_wallet
T27	t27_sell_transaction_percentage
T28	t28_unique_wallet_count

Notes:

• T23 is calculated once for the entire range (slot of 10th tx - creation slot), repeat the same value across all 20 slot_groups
• Round all signal_value to 4 decimal places
• Save as .xlsx or .json format

JSON Format Example:

[
  {"mint": "DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump", "signal_name": "t3_dust_transaction_count", "slot_group": 1, "signal_value": 0.1234},
  {"mint": "DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump", "signal_name": "t3_dust_transaction_count", "slot_group": 2, "signal_value": 0.2345},
  ...
]

---

How To Start

Your goal is to use pumpfun_trades.sql from https://filebin.net/524mfas00avsvovj/pumpfun_trades.7z and load it into a database of your choice like sqlite through a python script before you start analysing.

Example Query Structure:

SELECT ...
FROM pumpfun_trades
WHERE mint = 'DXDrSgx1iLduGRrj5LrxYB72HmjQgYWK5Q2mdtm3pump'
  AND slot >= [start_slot]
  AND slot < [end_slot]
  AND user NOT IN (
    '6WarevyxtaMdKHAhUDWdKar63G7sAcxj34gG4Z4nzuAT',
    '54BzBQ2eBNm7JToQJSeLWjYv4RQggVkXcXEtPSyR8EZu',
    '7H73ZXC8SqShEUXvYT51sLf8z7Cj85g5wXsUkza6coWg',
    'G1u7a3yymwhSAz2PPSzktDbGyHWprn7BwbYfoanYRFQM'
  )

---

Table Schema Reference

Key columns in pumpfun_trades:

Column	Type	Description
mint	String	Token mint address
slot	UInt64	Solana slot number
user	String	Wallet address
is_buy	UInt8	1 = buy, 0 = sell
sol_amount	String	Amount in lamports (convert to numeric)
signature	String	Transaction signature

---

Deliverables

1. Excel file (.xlsx) or JSON file (.json) with 280 rows/records in the specified format (mint, signal_name, slot_group, signal_value)
2. SQL queries used for each signal calculation
3. Brief notes on any observations or anomalies found in the data

---

Evaluation Criteria

• Correct application of excluded wallet filter
• Accurate interval calculations (absolute values per slot range)
• Proper handling of string-to-numeric conversion for sol_amount
• Correct implementation of T13h sliding window
• Correct identification of repeat buyer pattern for T25
• Correct application of normalization formulas for each signal
• All normalized values capped between 0.0 and 1.0
• All values rounded to 4 decimal places

---

Test Specification Version: 1.0 Date: 2026-01-07