SELECT
crime_t.city,
crime_t.safety_index,
sunshine_t.year / sunshine_max.year * 100 AS sunshine_index,
crime_t.safety_index + sunshine_index AS safe_and_sunny_index,
RANK() OVER (ORDER BY safe_and_sunny_index DESC) AS rank
FROM
@kaggle.ahmadjalalmasood123_world_crime_index.world_crime_index crime_t,
@kaggle.prasertk_sunshine_duration_by_city.sunshine_hours_by_city sunshine_t,
(SELECT MAX("year") AS "year" FROM @kaggle.prasertk_sunshine_duration_by_city.sunshine_hours_by_city) sunshine_max
WHERE
crime_t.city = (sunshine_t.city || ', ' || sunshine_t.country)
ORDER BY safe_and_sunny_index DESC
city | safety_index | sunshine_index | safe_and_sunny_index | rank |
---|---|---|---|---|
Muscat, Oman | 79.46 | 86.99972604786691 | 166.45972604786692 | 1 |
Riyadh, Saudi Arabia | 73.69 | 80.3128035265111 | 154.00280352651112 | 2 |
Nicosia, Cyprus | 70.13 | 82.53679675242198 | 152.666796752422 | 3 |
Alexandria, Egypt | 59.76 | 89.1465145817249 | 148.9065145817249 | 4 |
Canberra, Australia | 77.69 | 70.07446517072198 | 147.76446517072196 | 5 |
Lisbon, Portugal | 70.83 | 69.88269867755834 | 140.71269867755834 | 6 |
Yerevan, Armenia | 78.34 | 61.61432520608672 | 139.9543252060867 | 7 |
Lahore, Pakistan | 64.23 | 75.5609792543521 | 139.79097925435212 | 8 |
Valencia, Spain | 72.32 | 67.14317734664907 | 139.46317734664905 | 9 |
Madrid, Spain | 69.94 | 68.96122332079796 | 138.90122332079795 | 10 |
Adelaide, Australia | 70.02 | 68.87156625905911 | 138.89156625905912 | 11 |
Brisbane, Australia | 64.95 | 73.9272283515553 | 138.8772283515553 | 12 |
Perth, Australia | 58.39 | 80.42985580155903 | 138.81985580155902 | 13 |
Chiang Mai, Thailand | 76.09 | 62.565686250093385 | 138.6556862500934 | 14 |
Cairo, Egypt | 50.32 | 88.20760590740417 | 138.52760590740417 | 15 |
Rabat, Morocco | 63.83 | 72.69195327870894 | 136.52195327870893 | 16 |
Tashkent, Uzbekistan | 66.14 | 70.32849351231539 | 136.46849351231538 | 17 |
Beijing, China | 67.56 | 66.51557791447713 | 134.07557791447715 | 18 |
Lugano, Switzerland | 80.52 | 52.79804746843324 | 133.31804746843324 | 19 |
Split, Croatia | 66.38 | 65.52436928747541 | 131.9043692874754 | 20 |
Cagliari, Italy | 63.63 | 67.89031952780614 | 131.52031952780615 | 21 |
Chennai, India | 59.55 | 70.94115010086419 | 130.4911501008642 | 22 |
Gaborone, Botswana | 44.33 | 83.95387642268324 | 128.28387642268325 | 23 |
Ljubljana, Slovenia | 78.52 | 49.16195552013548 | 127.68195552013547 | 24 |
Sydney, Australia | 66.19 | 61.467387243792494 | 127.6573872437925 | 25 |
Hobart, Australia | 67.82 | 59.5995317908998 | 127.4195317908998 | 26 |
Zagreb, Croatia | 78.64 | 47.642766418449426 | 126.28276641844943 | 27 |
Porto, Portugal | 64.65 | 61.4648967698553 | 126.1148967698553 | 28 |
Seoul, South Korea | 74.62 | 51.45319154235051 | 126.07319154235051 | 29 |
Tbilisi, Georgia | 74.84 | 50.955096754912454 | 125.79509675491246 | 30 |
Baku, Azerbaijan | 69.64 | 54.97472168953752 | 124.61472168953752 | 31 |
Bucharest, Romania | 71.75 | 52.67352377157373 | 124.42352377157373 | 32 |
Bangkok, Thailand | 59.02 | 65.34505516399771 | 124.36505516399771 | 33 |
Tallinn, Estonia | 76.41 | 47.89181381216846 | 124.30181381216846 | 34 |
Singapore, Singapore | 72.78 | 50.36734490573556 | 123.14734490573557 | 35 |
Tokyo, Japan | 76.21 | 46.73872437924937 | 122.94872437924937 | 36 |
Calgary, Canada | 63.18 | 59.6792269568899 | 122.8592269568899 | 37 |
Tunis, Tunisia | 52.86 | 69.94247005205091 | 122.80247005205091 | 38 |
Zurich, Switzerland | 82.74 | 39.00082185639927 | 121.74082185639926 | 39 |
Ankara, Turkey | 60.59 | 61.006649565412296 | 121.59664956541229 | 40 |
Baghdad, Iraq | 40.87 | 80.71127935646155 | 121.58127935646155 | 41 |
Helsinki, Finland | 75.08 | 46.27300575299479 | 121.35300575299479 | 42 |
Windhoek, Namibia | 31.36 | 89.78158543570841 | 121.14158543570841 | 43 |
Jakarta, Indonesia | 46.7 | 74.29830896819665 | 120.99830896819665 | 44 |
Copenhagen, Denmark | 73.33 | 47.61786167907753 | 120.94786167907753 | 45 |
Bratislava, Slovakia | 70.11 | 50.75585883993724 | 120.86585883993723 | 46 |
Wellington, New Zealand | 69.39 | 51.271386944935614 | 120.66138694493561 | 47 |
Geneva, Switzerland | 73.39 | 46.99524319477996 | 120.38524319477996 | 48 |
Montreal, Canada | 69.01 | 51.079620451771966 | 120.08962045177196 | 49 |
Vienna, Austria | 73.15 | 46.92052897666426 | 120.07052897666426 | 50 |
Taipei, Taiwan | 84.95 | 34.99613976539735 | 119.94613976539736 | 51 |
Karachi, Pakistan | 45.9 | 73.47645256892386 | 119.37645256892387 | 52 |
Mumbai, India | 54.72 | 64.34139416731004 | 119.06139416731004 | 53 |
Tirana, Albania | 55.5 | 63.35765696211989 | 118.85765696211989 | 54 |
Darwin, Australia | 41.53 | 77.01043508579683 | 118.54043508579683 | 55 |
Prague, Czech Republic | 75.9 | 41.541105272333326 | 117.44110527233333 | 56 |
Tripoli, Libya | 37.71 | 79.37140437825317 | 117.08140437825318 | 57 |
Algiers, Algeria | 46.12 | 70.90379299180634 | 117.02379299180635 | 58 |
Barcelona, Spain | 51.13 | 64.52817971259931 | 115.65817971259932 | 59 |
Vilnius, Lithuania | 73.49 | 42.11391427788708 | 115.60391427788707 | 60 |
Tehran, Iran | 44.28 | 70.38328393893357 | 114.66328393893357 | 61 |
Harare, Zimbabwe | 39.65 | 74.98567977486115 | 114.63567977486116 | 62 |
Belgrade, Serbia | 61.99 | 52.598809553458025 | 114.58880955345802 | 63 |
Podgorica, Montenegro | 52.8 | 61.788658381690034 | 114.58865838169004 | 64 |
Budapest, Hungary | 64.94 | 49.51062187134212 | 114.45062187134212 | 65 |
Melbourne, Australia | 55.61 | 58.839937240056784 | 114.44993724005678 | 66 |
Ulaanbaatar, Mongolia | 44.66 | 69.52157995666575 | 114.18157995666574 | 67 |
Edmonton, Canada | 55.44 | 58.39663287923692 | 113.83663287923692 | 68 |
Accra, Ghana | 53.12 | 60.56832615246681 | 113.6883261524668 | 69 |
Athens, Greece | 43.58 | 69.06084227828555 | 112.64084227828555 | 70 |
Sofia, Bulgaria | 57.87 | 54.217617612631685 | 112.08761761263168 | 71 |
Warsaw, Poland | 72.7 | 39.12534555325878 | 111.82534555325879 | 72 |
Skopje, North Macedonia | 53.24 | 58.25218539087989 | 111.49218539087988 | 73 |
Bloemfontein, South Africa | 28.68 | 82.49196822155255 | 111.17196822155256 | 74 |
Shanghai, China | 66.59 | 44.22583617662441 | 110.81583617662442 | 75 |
Christchurch, New Zealand | 58.85 | 51.557791447712496 | 110.4077914477125 | 76 |
Santiago, Chile | 40.2 | 69.93001768236495 | 110.13001768236495 | 77 |
Moscow, Russia | 62.75 | 47.3439095459866 | 110.0939095459866 | 78 |
Vancouver, Canada | 61.75 | 48.25542300699823 | 110.00542300699823 | 79 |
Reykjavik, Iceland | 76.98 | 33.02368440714268 | 110.00368440714269 | 80 |
Toronto, Canada | 58.48 | 51.463153438099276 | 109.94315343809927 | 81 |
Monterrey, Mexico | 52.8 | 55.761711453689635 | 108.56171145368964 | 82 |
Rome, Italy | 46.89 | 61.58942046671482 | 108.47942046671483 | 83 |
Ho Chi Minh City, Vietnam | 46.39 | 61.987896296665255 | 108.37789629666526 | 84 |
Amsterdam, Netherlands | 66.94 | 41.39167683610191 | 108.3316768361019 | 85 |
Marseille, France | 37.49 | 70.62984085871541 | 108.1198408587154 | 86 |
Delhi, India | 40.85 | 66.85926331780936 | 107.70926331780936 | 87 |
Istanbul, Turkey | 52.38 | 55.23871192687968 | 107.61871192687968 | 88 |
Oslo, Norway | 65.94 | 41.541105272333326 | 107.48110527233332 | 89 |
Montevideo, Uruguay | 45.19 | 61.7986202774388 | 106.9886202774388 | 90 |
Kolkata, India | 53.32 | 52.48673822628446 | 105.80673822628447 | 91 |
Riga, Latvia | 62.09 | 43.68291285831694 | 105.77291285831694 | 92 |
San Juan, Puerto Rico | 31.45 | 73.81266655044455 | 105.26266655044455 | 93 |
Edinburgh, United Kingdom | 69.49 | 35.539063083704825 | 105.02906308370481 | 94 |
Guangzhou, China | 63.59 | 40.54491569745722 | 104.13491569745722 | 95 |
Bangalore, India | 45.19 | 58.80008965706174 | 103.99008965706173 | 96 |
Cape Town, South Africa | 26.87 | 77.05526361666625 | 103.92526361666626 | 97 |
San Salvador, El Salvador | 30.21 | 73.64331432271561 | 103.85331432271562 | 98 |
Auckland, New Zealand | 53.25 | 49.88668343585784 | 103.13668343585783 | 99 |
Nairobi, Kenya | 40.97 | 62.06012004084377 | 103.03012004084377 | 100 |
Bergen, Norway | 72.8 | 29.561925634448233 | 102.36192563444823 | 101 |
Hanoi, Vietnam | 61.88 | 39.474011904465414 | 101.35401190446541 | 102 |
Milan, Italy | 53.16 | 47.69257589719324 | 100.85257589719323 | 103 |
Kabul, Afghanistan | 20.61 | 79.07503797972754 | 99.68503797972754 | 104 |
Winnipeg, Canada | 41.07 | 58.598361268149326 | 99.66836126814933 | 105 |
Buenos Aires, Argentina | 36.67 | 62.88944786192812 | 99.55944786192812 | 106 |
Naples, Italy | 40.04 | 59.148756008268364 | 99.18875600826837 | 107 |
Stockholm, Sweden | 54.03 | 44.90324508754016 | 98.93324508754016 | 108 |
Lyon, France | 48.71 | 49.85928822254875 | 98.56928822254875 | 109 |
Berlin, Germany | 57.76 | 40.49510621871342 | 98.25510621871342 | 110 |
Pretoria, South Africa | 18.02 | 80.19326077752595 | 98.21326077752595 | 111 |
Frankfurt, Germany | 55.88 | 41.39167683610191 | 97.2716768361019 | 112 |
Johannesburg, South Africa | 19.45 | 77.81236769357209 | 97.26236769357209 | 113 |
Belo Horizonte, Brazil | 36.46 | 60.38652155505192 | 96.84652155505192 | 114 |
Gothenburg, Sweden | 52.66 | 43.88215077329215 | 96.54215077329215 | 115 |
Panama City, Panama | 52.84 | 43.42141309491196 | 96.26141309491197 | 116 |
Quito, Ecuador | 39.08 | 55.73680671431773 | 94.81680671431772 | 117 |
Mexico City, Mexico | 31.14 | 63.63160909521082 | 94.77160909521082 | 118 |
Almaty, Kazakhstan | 35.83 | 58.67556596020223 | 94.50556596020223 | 119 |
Fortaleza, Brazil | 22.64 | 69.83787014668891 | 92.47787014668891 | 120 |
Luanda, Angola | 32.55 | 58.301994869623684 | 90.85199486962368 | 121 |
Dhaka, Bangladesh | 36.34 | 51.45319154235051 | 87.79319154235051 | 122 |
Manila, Philippines | 35.28 | 52.37715737304809 | 87.6571573730481 | 123 |
London, United Kingdom | 46.71 | 40.66943939431674 | 87.37943939431673 | 124 |
Recife, Brazil | 23.58 | 63.17087141683062 | 86.75087141683062 | 125 |
Minsk, Belarus | 41.28 | 45.00286404502777 | 86.28286404502776 | 126 |
Brussels, Belgium | 47.59 | 38.50272706896122 | 86.09272706896122 | 127 |
Paris, France | 43.98 | 41.39167683610191 | 85.3716768361019 | 128 |
Salvador, Brazil | 24.31 | 60.84227828555774 | 85.15227828555774 | 129 |
Dublin, Ireland | 48.09 | 36.18658630737429 | 84.27658630737429 | 130 |
Curitiba, Brazil | 37.81 | 45.94426319328568 | 83.75426319328568 | 131 |
Port Moresby, Papua New Guinea | 19.29 | 61.34037307299579 | 80.63037307299578 | 132 |
Lagos, Nigeria | 33.78 | 45.959206036908824 | 79.73920603690883 | 133 |
Porto Alegre, Brazil | 27.08 | 52.334819316115855 | 79.41481931611585 | 134 |
Cali, Colombia | 30.5 | 48.362513386297415 | 78.86251338629742 | 135 |
Caracas, Venezuela | 16.02 | 62.43369113142231 | 78.4536911314223 | 136 |
Durban, South Africa | 19.4 | 58.909670510298106 | 78.3096705102981 | 137 |
Rio de Janeiro, Brazil | 22.07 | 54.47413642816228 | 76.54413642816229 | 138 |
Guayaquil, Ecuador | 29.41 | 39.376883420915 | 68.786883420915 | 139 |
Lima, Peru | 29.3 | 30.63282942744004 | 59.93282942744004 | 140 |