Skip to content

Instantly share code, notes, and snippets.

@hellais

hellais/query.sql

Created November 29, 2017 11:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hellais/1758488eb339254c0fabba7380ed2870 to your computer and use it in GitHub Desktop.
Save hellais/1758488eb339254c0fabba7380ed2870 to your computer and use it in GitHub Desktop.
Download ZIP
Tor failure rate around the world
Raw
query.sql
SELECT
probe_cc,
probe_asn_count,
success_count,
failure_count,
total_count,
ROUND(
(CAST(success_count AS FLOAT) / CAST(total_count AS FLOAT))::numeric
, 3) AS percentage,
test_runtime_avg
FROM (
SELECT AVG(
GREATEST(
LEAST(measurement.test_runtime, vanilla_tor.timeout),
0
)) AS test_runtime_avg, -- I do this to overcome some bugs in few measurements that have a very high runtime or a negative runtime
report.probe_cc AS probe_cc,
COUNT(DISTINCT report.probe_asn) AS probe_asn_count,
COUNT(CASE WHEN vanilla_tor.success = true THEN 1 END) as success_count,
COUNT(CASE WHEN vanilla_tor.success = false THEN 1 END) as failure_count,
COUNT(*) as total_count
FROM vanilla_tor
JOIN measurement ON vanilla_tor.msm_no = measurement.msm_no
JOIN report ON report.report_no = measurement.report_no
GROUP BY probe_cc
) AS vt
Raw
vanilla_tor-success-failure-by-country.csv
probe_cc probe_asn_count success_count failure_count total_count percentage test_runtime_avg
BN 1 1 0 1 1 107.654273986816
BO 1 1 0 1 1 32.3518943786621
CD 1 1 0 1 1 45.4121780395508
GA 1 1 0 1 1 36.9110717773438
JO 1 1 0 1 1 11.7876510620117
LR 1 1 0 1 1 20.0570201873779
MC 1 1 0 1 1 20.15110206604
MU 1 1 0 1 1 8.52128791809082
MV 1 1 0 1 1 14.4184980392456
NG 1 1 0 1 1 13.7381219863892
AL 1 2 0 2 1 29.3521809577942
DZ 1 2 0 2 1 13.6746397018433
FO 1 2 0 2 1 28.6844758987427
GE 1 2 0 2 1 4.9218430519104
GH 1 2 0 2 1 8.11505961418152
SR 2 2 0 2 1 29.514461517334
AO 1 3 0 3 1 14.9239107767741
LK 1 3 0 3 1 9.83234151204427
SZ 1 2 1 3 0.667 218.354227701823
DO 1 4 0 4 1 23.7161037921906
EU 3 4 0 4 1 72.3998990058899
IM 1 4 0 4 1 49.3362998962402
UY 1 4 0 4 1 13.5473358631134
PA 2 5 0 5 1 21.7617520332336
RS 3 5 1 6 0.833 105.349337736766
SS 1 4 2 6 0.667 183.090033213298
HU 2 7 0 7 1 35.8429614475795
SA 1 7 0 7 1 28.446263722011
ET 1 7 2 9 0.778 163.405189938015
CU 1 11 1 12 0.917 103.626942793528
JP 5 13 0 13 1 44.5179777878981
MG 1 13 0 13 1 31.3276907847478
TZ 4 13 1 14 0.929 63.0607640402658
CR 4 15 1 16 0.938 58.9820928573608
EC 7 15 1 16 0.938 61.9029508829117
LB 6 16 0 16 1 38.2876336574554
PE 2 19 0 19 1 35.4790872272692
CO 4 21 0 21 1 27.7259818939936
SG 6 21 1 22 0.955 52.7776248671792
AF 2 22 1 23 0.957 58.6827684485394
TN 3 20 4 24 0.833 107.625121792157
EE 1 25 0 25 1 5.11678636550903
PH 1 26 0 26 1 45.1022632672236
LS 3 27 0 27 1 56.5934703261764
HK 4 28 0 28 1 25.1352293831962
PT 5 28 4 32 0.875 48.5217010378838
ZM 2 26 6 32 0.813 94.9129467904568
KW 2 25 10 35 0.714 102.370763969421
KG 2 35 1 36 0.972 28.1690590381622
LA 2 36 1 37 0.973 41.2536270811751
CM 1 39 0 39 1 52.7203933275663
VE 4 43 1 44 0.977 48.0908779447729
ZW 5 42 6 48 0.875 82.1192637483279
IL 2 53 0 53 1 10.7893147738475
KH 7 48 7 55 0.873 78.2368997227062
NZ 2 55 0 55 1 20.7299786827781
BE 5 66 1 67 0.985 19.9623954224942
SK 4 74 0 74 1 9.22785359459954
MM 1 74 1 75 0.987 54.0655265299479
AE 2 78 2 80 0.975 29.3189157187939
ML 1 61 21 82 0.744 128.358245663527
UA 11 82 4 86 0.953 29.0135976436526
AT 6 84 3 87 0.966 24.8813862142892
RE 4 96 0 96 1 32.2202212909857
KZ 2 2 97 99 0.02 294.266922661752
IE 3 103 0 103 1 6.48503247973988
DK 5 101 3 104 0.971 20.2337407997021
ZA 7 105 1 106 0.991 45.179456823277
CN 8 15 99 114 0.132 279.013960302922
RO 6 117 1 118 0.992 9.94185377985744
TR 7 72 47 119 0.605 157.624322742975
AR 5 122 0 122 1 31.4664486509855
PL 10 129 0 129 1 14.9105978381726
VN 4 139 0 139 1 46.1903619354577
BG 10 131 28 159 0.824 65.7701103792251
CY 4 167 0 167 1 20.1104313713348
KE 10 155 15 170 0.912 46.653004175074
MX 8 158 17 175 0.903 62.0246244403294
CZ 7 175 4 179 0.978 15.8450447593987
TW 4 179 4 183 0.978 30.6410040985691
BI 4 185 10 195 0.949 87.8951176765638
MA 1 217 3 220 0.986 20.9828590869904
NL 19 250 8 258 0.969 24.517403395601
PS 3 259 10 269 0.963 49.6592321821305
PK 2 269 2 271 0.993 38.2534011474835
SE 14 302 2 304 0.993 11.0447258870853
LU 3 308 8 316 0.975 31.9291122080405
FI 4 313 16 329 0.951 50.7862147009481
UG 9 310 30 340 0.912 88.0218840935651
HR 7 338 62 400 0.845 55.2237829226255
CH 10 409 6 415 0.986 18.1350024361208
TH 6 415 2 417 0.995 31.4130667610992
ID 7 417 17 434 0.961 69.4774774384389
CL 6 434 8 442 0.982 42.5864462269917
IN 25 447 5 452 0.989 31.1389041495534
IR 9 449 41 490 0.916 69.3321947701123
KR 5 441 65 506 0.872 95.4878729661934
EG 7 121 406 527 0.23 240.133498995309
AU 12 536 14 550 0.975 37.8669759108803
CA 25 533 26 559 0.953 34.2257367033438
MY 5 672 5 677 0.993 32.3634027434593
ES 15 706 18 724 0.975 28.7121319342713
GR 4 763 19 782 0.976 44.3224103139794
NO 15 858 6 864 0.993 24.7879442556037
IS 4 867 4 871 0.995 20.0993553149577
GB 23 976 36 1012 0.964 24.8460943329004
BR 18 969 128 1097 0.883 89.9818406744012
SI 5 1394 1 1395 0.999 7.25013194186713
ZZ 10 1448 36 1484 0.976 23.383094335984
DE 27 1705 27 1732 0.984 19.5800073928701
IT 23 2675 107 2782 0.962 26.8166046033739
FR 38 3206 43 3249 0.987 18.8838687056797
US 122 4664 158 4822 0.967 29.7194833433119
RU 53 4787 54 4841 0.989 17.6032174229991
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment