User:MisterSynergy/misc/references

+/−

MisterSynergy’s work lists

• rowing/queries
• rowing/P214 for rowers
• rowing/P3171 for rowers
• rowing/event participation
• rowing/unconnected pages
• badminton/queries
• tennis/queries
• sports/queries
• sports/P641 worklists
• sports/P1447 worklists
• sports/Olympics
• cultural heritage/Germany
• misc/linked deleted items
• misc/quantity data type
• misc/references
• misc/sitelink badges
• misc/unsorted worklists

---

Special:PrefixIndex/User:MisterSynergy/ • User:MisterSynergy/sysop/header • item statistics • patrol • pages with code • activity • jobs

Queries

Use database item instead of plain link

SELECT ?item ?itemLabel ?property ?propertyLabel ?url WITH {
  SELECT DISTINCT ?item ?p ?url WHERE {
    ?item p:P106/ps:P106/wdt:P279* wd:Q26270618; ?p [ prov:wasDerivedFrom/pr:P854 ?url ] .
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  ?property wikibase:claim ?p .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} ORDER BY ASC(?url)

Try it!

Incomplete database reference

SELECT ?item ?itemLabel ?valueLabel WHERE {
  ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 . # limited to rowing persons
  ?item ?property ?statement .
  ?statement prov:wasDerivedFrom ?source .
  ?source pr:P248 wd:Q21008628 . # stated in Q21008628 World Rowing Database or Q18002875 Sports-Reference.com
  MINUS { ?source pr:P813 ?title . } # but P813 retrieval date is missing; P1476 title; P1447/P2091 ID; P407 language
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Broken database item

SELECT ?item ?itemLabel ?sourceitem ?sourceitemLabel WHERE {
  ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 . # limited to rowing persons
  ?item ?property ?statement .
  ?statement prov:wasDerivedFrom ?source .
  ?source pr:P248 ?sourceitem .
  MINUS { ?sourceitem wdt:P1687 ?foo . } # vary here
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Incomplete references (needs work)

SELECT ?item ?itemLabel ?sr ?propLabel ?val ?valLabel ?type WHERE {
  ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 .
  ?item ?property ?statement .
  ?statement prov:wasDerivedFrom ?source .
  VALUES ?database {
    wd:Q18002875
  }
  ?source pr:P248 ?database .
  ?source pr:P1447 ?sr .
  VALUES ?mandatoryStatement {
    pr:P1476
  }
  MINUS { ?source ?mandatoryStatement ?foo . }
  ?statement ?prop ?val .
  ?prop a ?type .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Version for web pages:

SELECT ?item ?itemLabel ?property ?propLbl ?value WHERE {
  ?property a wikibase:Property .
  ?property wikibase:claim ?claimprop; wikibase:statementProperty ?stmtprop; rdfs:label ?propLbl .
  FILTER(LANG(?propLbl) = 'en') .
  
  ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 .
  
  ?item ?claimprop ?statement .
  ?statement ?stmtprop ?value; prov:wasDerivedFrom ?source .
  ?source pr:P854 ?referenceUrl . 
  VALUES ?mandatoryStatement {
#	pr:P248 # stated in (if applicable)
	pr:P813 # retrieved
#	pr:P1476 # title
#   pr:P577 # publication date
#   pr:P1065 # archive url
#   pr:P2960 # archive date
#   pr:P2439 # language
#   pr:P50 # author
#   pr:P123 # publisher
#   pr:P1683 # quote
  }
  MINUS { 
    ?source ?mandatoryStatement ?foo . 
  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Databases that are used for references, but not linked as identifier

SELECT ?item ?property ?value ?identifier ?id WITH {
  SELECT DISTINCT ?item WHERE {
    ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 .
  }
} AS %subquery WHERE {
  INCLUDE %subquery 
  ?property wikibase:claim ?claim; wikibase:statementProperty ?statementProperty .
  ?identifier wikibase:propertyType wikibase:ExternalId; wikibase:claim ?identifierClaim; wikibase:statementProperty ?identifierStatementProperty; wikibase:reference ?ref .
  ?item ?claim [ ?statementProperty ?value; prov:wasDerivedFrom [ ?ref ?id ] ] .
  MINUS { ?item ?identifierClaim [ ?identifierStatementProperty ?id ] }
}

Try it!

Imported from target no longer available

Needs cleanup; imported from Wikimedia project (P143) should only be used for Wikimedia imports; still some hits.

SELECT ?item ?property ?value ?wikipedia ?wikiurl WITH {
  SELECT DISTINCT ?item WHERE {
    ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 .
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  ?property wikibase:claim ?claim; wikibase:statementProperty ?ps .
  ?item ?claim [ ?ps ?value; prov:wasDerivedFrom [ pr:P143 ?wikipedia ] ] .
  OPTIONAL {
    ?wikipedia wdt:P856 ?wikiurl .
  }
  OPTIONAL {
    ?sitelink schema:about ?item; schema:isPartOf ?wikiurl .
  }
  FILTER(!BOUND(?sitelink)) .
}

Try it!

Imported from something else than a Wikimedia project

SELECT ?item ?p ?reference ?referenceLabel WITH {
#SELECT ?reference ?referenceLabel (COUNT(*) AS ?cnt) WITH {
  SELECT DISTINCT ?item WHERE {
    ?item p:P106/ps:P106/wdt:P279* wd:Q26270618 .
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  ?item ?p [ prov:wasDerivedFrom/pr:P143 ?reference ] .
  MINUS { ?reference wdt:P31/wdt:P279* wd:Q14827288 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}# GROUP BY ?reference ?referenceLabel

Try it!

(need some tweaks)

Related to imported from Wikimedia project (P143)

Invalid P143 values with reference count

SELECT ?P143valueItem ?P143valueItemLabel (COUNT(?statement) AS ?cnt_statements) (COUNT(DISTINCT ?item) AS ?cnt_items) (IRI(CONCAT('https://query.wikidata.org/#SELECT%2a%7B%3Fitem%3Fprop%5Bprov%3AwasDerivedFrom%2Fpr%3AP143%20wd%3A', STRAFTER(STR(?P143valueItem), "entity/"), '%5D%7D')) AS ?query_items) WITH {
  SELECT DISTINCT ?P143valueItem WHERE { [] pr:P143 ?P143valueItem }
} AS %subquery1 WITH {
  SELECT DISTINCT ?P143valueItem ?P143valueItemLabel WHERE {
    INCLUDE %subquery1 .
    MINUS { ?P143valueItem wdt:P31/wdt:P279* wd:Q14827288 }
    MINUS { ?P143valueItem wdt:P31/wdt:P279* wd:Q63144794 }
#   FILTER(?P143valueItem NOT IN (wd:Q28054658, wd:Q24731821, wd:Q19960422, wd:Q48552277)) . MINUS { ?P143valueItem wdt:P31/wdt:P279* wd:Q4167836 } MINUS { ?P143valueItem wdt:P31/wdt:P279* wd:Q13406463 } # some internal Wikimedia stuff
#   VALUES ?class { wd:Q386724 wd:Q11028 } ?P143valueItem wdt:P31?/wdt:P279* ?class . # valid P248 values
    SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
  }
} AS %subquery2 WHERE {
  INCLUDE %subquery2 .
  ?statement prov:wasDerivedFrom/pr:P143 ?P143valueItem .
  ?item ?any ?statement .
} GROUP BY ?P143valueItem ?P143valueItemLabel ORDER BY DESC(?cnt_statements)

Try it!

Total number of references with values other than Wikimedia projects

SELECT (COUNT(?statement) AS ?cnt) WITH {
  SELECT DISTINCT ?item WHERE { [] pr:P143 ?item }
} AS %subquery1 WITH {
  SELECT DISTINCT ?item ?itemLabel WHERE {
    INCLUDE %subquery1 .
    MINUS { ?item wdt:P31/wdt:P279* wd:Q14827288 }
    MINUS { ?item wdt:P31/wdt:P279* wd:Q63144794 }
    SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
  }
} AS %subquery2 WHERE {
  INCLUDE %subquery2 .
  ?statement prov:wasDerivedFrom/pr:P143 ?item .
}

Try it!

Interesting for evaluation of a particular value

SELECT DISTINCT ?item WITH {
#SELECT ?cnt ?prs (COUNT(?prs) AS ?cntcnt) WITH {
  SELECT ?ref WHERE {
    ?ref pr:P143 wd:Q54919; pr:P813 [] . #  Q1823134 LCSH ; Q36578 GND; Q54919 VIAF; Q423048 ISNI; Q51711 Quora; Q37312 IMDb
  }
} AS %t WITH {
  SELECT ?ref (COUNT(?pr) AS ?cnt) (GROUP_CONCAT(STRAFTER(STR(?pr), 'reference/'); separator=', ') AS ?prs) WHERE {
    INCLUDE %t .
    ?ref ?pr [] .
    ?pr ^wikibase:reference [] .
  } GROUP BY ?ref
} AS %s WHERE {
  INCLUDE %s .
  ?statement prov:wasDerivedFrom ?ref . 
  ?item ?any ?statement .
} LIMIT 100 #GROUP BY ?cnt ?prs ORDER BY DESC(?cnt)

Try it!