query-79efce7e369eb7fdd4038602f95f55e3

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX bd: <http://www.bigdata.com/rdf#>
select distinct (group_concat(?contLabel) as ?cc) ?country ?countryLabel ?country_pop ?city ?cityLabel ?isitcapital ?city_pop ?ratio where
{
{ select distinct ?continent ?country ?countryLabel ?country_pop ?city ?cityLabel ?isitcapital ?city_pop ?ratio where 
{ 
  { select ?country (max(?city_pop) AS ?city_pop) (max(?country_pop) as ?country_pop) 
           {
{ select * 
 { ?city wdt:P31/wdt:P279* wd:Q515 ; 
         wdt:P17 ?country ; 
         wdt:P1082 ?city_pop . 
  ?country wdt:P1082 ?country_pop } }} group by ?country
  }

{ select * 
 { ?city wdt:P31/wdt:P279* wd:Q515 ; 
         wdt:P17 ?country ; 
         wdt:P1082 ?city_pop . 
  ?country wdt:P1082 ?country_pop } }
  ?country wdt:P31 wd:Q3624078 .
  #not a former country
  FILTER NOT EXISTS {?country wdt:P31 wd:Q3024240}
  #and no an ancient civilisation (needed to exclude ancient Egypt)
  FILTER NOT EXISTS {?country wdt:P31 wd:Q28171280}
  OPTIONAL { ?country wdt:P36 ?capital } .
  bind(if(?capital=?city,"yes","no") as ?isitcapital)
  bind(((?city_pop*100) / ?country_pop) as ?ratio)
  ?country wdt:P30 ?continent . 
  service wikibase:label {bd:serviceParam wikibase:language 'en'}
} order by desc(?city_pop) } ?continent rdfs:label ?contLabel . filter(lang(?contLabel)="en")
} group by ?country ?countryLabel ?country_pop ?city ?cityLabel ?isitcapital ?city_pop ?ratio

graph TD classDef projected fill:lightgreen; classDef literal fill:orange; classDef iri fill:yellow; v6("?capital") v10("?cc") v4("?city"):::projected v6("?city_pop"):::projected v1("?contLabel"):::projected v9("?continent") v3("?country"):::projected v6("?country_pop"):::projected v7("?isitcapital"):::projected v8("?ratio"):::projected a1((" ")) a2((" ")) c3(["wd:Q28171280"]):::iri c1(["en"]):::literal c4(["wd:Q3024240"]):::iri c9(["wd:Q3624078"]):::iri c13(["bd:serviceParam"]):::iri c6(["wd:Q515"]):::iri f0[["?contLabel = 'en'"]] f0 --> v1 f1[["not "]] subgraph f1e0["Exists Clause"] e0v1 --"wdt:P31"--> e0c2 e0v1("?country"):::projected e0c2(["wd:Q28171280"]):::iri end f1--EXISTS--> f1e0 f1 --> v3 f1 --> c2 f1 --> c3 v3 --"wdt:P31"--> c3 f2[["not "]] subgraph f2e1["Exists Clause"] e1v1 --"wdt:P31"--> e1c2 e1v1("?country"):::projected e1c2(["wd:Q3024240"]):::iri end f2--EXISTS--> f2e1 f2 --> v3 f2 --> c2 f2 --> c4 v3 --"wdt:P31"--> c4 v4 --"wdt:P31"--> a1 a1 --"wdt:P279"--> c6 v4 --"wdt:P17"--> v3 v4 --"wdt:P1082"--> v6 v3 --"wdt:P1082"--> v6 bind5[/"max(?city_pop)"/] v6 --o bind5 bind5 --as--o v6 bind6[/"max(?country_pop)"/] v6 --o bind6 bind6 --as--o v6 v4 --"wdt:P31"--> a2 a2 --"wdt:P279"--> c6 v4 --"wdt:P17"--> v3 v4 --"wdt:P1082"--> v6 v3 --"wdt:P1082"--> v6 v3 --"wdt:P31"--> c9 subgraph optional0["(optional)"] style optional0 fill:#bbf,stroke-dasharray: 5 5; v3 -."wdt:P36".-> v6 end bind7[/"if(?capital = ?city,'yes','no')"/] v6 --o bind7 v4 --o bind7 bind7 --as--o v7 bind8[/"?city_pop * '100^^xsd:integer' / ?country_pop"/] v6 --o bind8 v6 --o bind8 bind8 --as--o v8 v3 --"wdt:P30"--> v9 subgraph s1["http://wikiba.se/ontology#label"] style s1 stroke-width:4px; c13 --"wikibase:language"--> c1 end v9 --"rdfs:label"--> v1 bind10[/"?contLabel"/] v1 --o bind10 bind10 --as--o v10