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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
import { formatters } from '../utils';
export const dataFetchingQueries: any = {
get_missing_txn_hashes: `
SELECT
a.txn_hash
FROM
events_raw a
WHERE NOT EXISTS
(
SELECT
*
FROM
transactions b
WHERE
b.txn_hash = a.txn_hash
)
AND
a.block_number >= $1
AND
a.block_number < $2`,
get_used_block_numbers: `
SELECT DISTINCT
a.block_number
FROM
events_raw a
WHERE NOT EXISTS
(
SELECT
*
FROM
blocks b
WHERE
b.block_number = a.block_number
)
AND
a.block_number >= $1
AND
a.block_number < $2`,
get_token_registry: `
SELECT
*
FROM
tokens`,
get_max_block: `
SELECT
MAX(block_number)
FROM
events_raw`,
get_relayers: `
SELECT
*
FROM
relayers`,
get_most_recent_pricing_date: `
SELECT
MAX(DATE(timestamp))
FROM
prices
`,
get_top_unknown_token_addresses: `
SELECT a.token_address as address, a.txn_value / 2 as total_txn_value
FROM
(SELECT token_address, SUM(txn_value) as txn_value
FROM
(select a.timestamp, a.maker_token as token_address, (CASE WHEN a.taker_txn_usd_value > a.maker_txn_usd_value OR a.maker_txn_usd_value IS NULL
THEN a.taker_txn_usd_value
ELSE a.maker_txn_usd_value END) as txn_value
from events_full a
where a.event_type = 'LogFill'
and a.timestamp > (NOW() + INTERVAL '-24 hours')
union
select a.timestamp, a.taker_token as token_address, (CASE WHEN a.taker_txn_usd_value > a.maker_txn_usd_value OR a.maker_txn_usd_value IS NULL
THEN a.taker_txn_usd_value
ELSE a.maker_txn_usd_value END) as txn_value
from events_full a
where a.event_type = 'LogFill'
and a.timestamp > (NOW() + INTERVAL '-24 hours')) token_txn_values
WHERE token_address IS NOT NULL
AND txn_value > 0
GROUP BY 1
ORDER BY 2 DESC) a
LEFT JOIN tokens b
ON a.token_address = b.address
WHERE symbol is NULL
ORDER BY 2 DESC
`,
};
|
