Skip to content

Instantly share code, notes, and snippets.

@donovanhide

donovanhide/gist:ae86f05b842bb0fbf85e

Created August 14, 2014 16:39
Show Gist options
  • Save donovanhide/ae86f05b842bb0fbf85e to your computer and use it in GitHub Desktop.
Save donovanhide/ae86f05b842bb0fbf85e to your computer and use it in GitHub Desktop.
Download ZIP
Ripple transactions by week and year
Raw
gistfile1.txt
SELECT * FROM(
-> SELECT WEEK(FROM_UNIXTIME(CloseTime+946684800)) AS Week,
-> YEAR(FROM_UNIXTIME(CloseTime+946684800)) AS Year,
-> COUNT(*) AS Total,
-> SUM(IF(TransactionType=0,1,0)) AS Payment,
-> SUM(IF(TransactionType=3,1,0)) AS AccountSet,
-> SUM(IF(TransactionType=5,1,0)) AS SetRegularKey,
-> SUM(IF(TransactionType=7,1,0)) AS OfferCreate,
-> SUM(IF(TransactionType=8,1,0)) AS OfferCancel,
-> SUM(IF(TransactionType=20,1,0)) AS TrustSet,
-> SUM(IF(TransactionType=100,1,0)) AS Amendment,
-> SUM(IF(TransactionType=101,1,0)) AS FeeSet
-> FROM Ledger l
-> INNER JOIN Transaction t
-> ON l.LedgerSequence=t.LedgerSequence
-> GROUP BY WEEK(FROM_UNIXTIME(CloseTime+946684800)),
-> YEAR(FROM_UNIXTIME(CloseTime+946684800))
-> )s
-> ORDER BY s.Year,s.Week;
+------+------+---------+---------+------------+---------------+-------------+-------------+----------+-----------+--------+
| Week | Year | Total | Payment | AccountSet | SetRegularKey | OfferCreate | OfferCancel | TrustSet | Amendment | FeeSet |
+------+------+---------+---------+------------+---------------+-------------+-------------+----------+-----------+--------+
| 0 | 2013 | 72 | 47 | 0 | 0 | 4 | 0 | 21 | 0 | 0 |
| 1 | 2013 | 342 | 155 | 0 | 0 | 27 | 5 | 155 | 0 | 0 |
| 2 | 2013 | 427 | 259 | 77 | 0 | 20 | 3 | 68 | 0 | 0 |
| 3 | 2013 | 369 | 179 | 22 | 0 | 36 | 22 | 110 | 0 | 0 |
| 4 | 2013 | 327 | 136 | 0 | 0 | 78 | 23 | 90 | 0 | 0 |
| 5 | 2013 | 280 | 152 | 0 | 0 | 25 | 9 | 94 | 0 | 0 |
| 6 | 2013 | 2508 | 448 | 8 | 0 | 889 | 701 | 462 | 0 | 0 |
| 7 | 2013 | 4760 | 1221 | 7 | 0 | 1628 | 1341 | 563 | 0 | 0 |
| 8 | 2013 | 7522 | 1688 | 2 | 0 | 2946 | 2164 | 722 | 0 | 0 |
| 9 | 2013 | 12213 | 1971 | 1 | 0 | 5754 | 3583 | 904 | 0 | 0 |
| 10 | 2013 | 4048 | 1112 | 1 | 0 | 1497 | 951 | 487 | 0 | 0 |
| 11 | 2013 | 14274 | 1826 | 2 | 0 | 6266 | 5594 | 586 | 0 | 0 |
| 12 | 2013 | 11363 | 1717 | 1 | 0 | 4975 | 4315 | 355 | 0 | 0 |
| 13 | 2013 | 9600 | 2114 | 2 | 0 | 3824 | 3163 | 497 | 0 | 0 |
| 14 | 2013 | 11018 | 2569 | 1 | 0 | 4548 | 3124 | 776 | 0 | 0 |
| 15 | 2013 | 20892 | 6026 | 0 | 0 | 8023 | 5046 | 1797 | 0 | 0 |
| 16 | 2013 | 21458 | 7371 | 8 | 0 | 7459 | 4520 | 2099 | 0 | 1 |
| 17 | 2013 | 24917 | 6345 | 4 | 0 | 10087 | 7051 | 1430 | 0 | 0 |
| 18 | 2013 | 70256 | 38663 | 9 | 0 | 17954 | 10863 | 2767 | 0 | 0 |
| 19 | 2013 | 131814 | 64217 | 7 | 0 | 27570 | 37144 | 2876 | 0 | 0 |
| 20 | 2013 | 178005 | 20368 | 0 | 0 | 80560 | 74761 | 2316 | 0 | 0 |
| 21 | 2013 | 83083 | 28287 | 2 | 0 | 30045 | 22718 | 2031 | 0 | 0 |
| 22 | 2013 | 154754 | 90579 | 10 | 0 | 35304 | 26417 | 2444 | 0 | 0 |
| 23 | 2013 | 109653 | 61057 | 2 | 0 | 26826 | 20371 | 1397 | 0 | 0 |
| 24 | 2013 | 93345 | 32029 | 8 | 0 | 32673 | 27436 | 1199 | 0 | 0 |
| 25 | 2013 | 72199 | 32285 | 3 | 0 | 21638 | 16980 | 1293 | 0 | 0 |
| 26 | 2013 | 200974 | 143962 | 1 | 0 | 18770 | 15845 | 22396 | 0 | 0 |
| 27 | 2013 | 200193 | 71909 | 8 | 1 | 105767 | 21449 | 1059 | 0 | 0 |
| 28 | 2013 | 102740 | 24638 | 3 | 0 | 52113 | 24900 | 1086 | 0 | 0 |
| 29 | 2013 | 2421839 | 1038103 | 4 | 0 | 1275883 | 9540 | 98309 | 0 | 0 |
| 30 | 2013 | 87369 | 21978 | 0 | 0 | 43894 | 20312 | 1185 | 0 | 0 |
| 31 | 2013 | 223362 | 20778 | 9 | 0 | 107220 | 94499 | 856 | 0 | 0 |
| 32 | 2013 | 237243 | 13971 | 7 | 0 | 101402 | 121164 | 699 | 0 | 0 |
| 33 | 2013 | 216884 | 15017 | 3 | 0 | 89574 | 111458 | 832 | 0 | 0 |
| 34 | 2013 | 252207 | 19687 | 0 | 0 | 101830 | 130214 | 476 | 0 | 0 |
| 35 | 2013 | 226586 | 23788 | 0 | 0 | 88787 | 113528 | 483 | 0 | 0 |
| 36 | 2013 | 322826 | 65152 | 2 | 0 | 112032 | 145039 | 601 | 0 | 0 |
| 37 | 2013 | 319804 | 43954 | 9 | 0 | 123614 | 151228 | 999 | 0 | 0 |
| 38 | 2013 | 309376 | 24503 | 2 | 0 | 128063 | 155910 | 898 | 0 | 0 |
| 39 | 2013 | 308210 | 31435 | 4 | 0 | 123470 | 152275 | 1026 | 0 | 0 |
| 40 | 2013 | 320287 | 29435 | 122 | 0 | 129148 | 160802 | 780 | 0 | 0 |
| 41 | 2013 | 315904 | 19523 | 30 | 0 | 133327 | 162083 | 941 | 0 | 0 |
| 42 | 2013 | 307168 | 28368 | 46 | 0 | 126610 | 150776 | 1368 | 0 | 0 |
| 43 | 2013 | 312276 | 27978 | 107 | 0 | 129252 | 152827 | 2112 | 0 | 0 |
| 44 | 2013 | 280912 | 30737 | 112 | 0 | 114022 | 133431 | 2610 | 0 | 0 |
| 45 | 2013 | 260430 | 31932 | 54 | 0 | 105267 | 120846 | 2331 | 0 | 0 |
| 46 | 2013 | 353601 | 47813 | 436 | 0 | 142758 | 157773 | 4821 | 0 | 0 |
| 47 | 2013 | 369292 | 45282 | 591 | 0 | 171038 | 142511 | 9870 | 0 | 0 |
| 48 | 2013 | 413063 | 62874 | 1912 | 0 | 204324 | 134045 | 9905 | 0 | 3 |
| 49 | 2013 | 373273 | 65580 | 437 | 0 | 199464 | 102054 | 5738 | 0 | 0 |
| 50 | 2013 | 495826 | 99918 | 12647 | 2 | 278155 | 99001 | 6103 | 0 | 0 |
| 51 | 2013 | 326104 | 95290 | 6021 | 0 | 142532 | 77829 | 4432 | 0 | 0 |
| 52 | 2013 | 115613 | 47564 | 2138 | 0 | 38108 | 25712 | 2091 | 0 | 0 |
| 0 | 2014 | 151832 | 66385 | 3096 | 0 | 47570 | 31576 | 3205 | 0 | 0 |
| 1 | 2014 | 315509 | 129008 | 6395 | 0 | 109847 | 63402 | 6857 | 0 | 0 |
| 2 | 2014 | 340498 | 129667 | 8462 | 0 | 123381 | 74048 | 4940 | 0 | 0 |
| 3 | 2014 | 382009 | 129759 | 7054 | 0 | 136251 | 104181 | 4764 | 0 | 0 |
| 4 | 2014 | 478144 | 169678 | 10278 | 2 | 181899 | 112275 | 4012 | 0 | 0 |
| 5 | 2014 | 427614 | 109640 | 9358 | 0 | 185591 | 119504 | 3521 | 0 | 0 |
| 6 | 2014 | 549339 | 169740 | 7351 | 0 | 226993 | 140400 | 4855 | 0 | 0 |
| 7 | 2014 | 816412 | 449087 | 10201 | 0 | 213002 | 138621 | 5501 | 0 | 0 |
| 8 | 2014 | 609794 | 175722 | 15380 | 0 | 248801 | 165501 | 4390 | 0 | 0 |
| 9 | 2014 | 568356 | 150591 | 22413 | 0 | 234975 | 156413 | 3964 | 0 | 0 |
| 10 | 2014 | 460393 | 87430 | 15315 | 16 | 211990 | 142012 | 3630 | 0 | 0 |
| 11 | 2014 | 476713 | 101742 | 13990 | 11 | 206247 | 151247 | 3476 | 0 | 0 |
| 12 | 2014 | 999308 | 590577 | 12763 | 3 | 230182 | 161504 | 4279 | 0 | 0 |
| 13 | 2014 | 578677 | 148532 | 13477 | 2 | 239857 | 173483 | 3326 | 0 | 0 |
| 14 | 2014 | 639499 | 108224 | 13178 | 3 | 303124 | 211379 | 3591 | 0 | 0 |
| 15 | 2014 | 719259 | 169614 | 18104 | 0 | 316410 | 211656 | 3475 | 0 | 0 |
| 16 | 2014 | 646206 | 167987 | 12658 | 24 | 278463 | 183364 | 3710 | 0 | 0 |
| 17 | 2014 | 633622 | 121124 | 14310 | 4 | 300696 | 193524 | 3964 | 0 | 0 |
| 18 | 2014 | 716606 | 113307 | 15674 | 2 | 347566 | 235319 | 4738 | 0 | 0 |
| 19 | 2014 | 677065 | 74330 | 14687 | 1 | 345710 | 238197 | 4140 | 0 | 0 |
| 20 | 2014 | 1080989 | 201924 | 16948 | 2 | 547156 | 310816 | 4143 | 0 | 0 |
| 21 | 2014 | 934248 | 85925 | 15515 | 0 | 522884 | 306685 | 3239 | 0 | 0 |
| 22 | 2014 | 890978 | 153376 | 15902 | 5 | 447134 | 271561 | 3000 | 0 | 0 |
| 23 | 2014 | 847068 | 116391 | 15741 | 0 | 440496 | 271623 | 2817 | 0 | 0 |
| 24 | 2014 | 1208958 | 135040 | 98722 | 4 | 540943 | 431792 | 2457 | 0 | 0 |
| 25 | 2014 | 1061512 | 61624 | 78333 | 3 | 490167 | 422951 | 8434 | 0 | 0 |
| 26 | 2014 | 1117003 | 72678 | 13497 | 3 | 599770 | 428632 | 2423 | 0 | 0 |
| 27 | 2014 | 1601712 | 72951 | 120282 | 4 | 829004 | 504595 | 74876 | 0 | 0 |
| 28 | 2014 | 1485145 | 108042 | 22700 | 5 | 791573 | 513826 | 48999 | 0 | 0 |
| 29 | 2014 | 1405358 | 74188 | 17102 | 0 | 778083 | 534046 | 1939 | 0 | 0 |
| 30 | 2014 | 1305147 | 115075 | 17243 | 12 | 697920 | 472183 | 2714 | 0 | 0 |
| 31 | 2014 | 1293684 | 203562 | 16614 | 2 | 677742 | 393251 | 2513 | 0 | 0 |
| 32 | 2014 | 1294252 | 132160 | 14096 | 0 | 737928 | 374610 | 35458 | 0 | 0 |
+------+------+---------+---------+------------+---------------+-------------+-------------+----------+-----------+--------+
86 rows in set (1 min 21.99 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment