c - Stats by indexToken

flyingfishc - Stats by indexToken

Updated 2024-01-29

-- forked from b - Stats by Wallet @ https://flipsidecrypto.xyz/edit/queries/1259b517-e7fe-498b-9753-a15858791d0d

-- forked from a Base Query For Pairs Traded @ https://flipsidecrypto.xyz/edit/queries/2cf751c5-e596-48a6-b857-db02672f8fc5

with raw_data AS (

SELECT

block_timestamp

, origin_from_address AS wallet

, decoded_log:indexToken AS index_token

, name

, symbol

, decoded_log:amountIn / pow(10, 6) AS pos_size

, decoded_log:sizeDelta / pow(10, 30) AS open_size

, open_size / pos_size AS leverage

, decoded_log:isLong::boolean AS is_long

, decoded_log:executionFee / pow(10, 18) AS execution_fee

, tx_hash

FROM arbitrum.core.ez_decoded_event_logs

LEFT JOIN arbitrum.core.dim_contracts on index_token = address

-- WHERE block_timestamp::date > current_date - 2

WHERE block_timestamp > '2023-08-30'

AND origin_to_address = '0x75f688604a58c720e7e4496139765498a2563c78' -- Pear Contract Address

AND event_name = 'CreateIncreasePosition'

AND tx_status = 'SUCCESS'

ORDER BY event_index

)

SELECT

name

, symbol

, count(DISTINCT wallet) AS unique_users

, count(DISTINCT tx_hash) AS pear_trades

, sum(iff(is_long, 1, 0)) AS long_pos

, sum(iff(is_long, 0, 1)) AS short_pos

, sum(iff(is_long, pos_size, 0)) AS long_collateral

, sum(iff(is_long, 0, pos_size)) AS short_collateral

, sum(pos_size) AS total_collateral

Last run: over 1 year agoAuto-refreshes every 24 hours

	NAME	SYMBOL	UNIQUE_USERS	PEAR_TRADES	LONG_POS	SHORT_POS	LONG_COLLATERAL	SHORT_COLLATERAL	TOTAL_COLLATERAL	AVG(LEVERAGE)
1	Wrapped BTC	WBTC	79	299	123	176	1428.239914	2047.090804	3475.330718	974.27288688
2	Wrapped Ether	WETH	84	281	188	93	2170.90619	1055.166109	3226.072299	169.547663124
3	ChainLink Token	LINK	46	109	53	56	611.261963	651.39064	1262.652603	16.980287977
4	Uniswap	UNI	33	75	17	58	194.719466	671.47998	866.199446	18.975447643

337B