Skip to content

Instantly share code, notes, and snippets.

@connorshea

connorshea/wikidata_queries.md

Last active August 4, 2022 01:44
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save connorshea/d813cae2bad4dd72a490efe925dfb6c2 to your computer and use it in GitHub Desktop.
Save connorshea/d813cae2bad4dd72a490efe925dfb6c2 to your computer and use it in GitHub Desktop.
Download ZIP
Some simple, useful SPARQL queries I've written to help match PCGamingWiki articles to Wikidata items.
Raw
wikidata_queries.md

Some Wikidata queries I've written.

https://query.wikidata.org

Items with PCGW ID and 'n/a' description

Items with PCGamingWiki ID (P6337) and the english description 'n/a'.

This query catches a problem caused by creation of new items when running Mix'n'Match with the PCGW dataset. The Mix'n'match dataset lacks proper descriptions, which has an unintended side effect of new items being created with the description 'n/a'.

SELECT ?item ?itemLabel WHERE
{
  ?item wdt:P6337 ?pcgw_id. # with a PCGW ID
  ?item schema:description ?description . FILTER(LANG(?description) = "en")
  FILTER regex(?description, 'n/a') # English description is 'n/a'
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Note: This has a bug where a game with the description 'action/adventure game' would be captured due to the regex not including start/end syntax. I'm pretty sure it's supported by SPARQL, so maybe I should fix that.

Video games on Windows with no PCGW ID

Instances of (P31) video games (Q7889) on platform (P400) Microsoft Windows (Q1406) with no PCGamingWiki ID (P6337).

SELECT ?item ?itemLabel WHERE
{
  ?item wdt:P31 wd:Q7889; # instance of video game
        wdt:P400 wd:Q1406. # on Windows
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to get just the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P31 wd:Q7889; # instance of video game
        wdt:P400 wd:Q1406. # on Windows
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Video games on Windows with an English Wikipedia article and no PCGW ID

Instances of (P31) video games (Q7889) on platform (P400) Microsoft Windows (Q1406) with an English Wikipedia article and no PCGamingWiki ID (P6337).

SELECT ?item ?itemLabel ?enlanguagelink
{
  ?item wdt:P31 wd:Q7889;  # instance of video game
        wdt:P400 wd:Q1406. # on Windows
  
  # With an English Wikipedia article.
  ?enlanguagelink schema:about ?item.
  ?enlanguagelink schema:inLanguage "en". 
  ?enlanguagelink schema:isPartOf <https://en.wikipedia.org/>

  # And no PCGW ID
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to just get the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P31 wd:Q7889;  # instance of video game
        wdt:P400 wd:Q1406. # on Windows
  
  # With an English Wikipedia article.
  ?enlanguagelink schema:about ?item.
  ?enlanguagelink schema:inLanguage "en". 
  ?enlanguagelink schema:isPartOf <https://en.wikipedia.org/>

  # And no PCGW ID
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Video games on Windows with a GiantBomb ID and no PCGW ID

Instances of (P31) video games (Q7889) on platform (P400) Microsoft Windows (Q1406) with a GiantBomb ID (P5247) and no PCGamingWiki ID (P6337).

SELECT ?item ?itemLabel 
{
  ?item wdt:P31 wd:Q7889;       # instance of video game
        wdt:P400 wd:Q1406;      # on Windows
        wdt:P5247 ?giantBombID. # with a Giant Bomb ID
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to get just the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P31 wd:Q7889;       # instance of video game
        wdt:P400 wd:Q1406;      # on Windows
        wdt:P5247 ?giantBombID. # with a Giant Bomb ID
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a Steam AppID and no PCGW ID

Items with a Steam AppID (P1733) and no PCGamingWiki ID (P6337).

SELECT ?item ?itemLabel ?steamAppId
{
  ?item wdt:P1733 ?steamAppId. # items with a Steam App ID.
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to just get the # of items with a Steam AppID and no PCGW ID:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P1733 ?steamAppId. # items with a Steam App ID.
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a GOG.com App ID and no PCGW ID

Items with a GOG.com AppID (P2725) and no PCGamingWiki ID (P6337).

SELECT ?item ?itemLabel ?gogAppId
{
  ?item wdt:P2725 ?gogAppId. # items with a GOG App ID.
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to just get the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P2725 ?gogAppId. # items with a GOG App ID.
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a WineDB ID and no PCGW ID

Items with a WineDB ID (P600) and no PCGamingWiki ID (P6337).

SELECT ?item ?itemLabel ?wineDbId
{
  ?item wdt:P31 wd:Q7889; # items that are video games
        wdt:P600 ?wineDbId. # items with a WineDB ID.
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to just get the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P31 wd:Q7889; # items that are video games
        wdt:P600 ?wineDbId. # items with a WineDB ID.
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a PCGW ID and no WineDB ID

Items with a PCGamingWiki ID (P6337) and no WineDB ID (P600).

SELECT ?item ?itemLabel ?pcgw_id
{
  ?item wdt:P6337 ?pcgw_id. # items with a PCGW ID.
  FILTER NOT EXISTS { ?item wdt:P600 ?winedb_id . } # with no WineDB ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Or to just get the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item wdt:P6337 ?pcgw_id. # items with a PCGW ID.
  FILTER NOT EXISTS { ?item wdt:P600 ?winedb_id . } # with no WineDB ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a Steam AppID that cite PCGamingWiki as their source

Items with a Steam AppID (P1773) and a qualifier for that Steam AppID of having been imported from PCGamingWiki. (This one is mostly just because I've seen this in a few places and thought it was weird/interesting, e.g. https://www.wikidata.org/wiki/Q955289)

Credit to Tagishsimon on Wikidata for these queries!

SELECT ?item ?itemLabel 
{
  ?item p:P1733 [ ps:P1733 ?steamAppID ; prov:wasDerivedFrom [pr:P143 wd:Q17013880 ] ]. # Items with a Steam AppID that cites PCGW as the source
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} order by ?itemLabel

Or to just get the # of items:

SELECT (COUNT(?item) AS ?count)
{
  ?item p:P1733 [ ps:P1733 ?steamAppID ; prov:wasDerivedFrom [pr:P143 wd:Q17013880 ] ]. # Items with a Steam AppID that cites PCGW as the source
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

To get the items imported from PCGamingWiki with no PCGW ID (P6337):

SELECT ?item ?itemLabel 
{
  ?item p:P1733 [ ps:P1733 ?steamAppID ; prov:wasDerivedFrom [pr:P143 wd:Q17013880 ] ]. # Items with a Steam AppID that cites PCGW as the source
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} order by ?itemLabel

Items with a Steam AppID and no platform qualifier for it

To get items with a Steam AppID (P1733) and no platform (P400) qualifier:

SELECT ?item ?itemLabel ?appID ?platform WHERE {
  OPTIONAL {?item p:P1733 ?statement. # Get the statement of the appID
            ?statement ps:P1733 ?appID. # Get the actual appID
            FILTER NOT EXISTS {?statement pq:P400 ?platform.} # Get rid of anything with a platform qualifier.
           }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,en"  }    
}

Items with a PCGamingWiki ID and no 'instance of' property

To get items with a PCGamingWiki ID (P6337) and no 'instance of' (P31) property:

SELECT ?item ?itemLabel ?pcgwId
{
  ?item wdt:P6337 ?pcgwId. # items with a PCGW ID.
  FILTER NOT EXISTS { ?item wdt:P31 ?instanceOf . } # with no 'instance of'
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a PCGamingWiki ID that has URL-encoded strings in it

e.g. Assassin%27s_Creed_II, where the apostrophe is encoded as %27 which doesn't lead to a valid page. These is caused by some funky data that was exported for use in mix'n'match.

SELECT ?item ?itemLabel ?value WHERE {
  {
    SELECT ?item ?value WHERE {
      ?item p:P6337 [ ps:P6337 ?value ] .
      
      BIND( REGEX( STR( ?value ), "^.+%\\d\\d.*$" ) AS ?regexresult ) .
      FILTER( ?regexresult = true ) .
      FILTER( ?item NOT IN ( wd:Q4115189, wd:Q13406268, wd:Q15397819 ) ) .
    } 
    LIMIT 100
  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Items released in a given year with no PCGamingWiki ID

SELECT ?item ?itemLabel ?date WHERE {
  ?item wdt:P31/wdt:P279* wd:Q7889.
  ?item wdt:P577 ?date. FILTER(YEAR(?date) = 2017).
  FILTER NOT EXISTS { ?item wdt:P6337 ?pcgw_id . } # with no PCGW ID
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ?date

Items with a Steam App ID and not listed as being on Windows, macOS, or Linux

SELECT ?item ?itemLabel ?steam_id WHERE {
  ?item wdt:P31 wd:Q7889;
    wdt:P1733 ?steam_id.
  # Windows
  FILTER(NOT EXISTS { ?item wdt:P400 wd:Q1406 })
  # macOS
  FILTER(NOT EXISTS { ?item wdt:P400 wd:Q14116 })
  # Linux
  FILTER(NOT EXISTS { ?item wdt:P400 wd:Q388 })
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Items with a Steam App ID platform qualifier that's missing for the platform property.

For example, all items where the Steam App ID qualifiers say it's available on Linux but for which the platform property does not list Linux.

SELECT ?item ?itemLabel ?value ?valueLabel WHERE 
{
  ?item p:P1733 ?stat .
  ?stat pq:P400 ?value .  
  filter not exists {?item wdt:P400 ?value.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Items with the description 'video game' and a publication date.

SELECT ?item ?itemLabel (GROUP_CONCAT(?publicationDate) AS ?pubDates) WHERE
{
  ?item wdt:P31 wd:Q7889; # instance of video game
        wdt:P577 ?publicationDate. # with a publication date
  ?item schema:description ?description . FILTER(LANG(?description) = "en")
  FILTER regex(?description, '^video game$') # English description is just 'video game'
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} 
GROUP BY ?item ?itemLabel

Ideas

Some other queries that I haven't made yet, but that might be useful:

  • Items with a PCGamingWiki ID (P6337) and a platform (P400) value of "personal computer". (To correct these, since "personal computer" is an invalid platform)
  • Video games with a "follows" or "followed by" property and no "part of series" property.
@connorshea
Copy link
Author

See this for writing queries that involve statement qualifiers/references: https://www.wikidata.org/wiki/User_talk:Tagishsimon#Wittylama

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment