-
Notifications
You must be signed in to change notification settings - Fork 35
/
Copy pathpartials.go
864 lines (834 loc) · 34.3 KB
/
partials.go
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
package graph
// TODO make more dynamic.
const swapDeDup = `
swapDeDup AS (
SELECT
if(amount_id >= 0 and tokens_sold = 0, toUInt256(amount_id), sold_id) AS sold_id,
if(amount_id >= 0 and tokens_sold = 0, toUInt256(amount[toUInt256(amount_id)]), tokens_sold) AS tokens_sold,
if(amount_id >= 0 and tokens_bought = 0, toUInt256(amount_id), bought_id) AS bought_id,
if(amount_id >= 0 and tokens_bought = 0, toUInt256(amount[toUInt256(amount_id)]), tokens_bought) AS tokens_bought,
*
FROM (
SELECT tokens_bought,
tokens_sold,
sold_id,
bought_id,
token_decimal,
amount,
amount_usd,
contract_address AS swap_address,
tx_hash AS swap_tx_hash,
chain_id AS swap_chain_id,
if(notEmpty(amount.keys), amount.keys[1], -1) AS amount_id
FROM swap_events WHERE rowCount > 0 AND timestamp >= minTimestamp
LIMIT 1 BY chain_id, contract_address, event_type, block_number, event_index, tx_hash)
)
`
const originToDestCol = `
f.pre_ftoken AS ftoken,
f.pre_famount AS famount,
f.pre_famount_usd AS famount_usd,
f.pre_fevent_type AS fevent_type,
f.pre_ftoken_raw AS ftoken_raw,
f.pre_ftx_hash AS ftx_hash,
f.pre_fchain_id AS fchain_id,
f.pre_fcontract_address AS fcontract_address,
f.pre_ftoken_symbol AS ftoken_symbol,
f.pre_fdestination_kappa AS fdestination_kappa,
f.pre_fsender AS fsender,
f.pre_frecipient AS frecipient,
f.pre_frecipient_bytes AS frecipient_bytes,
f.pre_ffee AS ffee,
f.pre_fkappa AS fkappa,
f.pre_ftoken_index_from AS ftoken_index_from,
f.pre_ftoken_index_to AS ftoken_index_to,
f.pre_fmin_dy AS fmin_dy,
f.pre_fdeadline AS fdeadline,
f.pre_fblock_number AS fblock_number,
f.pre_fswap_success AS fswap_success,
f.pre_fswap_token_index AS fswap_token_index,
f.pre_fswap_min_amount AS fswap_min_amount,
f.pre_fswap_deadline AS fswap_deadline,
f.pre_ffee_amount_usd AS ffee_amount_usd,
f.pre_ftoken_decimal AS ftoken_decimal,
f.pre_ftimestamp AS ftimestamp,
f.pre_fdestination_chain_id AS fdestination_chain_id,
f.pre_finsert_time AS finsert_time,
IF(
ti.token_address = '', be.token, ti.token_address
) AS ttoken,
IF(
se.amount[se.bought_id] != '', toUInt256(se.amount[se.bought_id]),
be.amount
) AS tamount,
IF(
se.amount[se.bought_id] != '', se.token_decimal[se.bought_id],
be.token_decimal
) AS ttoken_decimal,
IF(
se.amount_usd[se.bought_id] > 0,
se.amount_usd[se.bought_id],
be.amount_usd
) AS tamount_usd,
be.event_type AS tevent_type,
be.token AS ttoken_raw,
be.tx_hash AS ttx_hash,
be.chain_id AS tchain_id,
be.contract_address AS tcontract_address,
be.token_symbol AS ttoken_symbol,
be.destination_kappa AS tdestination_kappa,
be.sender AS tsender,
be.recipient AS trecipient,
be.recipient_bytes AS trecipient_bytes,
toUInt256(be.fee) AS tfee,
be.kappa AS tkappa,
be.token_index_from AS ttoken_index_from,
be.token_index_to AS ttoken_index_to,
be.min_dy AS tmin_dy,
be.deadline AS tdeadline,
be.swap_success AS tswap_success,
be.swap_token_index AS tswap_token_index,
be.swap_min_amount AS tswap_min_amount,
be.swap_deadline AS tswap_deadline,
be.block_number AS tblock_number,
be.fee_usd AS tfee_amount_usd,
be.timestamp AS ttimestamp,
be.destination_chain_id AS tdestination_chain_id,
be.insert_time AS tinsert_time
FROM
(
SELECT
IF(
ti.token_address = '', be.token, ti.token_address
) AS pre_ftoken,
IF(
se.amount[se.sold_id] != '', toUInt256(se.amount[se.sold_id]),
be.amount
) AS pre_famount,
IF(
se.amount[se.sold_id] != '', se.token_decimal[se.sold_id],
be.token_decimal
) AS pre_ftoken_decimal,
IF(
se.amount_usd[se.sold_id] > 0,
se.amount_usd[se.sold_id],
be.amount_usd
) AS pre_famount_usd,
be.event_type AS pre_fevent_type,
be.token AS pre_ftoken_raw,
be.tx_hash AS pre_ftx_hash,
be.chain_id AS pre_fchain_id,
be.block_number AS pre_fblock_number,
be.contract_address AS pre_fcontract_address,
be.token_symbol AS pre_ftoken_symbol,
be.destination_kappa AS pre_fdestination_kappa,
be.sender AS pre_fsender,
be.recipient AS pre_frecipient,
be.recipient_bytes AS pre_frecipient_bytes,
toUInt256(be.fee) AS pre_ffee,
be.kappa AS pre_fkappa,
be.token_index_from AS pre_ftoken_index_from,
be.token_index_to AS pre_ftoken_index_to,
be.min_dy AS pre_fmin_dy,
be.deadline AS pre_fdeadline,
be.swap_success AS pre_fswap_success,
be.swap_token_index AS pre_fswap_token_index,
be.swap_min_amount AS pre_fswap_min_amount,
be.swap_deadline AS pre_fswap_deadline,
be.fee_usd AS pre_ffee_amount_usd,
be.timestamp AS pre_ftimestamp,
be.destination_chain_id AS pre_fdestination_chain_id,
be.insert_time AS pre_finsert_time
`
const destToOriginCol = `
t.pre_ttoken AS ttoken,
t.pre_tamount AS tamount,
t.pre_tamount_usd AS tamount_usd,
t.pre_tevent_type AS tevent_type,
t.pre_ttoken_raw AS ttoken_raw,
t.pre_ttx_hash AS ttx_hash,
t.pre_tchain_id AS tchain_id,
t.pre_tcontract_address AS tcontract_address,
t.pre_ttoken_symbol AS ttoken_symbol,
t.pre_tdestination_kappa AS tdestination_kappa,
t.pre_tsender AS tsender,
t.pre_trecipient AS trecipient,
t.pre_trecipient_bytes AS trecipient_bytes,
t.pre_tfee AS tfee,
t.pre_tkappa AS tkappa,
t.pre_ttoken_index_from AS ttoken_index_from,
t.pre_ttoken_index_to AS ttoken_index_to,
t.pre_tmin_dy AS tmin_dy,
t.pre_tblock_number AS tblock_number,
t.pre_tdeadline AS tdeadline,
t.pre_tswap_success AS tswap_success,
t.pre_tswap_token_index AS tswap_token_index,
t.pre_tswap_min_amount AS tswap_min_amount,
t.pre_tswap_deadline AS tswap_deadline,
t.pre_tfee_amount_usd AS tfee_amount_usd,
t.pre_ttoken_decimal AS ttoken_decimal,
t.pre_ttimestamp AS ttimestamp,
t.pre_tdestination_chain_id AS tdestination_chain_id,
t.pre_tinsert_time AS tinsert_time,
IF(
ti.token_address = '', be.token, ti.token_address
) AS ftoken,
IF(
se.amount[se.sold_id] != '', toUInt256(se.amount[se.sold_id]),
be.amount
) AS famount,
IF(
se.amount[se.sold_id] != '', se.token_decimal[se.sold_id],
be.token_decimal
) AS ftoken_decimal,
IF(
se.amount_usd[se.sold_id] > 0,
se.amount_usd[se.sold_id],
be.amount_usd
) AS famount_usd,
be.event_type AS fevent_type,
be.token AS ftoken_raw,
be.tx_hash AS ftx_hash,
be.chain_id AS fchain_id,
be.contract_address AS fcontract_address,
be.token_symbol AS ftoken_symbol,
be.destination_kappa AS fdestination_kappa,
be.sender AS fsender,
be.recipient AS frecipient,
be.recipient_bytes AS frecipient_bytes,
toUInt256(be.fee) AS ffee,
be.kappa AS fkappa,
be.token_index_from AS ftoken_index_from,
be.token_index_to AS ftoken_index_to,
be.min_dy AS fmin_dy,
be.deadline AS fdeadline,
be.swap_success AS fswap_success,
be.swap_token_index AS fswap_token_index,
be.swap_min_amount AS fswap_min_amount,
be.swap_deadline AS fswap_deadline,
be.block_number AS fblock_number,
be.fee_usd AS ffee_amount_usd,
be.timestamp AS ftimestamp,
be.destination_chain_id AS fdestination_chain_id,
be.insert_time AS finsert_time
FROM
(
SELECT
IF(
ti.token_address = '', be.token, ti.token_address
) AS pre_ttoken,
IF(
se.amount[se.bought_id] != '', toUInt256(se.amount[se.bought_id]),
be.amount
) AS pre_tamount,
IF(
se.amount[se.bought_id] != '', se.token_decimal[se.bought_id],
be.token_decimal
) AS pre_ttoken_decimal,
IF(
se.amount_usd[se.bought_id] > 0,
se.amount_usd[se.bought_id],
be.amount_usd
) AS pre_tamount_usd,
be.event_type AS pre_tevent_type,
be.token AS pre_ttoken_raw,
be.tx_hash AS pre_ttx_hash,
be.chain_id AS pre_tchain_id,
be.contract_address AS pre_tcontract_address,
be.token_symbol AS pre_ttoken_symbol,
be.destination_kappa AS pre_tdestination_kappa,
be.sender AS pre_tsender,
be.recipient AS pre_trecipient,
be.recipient_bytes AS pre_trecipient_bytes,
toUInt256(be.fee) AS pre_tfee,
be.kappa AS pre_tkappa,
be.token_index_from AS pre_ttoken_index_from,
be.token_index_to AS pre_ttoken_index_to,
be.min_dy AS pre_tmin_dy,
be.deadline AS pre_tdeadline,
be.block_number AS pre_tblock_number,
be.swap_success AS pre_tswap_success,
be.swap_token_index AS pre_tswap_token_index,
be.swap_min_amount AS pre_tswap_min_amount,
be.swap_deadline AS pre_tswap_deadline,
be.fee_usd AS pre_tfee_amount_usd,
be.timestamp AS pre_ttimestamp,
be.destination_chain_id AS pre_tdestination_chain_id,
be.insert_time AS pre_tinsert_time
`
const singleSideCol = `
IF(
ti.token_address = '', be.token, ti.token_address
) AS token,
IF(
se.amount[se.sold_id] != '', toUInt256(se.amount[se.sold_id]),
be.amount
) AS amount,
IF(
se.amount[se.sold_id] != '', se.token_decimal[se.sold_id],
be.token_decimal
) AS token_decimal,
IF(
se.amount_usd[se.sold_id] > 0,
se.amount_usd[se.sold_id],
be.amount_usd
) AS amount_usd,
be.event_type AS event_type,
be.token AS token_raw,
be.tx_hash AS tx_hash,
be.chain_id AS chain_id,
be.contract_address AS contract_address,
be.token_symbol AS token_symbol,
be.destination_kappa AS destination_kappa,
be.sender AS sender,
be.recipient AS recipient,
be.recipient_bytes AS recipient_bytes,
be.fee AS fee,
be.kappa AS kappa,
be.token_index_from AS token_index_from,
be.token_index_to AS token_index_to,
be.min_dy AS min_dy,
be.deadline AS deadline,
be.swap_success AS swap_success,
be.swap_token_index AS swap_token_index,
be.swap_min_amount AS swap_min_amount,
be.swap_deadline AS swap_deadline,
be.fee_usd AS fee_usd,
be.timestamp AS timestamp,
be.destination_chain_id AS destination_chain_id,
be.insert_time AS insert_time
`
const singleSideJoinsCTE = `
be
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT
DISTINCT ON (
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.sold_id
`
const baseSwap = `
SELECT * FROM swap_events LIMIT 1 BY chain_id, contract_address, event_type, block_number, event_index, tx_hash
`
const baseSwapWithTokenPt1 = `
SELECT
if(amount_id >= 0 and tokens_sold = 0, toUInt256(amount_id), sold_id) AS sold_id,
if(amount_id >= 0 and tokens_sold = 0, toUInt256(amount[toUInt256(amount_id)]), tokens_sold) AS tokens_sold,
if(amount_id >= 0 and tokens_bought = 0, toUInt256(amount_id), bought_id) AS bought_id,
if(amount_id >= 0 and tokens_bought = 0, toUInt256(amount[toUInt256(amount_id)]), tokens_bought) AS tokens_bought,
*
FROM (
SELECT tokens_bought,
tokens_sold,
sold_id,
event_type,
chain_id,
bought_id,
token_decimal,
amount,
amount_usd,
fee_usd,
sender,
tx_hash,
contract_address AS swap_address,
chain_id AS swap_chain_id,
if(notEmpty(amount.keys), amount.keys[1], -1) AS amount_id
FROM swap_events
`
const baseSwapWithTokenPt2 = `
LIMIT 1 BY chain_id, contract_address, event_type, block_number, event_index, tx_hash)
) se
LEFT JOIN (
SELECT DISTINCT ON(
chain_id, token_index, contract_address
) token_address AS token, *
FROM
token_indices
) ti ON se.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.bought_id
`
const baseMessageBus = `
SELECT * FROM message_bus_events LIMIT 1 BY chain_id, contract_address, event_type, block_number, event_index, tx_hash
`
const swapVolumeSelect = `
multiIf(event_type = 0, amount_usd[sold_id],event_type = 1, arraySum(mapValues(amount_usd)),event_type = 9, arraySum(mapValues(amount_usd)),event_type = 10,amount_usd[sold_id],0)
`
const orString = " OR "
const whereString = " WHERE ("
// TODO MAKE MORE DYNAMIC
const dailyVolumeBridgeMvPt1 = `
SELECT date,
results[1] AS ethereum,
results[10] AS optimism,
results[25] AS cronos,
results[56] AS bsc,
results[137] AS polygon,
results[250] AS fantom,
results[288] AS boba,
results[1088] AS metis,
results[1284] AS moonbeam,
results[1285] AS moonriver,
results[8217] AS klaytn,
results[42161] AS arbitrum,
results[43114] AS avalanche,
results[53935] AS dfk,
results[1313161554] AS aurora,
results[1666600000] AS harmony,
results[7700] AS canto,
results[2000] AS dogechain,
results[8453] AS base,
results[81457] AS blast,
results[534352] AS scroll,
results[59144] AS linea,
results[480] AS worldchain,
results[130] AS unichain,
results[80094] AS berachain,
results[999] AS hyperEVM,
arraySum(mapValues(results)) AS total
FROM (SELECT date, maxMap(map(chain_id, total)) AS results
FROM (SELECT coalesce(toString(b.date), toString(s.date)) AS date,
toInt64(coalesce(b.chain_id, s.chain_id, 0)) as chain_id,
(toFloat64(coalesce(b.usdTotal, 0)) + toFloat64(coalesce(s.usdTotal, 0)) ) as total
FROM (
SELECT toDate(FROM_UNIXTIME(ftimestamp, '%Y/%m/%d')) as date,
fchain_id AS chain_id,
sumKahan(famount_usd) as usdTotal
FROM (SELECT *
FROM mv_bridge_events
`
const dailyVolumeBridgeMvPt2 = `
ORDER BY ftimestamp DESC, fblock_number DESC, fevent_index DESC, insert_time DESC
LIMIT 1 BY fchain_id, fcontract_address, fevent_type, fblock_number, fevent_index, ftx_hash)
GROUP BY date, chain_id order by date, chain_id) b
`
const dailyVolumeBridge = `
SELECT date,
results[1] AS ethereum,
results[10] AS optimism,
results[25] AS cronos,
results[56] AS bsc,
results[137] AS polygon,
results[250] AS fantom,
results[288] AS boba,
results[1088] AS metis,
results[1284] AS moonbeam,
results[1285] AS moonriver,
results[8217] AS klaytn,
results[42161] AS arbitrum,
results[43114] AS avalanche,
results[53935] AS dfk,
results[1313161554] AS aurora,
results[1666600000] AS harmony,
results[7700] AS canto,
results[2000] AS dogechain,
results[8453] AS base,
results[81457] AS blast,
results[534352] AS scroll,
results[59144] AS linea,
results[480] AS worldchain,
results[130] AS unichain,
results[80094] AS berachain,
results[999] AS hyperEVM,
arraySum(mapValues(results)) AS total
FROM (SELECT date, maxMap(map(chain_id, total)) AS results
FROM (SELECT coalesce(toString(b.date), toString(s.date)) AS date,
toInt64(coalesce(pre_fchain_id, s.chain_id, 0)) as chain_id,
(toFloat64(coalesce(b.usdTotal, 0)) + toFloat64(coalesce(s.usdTotal, 0)) ) as total
FROM (
SELECT toDate(FROM_UNIXTIME(pre_ftimestamp, '%Y/%m/%d')) as date,
pre_fchain_id,
sumKahan(pre_famount_usd) as usdTotal
FROM (
SELECT IF(
ti.token_address = '', be.token, ti.token_address
) AS pre_ftoken,
IF(
se.amount[se.sold_id] != '', toUInt256(se.amount[se.sold_id]),
be.amount
) AS pre_famount,
IF(
se.amount[se.sold_id] != '', se.token_decimal[se.sold_id],
be.token_decimal
) AS pre_ftoken_decimal,
IF(
se.amount_usd[se.sold_id] > 0,
se.amount_usd[se.sold_id],
be.amount_usd
) AS pre_famount_usd,
be.event_type AS pre_fevent_type,
be.token AS pre_ftoken_raw,
be.tx_hash AS pre_ftx_hash,
be.chain_id AS pre_fchain_id,
be.block_number AS pre_fblock_number,
be.contract_address AS pre_fcontract_address,
be.token_symbol AS pre_ftoken_symbol,
be.destination_kappa AS pre_fdestination_kappa,
be.sender AS pre_fsender,
be.recipient AS pre_frecipient,
be.recipient_bytes AS pre_frecipient_bytes,
toUInt256(be.fee) AS pre_ffee,
be.kappa AS pre_fkappa,
be.token_index_from AS pre_ftoken_index_from,
be.token_index_to AS pre_ftoken_index_to,
be.min_dy AS pre_fmin_dy,
be.deadline AS pre_fdeadline,
be.swap_success AS pre_fswap_success,
be.swap_token_index AS pre_fswap_token_index,
be.swap_min_amount AS pre_fswap_min_amount,
be.swap_deadline AS pre_fswap_deadline,
be.fee_usd AS pre_ffee_amount_usd,
be.timestamp AS pre_ftimestamp,
be.destination_chain_id AS pre_fdestination_chain_id,
be.insert_time AS pre_finsert_time
FROM baseQuery be
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT DISTINCT ON(
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.sold_id)
group by date, pre_fchain_id
order by date, pre_fchain_id) b
`
const toDateSelect = `toDate(FROM_UNIXTIME(timestamp, '%Y/%m/%d')) as date`
const toDateSelectMv = `toDate(FROM_UNIXTIME(ftimestamp, '%Y/%m/%d')) as date`
// TODO MAKE MORE DYNAMIC.
const dailyStatisticGenericSelect = `
SELECT date,
results[1] AS ethereum,
results[10] AS optimism,
results[25] AS cronos,
results[56] AS bsc,
results[137] AS polygon,
results[250] AS fantom,
results[288] AS boba,
results[1088] AS metis,
results[1284] AS moonbeam,
results[1285] AS moonriver,
results[8217] AS klaytn,
results[42161] AS arbitrum,
results[43114] AS avalanche,
results[53935] AS dfk,
results[1313161554] AS aurora,
results[1666600000] AS harmony,
results[7700] AS canto,
results[2000] AS dogechain,
results[8453] AS base,
results[81457] AS blast,
results[534352] AS scroll,
results[59144] AS linea,
results[480] AS worldchain,
results[130] AS unichain,
results[80094] AS berachain,
arraySum(mapValues(results)) AS total
FROM (SELECT date, maxMap(map(chain_id, total)) AS results
FROM (SELECT coalesce(toString(b.date), toString(s.date), toString(m.date)) AS date,
toInt64(coalesce(b.chain_id, s.chain_id, m.chain_id, 0)) as chain_id,
toFloat64(coalesce(b.sumTotal, 0)) +toFloat64(coalesce(s.sumTotal, 0))+toFloat64(coalesce(m.sumTotal, 0)) as total`
const rankedChainsBridgeVolume = `
SELECT toInt64(coalesce(pre_fchain_id, s.chain_id, 0)) as chain_id,
(toFloat64(coalesce(b.usdTotal, 0)) +
toFloat64(coalesce(s.usdTotal, 0))) as total
FROM (
SELECT pre_fchain_id,
sumKahan(pre_famount_usd) as usdTotal
FROM (
SELECT IF(
ti.token_address = '', be.token,
ti.token_address
) AS pre_ftoken,
IF(
se.amount[se.sold_id] != '',
toUInt256(se.amount[se.sold_id]),
be.amount
) AS pre_famount,
IF(
se.amount[se.sold_id] != '',
se.token_decimal[se.sold_id],
be.token_decimal
) AS pre_ftoken_decimal,
IF(
se.amount_usd[se.sold_id] > 0,
se.amount_usd[se.sold_id],
be.amount_usd
) AS pre_famount_usd,
be.event_type AS pre_fevent_type,
be.token AS pre_ftoken_raw,
be.tx_hash AS pre_ftx_hash,
be.chain_id AS pre_fchain_id,
be.block_number AS pre_fblock_number,
be.contract_address AS pre_fcontract_address,
be.token_symbol AS pre_ftoken_symbol,
be.destination_kappa AS pre_fdestination_kappa,
be.sender AS pre_fsender,
be.recipient AS pre_frecipient,
be.recipient_bytes AS pre_frecipient_bytes,
toUInt256(be.fee) AS pre_ffee,
be.kappa AS pre_fkappa,
be.token_index_from AS pre_ftoken_index_from,
be.token_index_to AS pre_ftoken_index_to,
be.min_dy AS pre_fmin_dy,
be.deadline AS pre_fdeadline,
be.swap_success AS pre_fswap_success,
be.swap_token_index AS pre_fswap_token_index,
be.swap_min_amount AS pre_fswap_min_amount,
be.swap_deadline AS pre_fswap_deadline,
be.fee_usd AS pre_ffee_amount_usd,
be.timestamp AS pre_ftimestamp,
be.destination_chain_id AS pre_fdestination_chain_id,
be.insert_time AS pre_finsert_time
FROM baseQuery be
LEFT JOIN swapDeDup se
ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT DISTINCT ON(
chain_id, token_index,
contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.sold_id)
group by pre_fchain_id
order by pre_fchain_id) b
`
const dailyStatisticGenericSinglePlatform = `
SELECT date,
results[1] AS ethereum,
results[10] AS optimism,
results[25] AS cronos,
results[56] AS bsc,
results[137] AS polygon,
results[250] AS fantom,
results[288] AS boba,
results[1088] AS metis,
results[1284] AS moonbeam,
results[1285] AS moonriver,
results[8217] AS klaytn,
results[42161] AS arbitrum,
results[43114] AS avalanche,
results[53935] AS dfk,
results[1313161554] AS aurora,
results[1666600000] AS harmony,
results[7700] AS canto,
results[2000] AS dogechain,
results[8453] AS base,
results[81457] AS blast,
results[534352] AS scroll,
results[59144] AS linea,
results[480] AS worldchain,
results[130] AS unichain,
results[80094] AS berachain,
results[999] AS hyperEVM,
arraySum(mapValues(results)) AS total
FROM (
SELECT date,
maxMap(map(chain_id, sumTotal)) AS results
FROM (SELECT toString(toDate(FROM_UNIXTIME(timestamp, '%Y/%m/%d'))) as date,
chain_id,
`
const dailyStatisticGenericSinglePlatformMv = `
SELECT date,
results[1] AS ethereum,
results[10] AS optimism,
results[25] AS cronos,
results[56] AS bsc,
results[137] AS polygon,
results[250] AS fantom,
results[288] AS boba,
results[1088] AS metis,
results[1284] AS moonbeam,
results[1285] AS moonriver,
results[8217] AS klaytn,
results[42161] AS arbitrum,
results[43114] AS avalanche,
results[53935] AS dfk,
results[1313161554] AS aurora,
results[1666600000] AS harmony,
results[7700] AS canto,
results[2000] AS dogechain,
results[8453] AS base,
results[81457] AS blast,
results[534352] AS scroll,
results[59144] AS linea,
results[480] AS worldchain,
results[130] AS unichain,
results[80094] AS berachain,
results[999] AS hyperEVM,
arraySum(mapValues(results)) AS total
FROM (
SELECT date,
maxMap(map(chain_id, sumTotal)) AS results
FROM (SELECT toString(toDate(FROM_UNIXTIME(ftimestamp, '%Y/%m/%d'))) as date,
fchain_id AS chain_id,
`
const dailyStatisticGenericSinglePlatformMvFee = `
SELECT date,
results[1] AS ethereum,
results[10] AS optimism,
results[25] AS cronos,
results[56] AS bsc,
results[137] AS polygon,
results[250] AS fantom,
results[288] AS boba,
results[1088] AS metis,
results[1284] AS moonbeam,
results[1285] AS moonriver,
results[8217] AS klaytn,
results[42161] AS arbitrum,
results[43114] AS avalanche,
results[53935] AS dfk,
results[1313161554] AS aurora,
results[1666600000] AS harmony,
results[7700] AS canto,
results[2000] AS dogechain,
results[8453] AS base,
results[81457] AS blast,
results[534352] AS scroll,
results[59144] AS linea,
results[480] AS worldchain,
results[130] AS unichain,
results[80094] AS berachain,
results[999] AS hyperEVM,
arraySum(mapValues(results)) AS total
FROM (
SELECT date,
maxMap(map(chain_id, sumTotal)) AS results
FROM (SELECT toString(toDate(FROM_UNIXTIME(ttimestamp, '%Y/%m/%d'))) as date,
tchain_id AS chain_id,
`
const dailyStatisticBridge = `
FROM (
SELECT IF(
se.amount_usd[se.sold_id] > 0,
se.amount_usd[se.sold_id],
be.amount_usd
) AS amount_usd,
be.tx_hash AS tx_hash,
be.sender AS sender,
be.fee_usd AS fee_usd,
be.chain_id AS chain_id,
be.timestamp AS timestamp
FROM baseQuery be
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT DISTINCT ON(
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.sold_id)
`
const destToOriginJoinsPt1 = `
be
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT
DISTINCT ON (
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = be.bought_id
) AS t
LEFT JOIN (
SELECT
*
FROM
bridge_events WHERE timestamp >= minTimestamp AND destination_chain_id > 0
`
const destToOriginJoinsPt2 = `
ORDER BY
block_number DESC,
event_index DESC,
insert_time DESC
LIMIT
1 BY chain_id,
contract_address,
event_type,
block_number,
event_index,
tx_hash
) be ON pre_tchain_id = be.destination_chain_id
AND pre_tkappa = be.destination_kappa
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT
DISTINCT ON (
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.sold_id
`
const originToDestJoinsPt1 = `
be
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT
DISTINCT ON (
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.sold_id
) AS f
LEFT JOIN (
SELECT
*
from
bridge_events WHERE timestamp >= minTimestamp AND destination_chain_id = 0
`
const originToDestJoinsPt2 = `
ORDER BY
block_number DESC,
event_index DESC,
insert_time DESC
LIMIT
1 BY chain_id,
contract_address,
event_type,
block_number,
event_index,
tx_hash
) be ON fdestination_chain_id = be.chain_id
AND fdestination_kappa = be.kappa
LEFT JOIN swapDeDup se ON be.tx_hash = se.swap_tx_hash
AND be.chain_id = se.swap_chain_id
LEFT JOIN (
SELECT
DISTINCT ON (
chain_id, token_index, contract_address
) *
FROM
token_indices
) ti ON be.chain_id = ti.chain_id
AND se.swap_address = ti.contract_address
AND ti.token_index = se.bought_id
`