I am doing a query on cities population and would like to calculate the percentage of city population with respect to their counties. But there are multiple populations for each city due to the year, therefore would like to take the latest version.
#List of countries ordered by the number of their cities with female mayor
#added before 2016-10
SELECT ?country ?countryLabel ?countryPopulation ?city ?cityLabel ?cityPopulation (?cityPopulation / ?countryPopulation AS ?ratio) #(year(?date) as ?dateyear)
WHERE
{
?city wdt:P31/wdt:P279* wd:Q515 . # find instances of subclasses of city
?city wdt:P17 ?country . # Also find the country of the city
?city wdt:P1082 ?cityPopulation . # city population
?country wdt:P1082 ?countryPopulation .
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
}
}
#ORDER BY DESC(?count)
LIMIT 100
For example here I get multiple entries with different values corresponding to the population of wd:Q9954
** UPDATE
Based on my understanding from a similar question here I tried to update my code - but still, I am not confident whether I am forming my query correctly. Also, I have to add that, I would like to calculate the city_population / country_population
which even after filtering doesn't seem to hold. I appreciate your comments.
SELECT ?country ?countryLabel ?countryPopulation ?city ?cityLabel ?cityPopulation ?ciy_date
WHERE
{
#?city wdt:P31/wdt:P279* wd:Q515 .
#?city wdt:P1082 ?cityPopulation .
#?country wdt:P1082 ?countryPopulation .
?city wdt:P31/wdt:P279* wd:Q515 .
?city p:P1082 ?populationStatement .
?populationStatement ps:P1082 ?cityPopulation;
pq:P585 ?ciy_date .
?city wdt:P17 ?country .
?country wdt:P1082 ?countryPopulation .
FILTER NOT EXISTS {
?country p:P1081/pq:P585 ?hdi_country_date_ .
?city p:P1082/pq:P585 ?hdi_city_date_ .
FILTER (?hdi_city_date_ > ?ciy_date)
FILTER (?hdi_country_date_ > ?hdi_country_date_)
}
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
}
}
#ORDER BY DESC(?count)
LIMIT 100