Wikidata:WikiProject Video games/Queries

From Wikidata
Jump to navigation Jump to search
Home

Video game-related SPARQL queries.

Series with the most games[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, part of the series (P179)  View with Reasonator View with SQID
    SELECT ?series (SAMPLE(?label) AS ?label) (COUNT(?game) AS ?count)
    WHERE
    {
      ?game wdt:P31 wd:Q7889;
              wdt:P179 ?series.
      ?series rdfs:label ?label.
      FILTER(LANG(?label) = "en").
    }
    GROUP BY ?series
    ORDER BY DESC(?count)
    

Upcoming video games[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, publication date (P577)  View with Reasonator View with SQID
    SELECT ?item ?itemLabel ?date WHERE {
       ?item wdt:P31 wd:Q7889 .
       ?item wdt:P577 ?date .
       FILTER(?date > NOW()).
       SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    

Recent video-game-related items with sitelinks and external identifiers[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, formatter URL (P1630)  View with Reasonator View with SQID, subject item of this property (P1629)  View with Reasonator View with SQID
    SELECT ?item ?qid (GROUP_CONCAT( DISTINCT ?sitelink) as ?sitelinks) (GROUP_CONCAT( DISTINCT ?identifier) as ?identifiers) {
      VALUES ?gametype {wd:Q7889 wd:Q16070115 wd:Q7058673} .
      ?item wdt:P31 ?gametype .
      BIND (xsd:integer(STRAFTER(str(?item), "Q")) AS ?qid)
      FILTER (?qid > 51090000) . 
      OPTIONAL { 
        # Get sitelinks
        ?s schema:about ?item ; schema:inLanguage ?lang . 
        BIND(CONCAT("[", str(?s), " ", ?lang, "]") as ?sitelink)
        
        # Get external identifiers
        ?item ?prop ?id.
        ?propItem wikibase:directClaim ?prop.
        ?propItem wikibase:propertyType wikibase:ExternalId.
        ?propItem wdt:P31 wd:Q28147643 .
        ?propItem wdt:P1630 ?formatterurl . 
        ?propItem wdt:P1629 ?website .
        ?propItem rdfs:label ?propItemLabel .
        FILTER (lang(?propItemLabel) = "en"). 
        BIND(IRI(REPLACE(?id, '^(.+)$', ?formatterurl)) AS ?url).
        BIND(CONCAT("[", str(?url), " ", ?propItemLabel, "]") as ?identifier)
      } 
    } GROUP BY ?item ?qid ORDER BY DESC(?qid) LIMIT 50
    

Video game characters named after humans[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, named after (P138)  View with Reasonator View with SQID
    SELECT DISTINCT ?item ?itemLabel ?named ?namedLabel
    WHERE 
    {
      ?item wdt:P31/wdt:P279* wd:Q1569167.
      ?item wdt:P138 ?named. 
      ?named wdt:P31 wd:Q5.
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    

source

Films based on video games[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, based on (P144)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID
    SELECT ?item ?itemLabel ?videogame ?videogameLabel WHERE {
      ?item wdt:P31 wd:Q11424.
      ?item wdt:P144 ?videogame.
      ?videogame (wdt:P31/wdt:P279*) wd:Q7889.
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    

Characters from the Mario franchise by games in which they appear[edit]

The following query uses these:

Features: Graph (Q24515287)  View with Reasonator View with SQID

#defaultView:Graph
SELECT ?item ?itemLabel ?game ?gameLabel ("00FFFF" AS ?rgb) WHERE {
  ?item wdt:P31 wd:Q33093124.
 ?game wdt:P674 ?item.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

source

Games with the most player characters[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, characters (P674)  View with Reasonator View with SQID, object has role (P3831)  View with Reasonator View with SQID
    SELECT ?item ?itemLabel (COUNT(?character) AS ?playable_characters) WHERE {
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      ?item (wdt:P31/(wdt:P279*)) wd:Q7889;
        p:P674 ?statement.
      ?statement ps:P674 ?character;
        pq:P3831 wd:Q1062345.
    }
    GROUP BY ?item ?itemLabel
    ORDER BY DESC (?playable_characters)
    

Games with female protagonists[edit]

The following query uses these:

Games with female player characters[edit]

The following query uses these:

Games with female characters[edit]

The following query uses these:

Games with LGBT characters[edit]

The following query uses these:

Games with LGBT protagonists[edit]

The following query uses these:

Games with LGBT player characters[edit]

The following query uses these:

Graph of video games genres[edit]

Graph of video game genre (Q659563) and outline the ones that are linked to OLAC video game genre vocabulary ID (P6352)

The following query uses these:

Features: Graph (Q24515287)  View with Reasonator View with SQID

#defaultView:Graph
SELECT DISTINCT ?genre ?genreLabel ?rgb ?OLAC ?_subclass_of ?_subclass_ofLabel WHERE {
  ?genre wdt:P31 wd:Q659563.
  OPTIONAL {
    ?genre wdt:P279 ?_subclass_of.
    { ?_subclass_of wdt:P31 wd:Q659563. } UNION {?_subclass_of wdt:P279 wd:Q2249149.}
  }

  OPTIONAL { ?genre wdt:P6352 ?OLAC. }
  BIND(IF(BOUND(?OLAC), "FFA500", "7FFF00") AS ?rgb)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}


Video games whose composer is Japanese, not born in Tokyo, and less than 25 at the time[edit]

The following query uses these:

Timeline of games per platform[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, platform (P400)  View with Reasonator View with SQID, publication date (P577)  View with Reasonator View with SQID
    #defaultView:AreaChart
    SELECT (SAMPLE(?year) AS ?year) (COUNT(?_platform) AS ?count) (SAMPLE(?_platformLabel) AS ?platformLabel)  WHERE {
      ?video_game wdt:P31 wd:Q7889.
      ?video_game wdt:P400 ?_platform.
      ?video_game wdt:P577 ?date.
      BIND ( STR(year(?date)) as ?year )
      FILTER(BOUND(?year))
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
                              ?_platform rdfs:label ?_platformLabel.}
    }
    GROUP BY ?year ?_platform
    HAVING (?count > 10)
    

Video games and their trailers[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, YouTube video ID (P1651)  View with Reasonator View with SQID, video (P10)  View with Reasonator View with SQID, object has role (P3831)  View with Reasonator View with SQID
    SELECT ?item ?itemLabel (GROUP_CONCAT(DISTINCT ?value; SEPARATOR = " ") AS ?trailer) WHERE {
      {
        ?item p:P1651 ?statement.
        ?statement ps:P1651 ?v;
          pq:P3831 wd:Q65972034.
        BIND(CONCAT("https://www.youtube.com/watch?v=", ?v) AS ?value)
      }
      UNION
      {
        ?item p:P10 ?statement.
        ?statement ps:P10 ?v;
          pq:P3831 wd:Q65972034.
        BIND(CONCAT("[", STR(?v), " C]") AS ?value)
      }
      ?item (wdt:P31/(wdt:P279*)) wd:Q7889.
      SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    }
    GROUP BY ?item ?itemLabel
    ORDER BY DESC (xsd:integer(STRAFTER(STR(?item), "Q")))
    

Video games with few statements and identifiers[edit]

See report at Wikidata:WikiProject Video games/Maintenance/Few statements

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID
    #adapted from query by User:Matěj Suchánek
    SELECT ?item ?st ?sl ?ids str(?i) as ?offset)
    WITH { SELECT DISTINCT ?item        WHERE { ?item wdt:P31 wd:Q7889 ; wikibase:statements ?st . FILTER(?st < 2) }  } AS %all
    WITH { SELECT (COUNT(*) AS ?count)  WHERE { INCLUDE %all }  } AS %count
    WITH { SELECT (?item AS ?item2)     WHERE { INCLUDE %all }  } AS %all2
    WITH
    {
      SELECT ?item (SUM(?x) AS ?i)  WHERE  { INCLUDE %all . INCLUDE %all2 .
        BIND( IF( STR( ?item ) > STR( ?item2 ), 1, 0 ) AS ?x ) .
      } GROUP BY ?item
    } AS %main 
    WHERE
    {
      hint:Query hint:optimizer "None".
      INCLUDE %count .
      BIND(ROUND( ?count / 20) as ?slice ) BIND( ROUND(FLOOR( RAND() * ?count / 20 )) AS ?rnd) 
      INCLUDE %main .
      FILTER( ROUND( ( ?i/ ?slice - FLOOR( ?i / ?slice ) ) * ?slice ) = ?rnd)
      ?item wikibase:statements ?st; wikibase:sitelinks ?sl; wikibase:identifiers ?ids.
    }
    

Video games with a 10/10 in Edge Magazine[edit]

The following query uses these:

  • Properties: review score (P444)  View with Reasonator View with SQID, review score by (P447)  View with Reasonator View with SQID
    SELECT ?item ?itemLabel WHERE {
      ?item p:P444 ?review_scoreStatement.
      ?review_scoreStatement pq:P447 wd:Q793975;
        ps:P444 "10/10".
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    LIMIT 100
    

Most-cited reviewers[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, review score (P444)  View with Reasonator View with SQID, review score by (P447)  View with Reasonator View with SQID
    SELECT ?reviewer ?reviewerLabel (COUNT(?item) AS ?items) WHERE {
      ?item (wdt:P31/(wdt:P279*)) wd:Q7889;
        p:P444 ?review_scoreStatement.
      ?review_scoreStatement pq:P447 ?reviewer.
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    GROUP BY ?reviewer ?reviewerLabel
    ORDER BY DESC (?items)
    


Video game series with the longest time time gap between a game and its direct sequel[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, publication date (P577)  View with Reasonator View with SQID, followed by (P156)  View with Reasonator View with SQID, part of the series (P179)  View with Reasonator View with SQID
    # Video game series with the longest time time gap between a game and its direct sequel
    # (as games can have more than one publication date [because re-releases], we only take the earliest one [using MIN])
    SELECT ?series ?seriesLabel ?years ?interval ?game ?gameLabel ?original_date_game ?sequel ?sequelLabel ?original_date_sequel WHERE {
      {
        SELECT ?game ?gameLabel (MIN(?game_date) AS ?original_date_game) ?sequel ?sequelLabel (MIN(?sequel_date) AS ?original_date_sequel) ?series ?seriesLabel WHERE {
          ?game (wdt:P31/(wdt:P279*)) wd:Q7889; # All video games...
            wdt:P577 ?game_date.                # with their publication date(s)...
          {
            ?game p:P179 ?series_statement.     # (Unpacking the series statement)
            ?series_statement ps:P179 ?series;  #   ...in a series...
              pq:P156 ?sequel.                  #   ...with a sequel...
          } UNION {                             # ...or...
            ?game wdt:P156 ?sequel.             #   ...with a sequel...
            OPTIONAL {
              ?game wdt:P179 ?series            # ...maybe with a series
            }
          }
          ?sequel wdt:P577 ?sequel_date.        # with its own publication date(s)
          SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
        }
        GROUP BY ?game ?gameLabel ?sequel ?sequelLabel ?series ?seriesLabel
      }
      BIND(?original_date_sequel - ?original_date_game AS ?interval)  # Calculating the time gap between the original releases, in days...
      BIND(ROUND(?interval / 365 ) AS ?years)                         # ...and converting to years, for convenience...
      FILTER(?interval > 0 )                                          # Only keeping sequels published after (filtering out some bad data)
    }
    ORDER BY DESC (?interval)                                         # Longest time gaps first
    

Video games with the most expansions[edit]

The following query uses these:

  • Properties: expansion of (P8646)  View with Reasonator View with SQID, instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID
    SELECT ?game ?gameLabel (COUNT(?item) AS ?count) WHERE {
      ?item wdt:P8646 ?game.
      ?game (wdt:P31/(wdt:P279*)) wd:Q7889.
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    GROUP BY ?game ?gameLabel
    ORDER BY DESC (?count)
    

Games published after their sequels[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, publication date (P577)  View with Reasonator View with SQID, part of the series (P179)  View with Reasonator View with SQID, followed by (P156)  View with Reasonator View with SQID
    SELECT ?game ?gameLabel ?original_date_game ?sequel ?sequelLabel ?original_date_sequel ?interval ?series ?seriesLabel WHERE {
      {
        SELECT ?game ?gameLabel (MIN(?game_date) AS ?original_date_game) ?sequel ?sequelLabel (MIN(?sequel_date) AS ?original_date_sequel) ?series ?seriesLabel WHERE {
          ?game wdt:P31/wdt:P279* wd:Q7889;
            wdt:P577 ?game_date;
            p:P179 ?series_statement.
          ?series_statement pq:P156 ?sequel;
            ps:P179 ?series.
          ?sequel wdt:P577 ?sequel_date.
          SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
        }
        GROUP BY ?game ?gameLabel ?sequel ?sequelLabel ?series ?seriesLabel
      }
      BIND(?original_date_sequel - ?original_date_game AS ?interval)
      FILTER ( ?interval < 0 )
    }
    

DLCs not linked to the main game[edit]

The following query uses these:

  • Properties: expansion of (P8646)  View with Reasonator View with SQID, instance of (P31)  View with Reasonator View with SQID, of (P642)  View with Reasonator View with SQID
    SELECT ?item ?itemLabel ?game ?gameLabel WHERE {
      ?item p:P31 ?p31.
      ?p31 ps:P31 wd:Q1066707.
      OPTIONAL { ?p31 pq:P642 ?game. }
      FILTER(NOT EXISTS { ?item wdt:P8646 _:b6. })
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    

Video games whose developer is headquartered in a given country but with a different country of origin[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, developer (P178)  View with Reasonator View with SQID, country (P17)  View with Reasonator View with SQID, headquarters location (P159)  View with Reasonator View with SQID, country of origin (P495)  View with Reasonator View with SQID
    #TEMPLATE={"template":"Video games whose developer is headquartered in ?country but with a different country of origin","variables":{"?country":{"query":" SELECT ?id WHERE { ?id wdt:P31 wd:Q6256 . }"} } }
    SELECT DISTINCT ?item ?itemLabel ?dev ?devLabel ?hq ?hqLabel ?country_originLabel WHERE {
      BIND(wd:Q142 AS ?country)
      ?item (wdt:P31/(wdt:P279*)) wd:Q7889;
        wdt:P178 ?dev.
      ?dev wdt:P17 ?country.
      OPTIONAL { ?dev wdt:P159 ?hq. }
      FILTER(NOT EXISTS { ?item wdt:P495 ?country. })
      OPTIONAL {
        ?item wdt:P495 ?country_origin
      }
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    


Video game released on both a handheld and home platform of the same console generation[edit]

(In many cases, an handheld platform would not be powerful enough to run a game from a home platform − hinting these might be two different games that need to be split up).

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, platform (P400)  View with Reasonator View with SQID, part of (P361)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID
    SELECT DISTINCT ?item ?itemLabel ?handheld_platform ?handheld_platformLabel ?home_platform ?home_platformLabel ?generationLabel WHERE {
      ?item wdt:P31 wd:Q7889; 
        wdt:P400 ?home_platform, ?handheld_platform.
      ?home_platform wdt:P361 ?generation;
        (wdt:P279*) wd:Q17589470.
      ?handheld_platform wdt:P361 ?generation;
        (wdt:P279*) wd:Q941818.
      ?generation wdt:P31 wd:Q61697632.
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    

Video games with the most time elapsed between their announcement and their release[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, publication date (P577)  View with Reasonator View with SQID, announcement date (P6949)  View with Reasonator View with SQID
    # Video games with the most time elapsed between their announcement and their release
    # (as games can have more than one publication date [because re-releases], we only take the earliest one [using MIN])
    SELECT ?years ?interval ?game ?gameLabel ?first_publication_date ?announcement_date WHERE {
      {
        SELECT ?game ?gameLabel (MIN(?publication_date) AS ?first_publication_date) ?announcement_date WHERE {
          ?game (wdt:P31/(wdt:P279*)) wd:Q7889;
            wdt:P577 ?publication_date;
            wdt:P6949 ?announcement_date.
          SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
        }
        GROUP BY ?game ?gameLabel ?announcement_date
      }
      BIND(?first_publication_date - ?announcement_date AS ?interval)
      BIND(ROUND(?interval / 365 ) AS ?years)
      FILTER(?interval > 0 )
    }
    ORDER BY DESC (?interval)
    

Video games with the most nominations at the given award ceremony[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, facet of (P1269)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, developer (P178)  View with Reasonator View with SQID, nominated for (P1411)  View with Reasonator View with SQID, statement is subject of (P805)  View with Reasonator View with SQID
    #TEMPLATE={ "template":"Video games with the most nominations at the ?ceremony award ceremony","variables":{"?ceremony":{"query":" SELECT ?id WHERE { ?id wdt:P31 wd:Q4504495; wdt:P1269 ?award. ?award wdt:P279 wd:Q18328126. }"} } }
    SELECT ?item ?itemLabel ?developer (GROUP_CONCAT(?awardLabel; SEPARATOR = ", ") AS ?awards) (COUNT(?award) AS ?nominations) WHERE {
      BIND(wd:Q106016685 AS ?ceremony)
      ?item p:P1411 ?statement.
      ?statement ps:P1411 ?award;
        pq:P805 ?ceremony.
      OPTIONAL { ?item wdt:P178 ?developer. }
      SERVICE wikibase:label {
        bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
        ?award rdfs:label ?awardLabel.
        ?item rdfs:label ?itemLabel.
      }
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    GROUP BY ?item ?itemLabel ?developer
    ORDER BY DESC (?nominations)
    

Video games with the most wins at the given award ceremony[edit]

The following query uses these:

  • Properties: instance of (P31)  View with Reasonator View with SQID, facet of (P1269)  View with Reasonator View with SQID, subclass of (P279)  View with Reasonator View with SQID, developer (P178)  View with Reasonator View with SQID, award received (P166)  View with Reasonator View with SQID, statement is subject of (P805)  View with Reasonator View with SQID
    #TEMPLATE={ "template":"Video games with the most nominations at the ?ceremony award ceremony","variables":{"?ceremony":{"query":" SELECT ?id WHERE { ?id wdt:P31 wd:Q4504495; wdt:P1269 ?award. ?award wdt:P279 wd:Q18328126. }"} } }
    SELECT ?item ?itemLabel ?developer (GROUP_CONCAT(?awardLabel; SEPARATOR = ", ") AS ?awards) (COUNT(?award) AS ?award_received) WHERE {
      BIND(wd:Q102300180 AS ?ceremony)
      ?item p:P166 ?statement.
      ?statement ps:P166 ?award;
        pq:P805 ?ceremony.
      OPTIONAL { ?item wdt:P178 ?developer. }
      SERVICE wikibase:label {
        bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
        ?award rdfs:label ?awardLabel.
        ?item rdfs:label ?itemLabel.
      }
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
    }
    GROUP BY ?item ?itemLabel ?developer
    ORDER BY DESC (?award_received)