query-abde6db42abc300eaad27ac7a38092e2

rq turtle/ttl

most common election day per country SELECT ?countryLabel ?weekday ?count ?totalCount ?percentage WITH { # count election “weekdays” (but see below) for each country SELECT ?country ?offset (COUNT(DISTINCT ?election) AS ?count) WHERE { ?election wdt:P31/wdt:P279* wd:Q40231; wdt:P17 ?country; p:P585/psv:P585 [ wikibase:timeValue ?date; wikibase:timePrecision ?precision ]. FILTER(?precision >= "11"^^xsd:integer) MINUS { ?country wdt:P156|wdt:P576|wdt:P582 ?end. } # calculate the weekday as the offset from July 4th, 1776 (arbitrary choice) modulo 7 # SPARQL has no modulo operator, so this gets finicky BIND(?date - "1776-07-04"^^xsd:dateTime AS ?offsetInt) BIND(?offsetInt - (7 * xsd:integer(xsd:float(?offsetInt) / 7.0)) AS ?offsetSigned) BIND(IF(?offsetSigned < 0, ?offsetSigned + 7, ?offsetSigned) AS ?offset) } GROUP BY ?country ?offset } AS %offsets WITH { SELECT ?country ?weekday ?count WHERE { INCLUDE %offsets. # translate offset into weekday (July 4th, 1776 was a Thursday) # SPARQL has no switch/case/match syntax, so this is also finicky BIND(IF(?offset = 0, "Thursday"@en, IF(?offset = 1, "Friday"@en, IF(?offset = 2, "Saturday"@en, IF(?offset = 3, "Sunday"@en, IF(?offset = 4, "Monday"@en, IF(?offset = 5, "Tuesday"@en, IF(?offset = 6, "Wednesday"@en, "unknown day"@en))))))) AS ?weekday) } } AS %weekdays WITH { # get highest count for each country (i. e. the count for the most common weekday) SELECT ?country (MAX(?count) AS ?maxCount) (SUM(?count) AS ?totalCount) WHERE { INCLUDE %weekdays. } GROUP BY ?country HAVING(?totalCount >= 3) # filter out countries with not enough data (3 is a very generous limit) } AS %maxWeekdayCount WITH { # get weekday whose count matches the highest count for each country (i. e. the most common weekday) # (if more than one weekday have the same count, pick any one) SELECT ?country (SAMPLE(?weekday) AS ?weekday) ?count ?totalCount WHERE { INCLUDE %weekdays. INCLUDE %maxWeekdayCount. FILTER(?count = ?maxCount) } GROUP BY ?country ?count ?totalCount } AS %maxWeekday WHERE { # prettify INCLUDE %maxWeekday. BIND(?count / ?totalCount AS ?ratio) BIND(CONCAT(SUBSTR(STR(100 * ?ratio), 1, 5), "%") AS ?percentage) SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } } ORDER BY DESC(?totalCount)

Use at

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX psv: <http://www.wikidata.org/prop/statement/value/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
# most common election day per country
SELECT ?countryLabel ?weekday ?count ?totalCount ?percentage
WHERE {
  # prettify
   {
  # get weekday whose count matches the highest count for each country (i. e. the most common weekday)
  # (if more than one weekday have the same count, pick any one)
  SELECT ?country (SAMPLE(?weekday) AS ?weekday) ?count ?totalCount WHERE {
     {
  SELECT ?country ?weekday ?count WHERE {
    INCLUDE %offsets.
    # translate offset into weekday (July 4th, 1776 was a Thursday)
    # SPARQL has no switch/case/match syntax, so this is also finicky
    BIND(IF(?offset = 0, "Thursday"@en,
            IF(?offset = 1, "Friday"@en,
               IF(?offset = 2, "Saturday"@en,
                  IF(?offset = 3, "Sunday"@en,
                     IF(?offset = 4, "Monday"@en,
                        IF(?offset = 5, "Tuesday"@en,
                           IF(?offset = 6, "Wednesday"@en,
                              "unknown day"@en)))))))
         AS ?weekday)
  }
}
     {
  # get highest count for each country (i. e. the count for the most common weekday)
  SELECT ?country (MAX(?count) AS ?maxCount) (SUM(?count) AS ?totalCount) WHERE {
    INCLUDE %weekdays.
  }
  GROUP BY ?country
  HAVING(?totalCount >= 3) # filter out countries with not enough data (3 is a very generous limit)
}
    FILTER(?count = ?maxCount)
  }
  GROUP BY ?country ?count ?totalCount
}
  BIND(?count / ?totalCount AS ?ratio)
  BIND(CONCAT(SUBSTR(STR(100 * ?ratio), 1, 5), "%") AS ?percentage)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(?totalCount)

Query found at