adriaparcerisasFlipside's World Cup of Bounties Side Quest: Pool Props: Decentralized Sports Exchange 4
Updated 2022-11-30
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
--Let’s create a parameterized dashboard(based on wallet address) to help players understand their activity on pool props:
--How many pools they’ve traded in
--how many positions they’ve taken
--How much they’ve won or lost, in USD and native currency
--At the top of your dash create a top 10 all-time leaderboard show both the biggest winners and losers, how many they’ve taken and how much they’ve won or lost.
WITH mx AS (
SELECT DATEADD('hours', -7, MAX(block_timestamp)) AS recency
FROM solana.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
), b0 AS (
SELECT tx_id
, ARRAY_SIZE(account_keys) AS ak_sz
, ARRAY_SIZE(instructions[0]:accounts) AS in_ac_sz
, instructions[0]:accounts AS in_ac
, signers[0]::string AS address
, date_trunc('hour', DATEADD('hours', -7, block_timestamp)) AS hour
, DATEADD('hours', -7, block_timestamp)::date AS date
, ARRAY_SIZE(inner_instructions[0]:instructions) AS sz
, inner_instructions[0]:instructions AS i
, i[0]:parsed:info AS i0
, i[1]:parsed:info AS i1
, i[2]:parsed:info AS i2
, i[3]:parsed:info AS i3
, i[4]:parsed:info AS i4
, i[5]:parsed:info AS i5
, i[6]:parsed:info AS i6
, i[0]:parsed:type::string AS type0
, i[1]:parsed:type::string AS type1
, i[2]:parsed:type::string AS type2
, i[3]:parsed:type::string AS type3
, COALESCE(i0:authority, i0:source)::string AS address0
, i0:mint::string AS mint0
, i1:mint::string AS mint1
, i2:mint::string AS mint2
Run a query to Download Data