Get Wikidata Dump with countries and attributes

Question

I'm trying to write a SPARQL-query for wikidata, returning all European countries with their most recent properties (population, area, average income, age of retirement and human development index). One country should fill one line because I would like to cluster this data later.

I wrote the following query but it didn't work as expected.

SELECT ?countryLabel ?population ?populationDate ?area ?areaDate ?income ?incomeDate ?retirementAge ?retirementAgeDate ?hdi ?hdiDate WHERE {
  ?country wdt:P31 wd:Q6256.
  OPTIONAL {
    SELECT ?country (MAX(?popDate) AS ?latestPopDate) ?population WHERE {
      ?country p:P1082 ?popStmt.
      ?popStmt ps:P1082 ?population.
      ?popStmt pq:P585 ?popDate.
    } GROUP BY ?country ?population
  }
  OPTIONAL {
    SELECT ?country (MAX(?areaDate) AS ?latestAreaDate) ?area WHERE {
      ?country p:P2046 ?areaStmt.
      ?areaStmt ps:P2046 ?area.
      ?areaStmt pq:P585 ?areaDate.
    } GROUP BY ?country ?area
  }
  OPTIONAL {
    SELECT ?country (MAX(?incomeDate) AS ?latestIncomeDate) ?income WHERE {
      ?country p:P3529 ?incomeStmt.
      ?incomeStmt ps:P3529 ?income.
      ?incomeStmt pq:P585 ?incomeDate.
    } GROUP BY ?country ?income
  }
  OPTIONAL {
    SELECT ?country (MAX(?retirementAgeDate) AS ?latestRetirementAgeDate) ?retirementAge WHERE {
      ?country p:P8772 ?retirementAgeStmt.
      ?retirementAgeStmt ps:P8772 ?retirementAge.
      ?retirementAgeStmt pq:P585 ?retirementAgeDate.
    } GROUP BY ?country ?retirementAge
  }
  OPTIONAL {
    SELECT ?country (MAX(?hdiDate) AS ?latestHdiDate) ?hdi WHERE {
      ?country p:P1081 ?hdiStmt.
      ?hdiStmt ps:P1081 ?hdi.
      ?hdiStmt pq:P585 ?hdiDate.
    } GROUP BY ?country ?hdi
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ?countryLabel

How would you write such a query?

Answer 1

You can get some of the geographical data (e.g. area) easily from geonames.org. This list comprises all world countries though.

Perhaps join that list with this query to get the current list of 47 souvereign states in Europe, according to Wikidata, https://w.wiki/6nnb,

PREFIX wd:       <http://www.wikidata.org/entity/>
PREFIX wdt:      <http://www.wikidata.org/prop/direct/>
PREFIX p:        <http://www.wikidata.org/prop/>
PREFIX ps:       <http://www.wikidata.org/prop/statement/>
PREFIX pq:       <http://www.wikidata.org/prop/qualifier/>
PREFIX rdfs:     <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd:      <http://www.w3.org/2001/XMLSchema#>
PREFIX bd:       <http://www.bigdata.com/rdf#>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX schema:   <http://schema.org/>

SELECT ?country ?countryQ ?countryLabel WHERE {
  ?country wdt:P31 wd:Q3624078;
    wdt:P30 wd:Q46.
  BIND (xsd:integer(REPLACE(STR(?country), "http://www.wikidata.org/entity/Q", "" )) as ?countryQ)
  FILTER(?countryQ < 2000) # filter "Kingdom of " duplicates; UK constituent countries, disputed territories, etc.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ASC(?countryQ) ASC( ?countryLabel)

Does this list fit your needs or do you define Eurpe differently (e.g. only Western European countries)?

As for retirement age, you proposed query fragment ?country p:P8772 ?retirementAgeStmt. ... makes no sense to me. p:P8772 is "has blogger id". (?)

I suggest that you search some specialized databases such as data.europa.eu. It also has a (difficult to use) SPARQL interface.
Retirement ages can be found there via classic search forms, though, example: Average age at which not employed persons started receiving a retirement pension - by sex and main reason for retirement or early retirement , textfile.