Query: Safe n' Sunny Index by jsoares

Loading chart...

1SELECT 
2  crime_t.city, 
3  crime_t.safety_index, 
4  sunshine_t.year / sunshine_max.year * 100 AS sunshine_index, 
5  crime_t.safety_index + sunshine_index AS safe_and_sunny_index,
6  RANK() OVER (ORDER BY safe_and_sunny_index DESC) AS rank
7FROM 
8  @kaggle.ahmadjalalmasood123_world_crime_index.world_crime_index crime_t,
9  @kaggle.prasertk_sunshine_duration_by_city.sunshine_hours_by_city sunshine_t,
10  (SELECT MAX("year") AS "year" FROM @kaggle.prasertk_sunshine_duration_by_city.sunshine_hours_by_city) sunshine_max
11WHERE 
12    crime_t.city = (sunshine_t.city || ', ' || sunshine_t.country)
13ORDER 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