Baselight
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
citysafety_indexsunshine_indexsafe_and_sunny_indexrank
Muscat, Oman79.4686.99972604786691166.459726047866921
Riyadh, Saudi Arabia73.6980.3128035265111154.002803526511122
Nicosia, Cyprus70.1382.53679675242198152.6667967524223
Alexandria, Egypt59.7689.1465145817249148.90651458172494
Canberra, Australia77.6970.07446517072198147.764465170721965
Lisbon, Portugal70.8369.88269867755834140.712698677558346
Yerevan, Armenia78.3461.61432520608672139.95432520608677
Lahore, Pakistan64.2375.5609792543521139.790979254352128
Valencia, Spain72.3267.14317734664907139.463177346649059
Madrid, Spain69.9468.96122332079796138.9012233207979510
Adelaide, Australia70.0268.87156625905911138.8915662590591211
Brisbane, Australia64.9573.9272283515553138.877228351555312
Perth, Australia58.3980.42985580155903138.8198558015590213
Chiang Mai, Thailand76.0962.565686250093385138.655686250093414
Cairo, Egypt50.3288.20760590740417138.5276059074041715
Rabat, Morocco63.8372.69195327870894136.5219532787089316
Tashkent, Uzbekistan66.1470.32849351231539136.4684935123153817
Beijing, China67.5666.51557791447713134.0755779144771518
Lugano, Switzerland80.5252.79804746843324133.3180474684332419
Split, Croatia66.3865.52436928747541131.904369287475420
Cagliari, Italy63.6367.89031952780614131.5203195278061521
Chennai, India59.5570.94115010086419130.491150100864222
Gaborone, Botswana44.3383.95387642268324128.2838764226832523
Ljubljana, Slovenia78.5249.16195552013548127.6819555201354724
Sydney, Australia66.1961.467387243792494127.657387243792525
Hobart, Australia67.8259.5995317908998127.419531790899826
Zagreb, Croatia78.6447.642766418449426126.2827664184494327
Porto, Portugal64.6561.4648967698553126.114896769855328
Seoul, South Korea74.6251.45319154235051126.0731915423505129
Tbilisi, Georgia74.8450.955096754912454125.7950967549124630
Baku, Azerbaijan69.6454.97472168953752124.6147216895375231
Bucharest, Romania71.7552.67352377157373124.4235237715737332
Bangkok, Thailand59.0265.34505516399771124.3650551639977133
Tallinn, Estonia76.4147.89181381216846124.3018138121684634
Singapore, Singapore72.7850.36734490573556123.1473449057355735
Tokyo, Japan76.2146.73872437924937122.9487243792493736
Calgary, Canada63.1859.6792269568899122.859226956889937
Tunis, Tunisia52.8669.94247005205091122.8024700520509138
Zurich, Switzerland82.7439.00082185639927121.7408218563992639
Ankara, Turkey60.5961.006649565412296121.5966495654122940
Baghdad, Iraq40.8780.71127935646155121.5812793564615541
Helsinki, Finland75.0846.27300575299479121.3530057529947942
Windhoek, Namibia31.3689.78158543570841121.1415854357084143
Jakarta, Indonesia46.774.29830896819665120.9983089681966544
Copenhagen, Denmark73.3347.61786167907753120.9478616790775345
Bratislava, Slovakia70.1150.75585883993724120.8658588399372346
Wellington, New Zealand69.3951.271386944935614120.6613869449356147
Geneva, Switzerland73.3946.99524319477996120.3852431947799648
Montreal, Canada69.0151.079620451771966120.0896204517719649
Vienna, Austria73.1546.92052897666426120.0705289766642650
Taipei, Taiwan84.9534.99613976539735119.9461397653973651
Karachi, Pakistan45.973.47645256892386119.3764525689238752
Mumbai, India54.7264.34139416731004119.0613941673100453
Tirana, Albania55.563.35765696211989118.8576569621198954
Darwin, Australia41.5377.01043508579683118.5404350857968355
Prague, Czech Republic75.941.541105272333326117.4411052723333356
Tripoli, Libya37.7179.37140437825317117.0814043782531857
Algiers, Algeria46.1270.90379299180634117.0237929918063558
Barcelona, Spain51.1364.52817971259931115.6581797125993259
Vilnius, Lithuania73.4942.11391427788708115.6039142778870760
Tehran, Iran44.2870.38328393893357114.6632839389335761
Harare, Zimbabwe39.6574.98567977486115114.6356797748611662
Belgrade, Serbia61.9952.598809553458025114.5888095534580263
Podgorica, Montenegro52.861.788658381690034114.5886583816900464
Budapest, Hungary64.9449.51062187134212114.4506218713421265
Melbourne, Australia55.6158.839937240056784114.4499372400567866
Ulaanbaatar, Mongolia44.6669.52157995666575114.1815799566657467
Edmonton, Canada55.4458.39663287923692113.8366328792369268
Accra, Ghana53.1260.56832615246681113.688326152466869
Athens, Greece43.5869.06084227828555112.6408422782855570
Sofia, Bulgaria57.8754.217617612631685112.0876176126316871
Warsaw, Poland72.739.12534555325878111.8253455532587972
Skopje, North Macedonia53.2458.25218539087989111.4921853908798873
Bloemfontein, South Africa28.6882.49196822155255111.1719682215525674
Shanghai, China66.5944.22583617662441110.8158361766244275
Christchurch, New Zealand58.8551.557791447712496110.407791447712576
Santiago, Chile40.269.93001768236495110.1300176823649577
Moscow, Russia62.7547.3439095459866110.093909545986678
Vancouver, Canada61.7548.25542300699823110.0054230069982379
Reykjavik, Iceland76.9833.02368440714268110.0036844071426980
Toronto, Canada58.4851.463153438099276109.9431534380992781
Monterrey, Mexico52.855.761711453689635108.5617114536896482
Rome, Italy46.8961.58942046671482108.4794204667148383
Ho Chi Minh City, Vietnam46.3961.987896296665255108.3778962966652684
Amsterdam, Netherlands66.9441.39167683610191108.331676836101985
Marseille, France37.4970.62984085871541108.119840858715486
Delhi, India40.8566.85926331780936107.7092633178093687
Istanbul, Turkey52.3855.23871192687968107.6187119268796888
Oslo, Norway65.9441.541105272333326107.4811052723333289
Montevideo, Uruguay45.1961.7986202774388106.988620277438890
Kolkata, India53.3252.48673822628446105.8067382262844791
Riga, Latvia62.0943.68291285831694105.7729128583169492
San Juan, Puerto Rico31.4573.81266655044455105.2626665504445593
Edinburgh, United Kingdom69.4935.539063083704825105.0290630837048194
Guangzhou, China63.5940.54491569745722104.1349156974572295
Bangalore, India45.1958.80008965706174103.9900896570617396
Cape Town, South Africa26.8777.05526361666625103.9252636166662697
San Salvador, El Salvador30.2173.64331432271561103.8533143227156298
Auckland, New Zealand53.2549.88668343585784103.1366834358578399
Nairobi, Kenya40.9762.06012004084377103.03012004084377100
Bergen, Norway72.829.561925634448233102.36192563444823101
Hanoi, Vietnam61.8839.474011904465414101.35401190446541102
Milan, Italy53.1647.69257589719324100.85257589719323103
Kabul, Afghanistan20.6179.0750379797275499.68503797972754104
Winnipeg, Canada41.0758.59836126814932699.66836126814933105
Buenos Aires, Argentina36.6762.8894478619281299.55944786192812106
Naples, Italy40.0459.14875600826836499.18875600826837107
Stockholm, Sweden54.0344.9032450875401698.93324508754016108
Lyon, France48.7149.8592882225487598.56928822254875109
Berlin, Germany57.7640.4951062187134298.25510621871342110
Pretoria, South Africa18.0280.1932607775259598.21326077752595111
Frankfurt, Germany55.8841.3916768361019197.2716768361019112
Johannesburg, South Africa19.4577.8123676935720997.26236769357209113
Belo Horizonte, Brazil36.4660.3865215550519296.84652155505192114
Gothenburg, Sweden52.6643.8821507732921596.54215077329215115
Panama City, Panama52.8443.4214130949119696.26141309491197116
Quito, Ecuador39.0855.7368067143177394.81680671431772117
Mexico City, Mexico31.1463.6316090952108294.77160909521082118
Almaty, Kazakhstan35.8358.6755659602022394.50556596020223119
Fortaleza, Brazil22.6469.8378701466889192.47787014668891120
Luanda, Angola32.5558.30199486962368490.85199486962368121
Dhaka, Bangladesh36.3451.4531915423505187.79319154235051122
Manila, Philippines35.2852.3771573730480987.6571573730481123
London, United Kingdom46.7140.6694393943167487.37943939431673124
Recife, Brazil23.5863.1708714168306286.75087141683062125
Minsk, Belarus41.2845.0028640450277786.28286404502776126
Brussels, Belgium47.5938.5027270689612286.09272706896122127
Paris, France43.9841.3916768361019185.3716768361019128
Salvador, Brazil24.3160.8422782855577485.15227828555774129
Dublin, Ireland48.0936.1865863073742984.27658630737429130
Curitiba, Brazil37.8145.9442631932856883.75426319328568131
Port Moresby, Papua New Guinea19.2961.3403730729957980.63037307299578132
Lagos, Nigeria33.7845.95920603690882479.73920603690883133
Porto Alegre, Brazil27.0852.33481931611585579.41481931611585134
Cali, Colombia30.548.36251338629741578.86251338629742135
Caracas, Venezuela16.0262.4336911314223178.4536911314223136
Durban, South Africa19.458.90967051029810678.3096705102981137
Rio de Janeiro, Brazil22.0754.4741364281622876.54413642816229138
Guayaquil, Ecuador29.4139.37688342091568.786883420915139
Lima, Peru29.330.6328294274400459.93282942744004140

Share link

Anyone who has the link will be able to view this.