jackguyOvertime Markets Profitable Traders
Updated 2022-11-02
99
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
›
⌄
with vol_in as (
SELECT
to_address,
count(DISTINCT tx_hash) as bets,
sum(raw_amount/power(10, decimals)) as volume_in
FROM optimism.core.fact_token_transfers
LEFT outer JOIN optimism.core.dim_contracts
ON address = contract_address
--WHERE tx_hash LIKE lower('0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865')
WHERE from_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')
AND symbol LIKE 'sUSD'
--AND block_timestamp > CURRENT_DATE - 14
GROUP BY 1
), vol_out as (
SELECT
from_address,
sum(raw_amount/power(10, decimals)) as volume_out
FROM optimism.core.fact_token_transfers
LEFT outer JOIN optimism.core.dim_contracts
ON address = contract_address
--WHERE tx_hash LIKE lower('0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865')
WHERE to_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')
AND symbol LIKE 'sUSD'
--AND block_timestamp > CURRENT_DATE - 14
GROUP BY 1
)
SELECT
from_address,
bets,
case WHEN volume_out is NULL THEN -1 * volume_in ELSE volume_out - volume_in END as gain_loss
FROM vol_in
LEFT OUTER join vol_out
on to_address = from_address
HAVING NOT gain_loss is NULL
AND NOT from_address IS NULL
Run a query to Download Data