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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

pub use clickhouse_mod::*;
#[rustfmt::skip]
mod clickhouse_mod {
#[allow(dead_code)]
pub const CEX_SYMBOLS: &str = r#"SELECT DISTINCT
    s.exchange AS exchange,
    upper(replaceAll(replaceAll(replaceAll(s.pair, '/', ''), '-', ''), '_', '')) AS symbol_pair,
    (toString(p1.address), toString(p2.address)) AS address_pair
FROM cex.trading_pairs s
INNER JOIN cex.address_symbols AS p1 ON p1.symbol = s.base_asset OR p1.unwrapped_symbol = s.base_asset
INNER JOIN cex.address_symbols AS p2 ON p2.symbol = s.quote_asset OR p2.unwrapped_symbol = s.quote_asset
and not (s.exchange = 'okex' and s.trading_type = 'FUTURES')
"#;

#[allow(dead_code)]
pub const RAW_CEX_TRADES: &str = r#"SELECT
    c.exchange AS exchange,
    'Taker' AS trade_type,
    upper(replaceAll(replaceAll(replaceAll(c.symbol, '/', ''), '-', ''), '_', '')) AS symbol,
    c.timestamp AS timestamp,
    c.side AS side,
    c.price AS price,
    c.amount AS amount
FROM cex.normalized_trades AS c 
WHERE c.timestamp >= ? AND c.timestamp < ?
ORDER BY timestamp "#;

#[allow(dead_code)]
pub const MOST_VOLUME_PAIR_EXCHANGE: &str = r#"WITH ranked_symbols AS
    (
        SELECT
            month,
            upper(replaceAll(replaceAll(replaceAll(symbol, '/', ''), '-', ''), '_', '')) AS symbol,
            exchange,
            sum(sum_volume) AS total_volume
        FROM cex.trading_volume_by_month
        WHERE (month >= toStartOfMonth(toDateTime(? / 1000000) - toIntervalMonth(1))) AND (month <= toStartOfMonth(toDateTime(? / 1000000) + toIntervalMonth(1)))
        GROUP BY month, symbol, exchange
    ),
    aggregated_exchanges AS
    (
        SELECT
            symbol,
            month,
            groupArray((exchange, total_volume)) AS exchanges_volumes
        FROM ranked_symbols
        GROUP BY symbol, month
    )
SELECT
    symbol,
    arrayMap(x -> x.1, arraySort(v -> -v.2, exchanges_volumes)) AS exchanges, 
    toUnixTimestamp(month) * 1000000 AS timestamp
FROM aggregated_exchanges
ORDER BY timestamp

"#;

#[allow(dead_code)]
pub const P2P_OBSERVATIONS: &str = r#"SELECT 
    CAST(round(min(timestamp) / 1000), 'UInt64') AS first_observation
FROM ethereum.`chainbound.block_observations`
WHERE block_number = ? AND block_hash = ?"#;

#[allow(dead_code)]
pub const RAW_CEX_QUOTES: &str = r#"WITH
    grouped_time AS (
        SELECT
            c.exchange as exchange,
            upper(replaceAll(replaceAll(replaceAll(c.symbol, '/', ''), '-', ''), '_', '')) AS symbol,
            toUnixTimestamp(toDateTime(round(c.timestamp / 1000000), 'UTC')) * 1000000 AS timestamp_sec,
            argMin(c.timestamp, abs(CAST(c.timestamp, 'Int64') - CAST(timestamp_sec, 'Int64'))) as timestamp,
            argMin(c.ask_amount, abs(CAST(c.timestamp, 'Int64') - CAST(timestamp_sec, 'Int64'))) as ask_amount,
            argMin(c.ask_price, abs(CAST(c.timestamp, 'Int64') - CAST(timestamp_sec, 'Int64'))) as ask_price,
            argMin(c.bid_price, abs(CAST(c.timestamp, 'Int64') - CAST(timestamp_sec, 'Int64'))) as bid_price,
            argMin(c.bid_amount, abs(CAST(c.timestamp, 'Int64') - CAST(timestamp_sec, 'Int64'))) as bid_amount
        FROM cex.normalized_quotes as c
        WHERE c.timestamp >= ? AND c.timestamp < ?
        GROUP BY exchange, symbol, timestamp_sec
    )
SELECT
    exchange,
    symbol,
    timestamp,
    ask_amount,
    ask_price,
    bid_price,
    bid_amount
FROM grouped_time
ORDER BY timestamp
"#;

#[allow(dead_code)]
pub const CRIT_INIT_TABLES: &str = r#"with 
  pools as (
    select cast(count(), 'UInt64') as pool_creation from ( select init_block from ethereum.pools group by init_block )
  ),
  address_to_protocol as (
    select cast(count(distinct address), 'UInt64') as address_to_protocol from ethereum.pools 
    WHERE length(pools.tokens) >= 2
  ),
  tokens as (
    select cast(count(distinct address), 'UInt64') as tokens from brontes.token_info
  ),
  builder as (
    select cast(count(), 'UInt64') as builder from brontes_api.builder_info
  ),
  address_meta as(
    select cast(count(), 'UInt64') as address_meta from brontes_api.address_meta
  )
  select 
    p.*,
    a.*,
    t.*,
    b.*,
    am.* 
  from pools as p
    cross join address_to_protocol as a
    cross join tokens as t
    cross join builder as b
    cross join address_meta as am


"#;

#[allow(dead_code)]
pub const BLOCK_TIMES: &str = r#"SELECT
    CAST(block_number, 'UInt64') AS block_number,
    CAST(block_timestamp * 1000000, 'UInt64') AS block_timestamp
FROM ethereum.blocks
WHERE block_number >= ? AND block_number < ?
ORDER BY block_number"#;

#[allow(dead_code)]
pub const PRIVATE_FLOW: &str = r#"SELECT DISTINCT
    tx_hash
FROM ethereum.`chainbound.mempool`
WHERE tx_hash IN (
    SELECT arrayJoin(?) AS tx_hash
)"#;

}