Wikidata:WikiProject Chess/Maintenance Queries
Jump to navigation
Jump to search
Try it!
Try it!
Try it!
Try it!
Try it!
|
|
|
|
Chess players without a title and without interwiki links (= potentially not notable items):
SELECT ?item ?itemLabel WHERE {
?item wdt:P106 wd:Q10873124 .
?item wikibase:sitelinks 0 .
MINUS {?item wdt:P2962 ?title }.
MINUS {?item wdt:P691 ?nkcr }.
MINUS {?item wdt:P3940 ?olimp } .
MINUS {?item wdt:P5224 ?olimpWomen } .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
GM title awarded to a player before the IM title:
SELECT ?item ?start_GM ?start_IM WHERE {
?item p:P2962 [ ps:P2962 wd:Q105269; pq:P580 ?start_GM ], [ ps:P2962 wd:Q752119; pq:P580 ?start_IM ] .
FILTER(YEAR(?start_GM) < YEAR(?start_IM)) .
}
Chess players with least number of statements:
SELECT
?item
(Sample(?itemLabel) as ?itemLabel)
(Sample(?itemDescription) as ?itemDescription)
(Sample(?st) as ?st)
(Sample(?sl) as ?sl)
(Sample(?ids) as ?ids)
(Sample(?dob) as ?dob)
WHERE {
?item wdt:P31 wd:Q5 .
?item wdt:P106 wd:Q10873124 .
?item wikibase:statements ?st .
?item wikibase:sitelinks ?sl .
OPTIONAL { ?item wdt:P569 ?dob .}
OPTIONAL { ?item wikibase:identifiers ?ids . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en".
?item rdfs:label ?itemLabel .
?item schema:description ?itemDescription . }
}
GROUP BY ?item
ORDER BY ASC(?st) ?item
LIMIT 100
Items with the only statement sport (P641): chess (Q718):
SELECT DISTINCT ?item ?sl ?itemLabel
{
?item wdt:P641 wd:Q718 ; wikibase:statements 1 ; wikibase:sitelinks ?sl ; rdfs:label ?l
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY DESC(xsd:integer(strafter(str(?item), "Q")))
LIMIT 1000
Chess people with same name and similar date of birth / date of death (possible duplicates):
# TODO:
# * order by creation rather than QID string: "FILTER(STR(?item) < STR(?item2))"
# * include non-en labels for first item (times out)
SELECT DISTINCT ?itemName ?item ?dob1displayStr ?dod1displayStr ?item2 ?dob2displayStr ?dod2displayStr ?dobdiff ?doddiff ?meandiff WHERE {
?item (wdt:P106/wdt:P279*) wd:Q73145133. # chess people or any subclass thereof
{ ?item rdfs:label ?itemName. } UNION { ?item skos:altLabel ?itemName. }
FILTER((LANG(?itemName)) = "en")
?item2 (wdt:P106/wdt:P279*) wd:Q73145133.
{ ?item2 rdfs:label ?itemName. } UNION { ?item2 skos:altLabel ?itemName. }
FILTER((STR(?item)) < (STR(?item2)))
MINUS { ?item wdt:P1889 ?item2. }
MINUS { ?item2 wdt:P1889 ?item. }
MINUS { ?item wdt:P460 ?item2. }
MINUS { ?item2 wdt:P460 ?item. }
MINUS { ?item wdt:P3373 ?item2. }
MINUS { ?item2 wdt:P3373 ?item. }
OPTIONAL {
?item p:P569/psv:P569 [
wikibase:timeValue ?dob1Val;
wikibase:timePrecision ?dob1Prec ] .
BIND(STR(?dob1Val) AS ?dob1fullStr)
BIND(IF(?dob1Prec <= 10, 3, 0) AS ?dob1insigDayChars)
BIND(IF(?dob1Prec <= 9, 3, 0) AS ?dob1insigMonthChars)
BIND(IF(?dob1Prec <= 8, 9-?dob1Prec, 0) AS ?dob1insigYearChars)
BIND(?dob1insigDayChars + ?dob1insigMonthChars + ?dob1insigYearChars AS ?dob1insigDateChars)
BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dob1insigDateChars) AS ?dob1significantStr)
BIND(CONCAT(?dob1significantStr, SUBSTR("0000000000000000", 1, ?dob1insigYearChars)) AS ?dob1displayStr)
}
OPTIONAL {
?item2 p:P569/psv:P569 [
wikibase:timeValue ?dob2Val;
wikibase:timePrecision ?dob2Prec ] .
BIND(STR(?dob2Val) AS ?dob2fullStr)
BIND(IF(?dob2Prec <= 10, 3, 0) AS ?dob2insigDayChars)
BIND(IF(?dob2Prec <= 9, 3, 0) AS ?dob2insigMonthChars)
BIND(IF(?dob2Prec <= 8, 9-?dob2Prec, 0) AS ?dob2insigYearChars)
BIND(?dob2insigDayChars + ?dob2insigMonthChars + ?dob2insigYearChars AS ?dob2insigDateChars)
BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dob2insigDateChars) AS ?dob2significantStr)
BIND(CONCAT(?dob2significantStr, SUBSTR("0000000000000000", 1, ?dob2insigYearChars)) AS ?dob2displayStr)
}
BIND(IF(?dob1insigDateChars > ?dob2insigDateChars, ?dob1insigDateChars, ?dob2insigDateChars) AS ?dobCommonInsigDateChars)
BIND(SUBSTR(?dob1fullStr, 1, STRLEN(?dob1fullStr) - 10 - ?dobCommonInsigDateChars) AS ?dob1CommonSignificantStr)
BIND(SUBSTR(?dob2fullStr, 1, STRLEN(?dob2fullStr) - 10 - ?dobCommonInsigDateChars) AS ?dob2CommonSignificantStr)
BIND(STRDT(CONCAT(?dob1CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobCommonInsigDateChars)), xsd:dateTime) AS ?dob1CommonSignificant)
BIND(STRDT(CONCAT(?dob2CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dobCommonInsigDateChars)), xsd:dateTime) AS ?dob2CommonSignificant)
BIND((CEIL(ABS((?dob1CommonSignificant - ?dob2CommonSignificant) / 3.652425))) / 100 AS ?dobdiff)
OPTIONAL {
?item p:P570/psv:P570 [
wikibase:timeValue ?dod1Val;
wikibase:timePrecision ?dod1Prec ] .
BIND(STR(?dod1Val) AS ?dod1fullStr)
BIND(IF(?dod1Prec <= 10, 3, 0) AS ?dod1insigDayChars)
BIND(IF(?dod1Prec <= 9, 3, 0) AS ?dod1insigMonthChars)
BIND(IF(?dod1Prec <= 8, 9-?dod1Prec, 0) AS ?dod1insigYearChars)
BIND(?dod1insigDayChars + ?dod1insigMonthChars + ?dod1insigYearChars AS ?dod1insigDateChars)
BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dod1insigDateChars) AS ?dod1significantStr)
BIND(CONCAT(?dod1significantStr, SUBSTR("0000000000000000", 1, ?dod1insigYearChars)) AS ?dod1displayStr)
}
OPTIONAL {
?item2 p:P570/psv:P570 [
wikibase:timeValue ?dod2Val;
wikibase:timePrecision ?dod2Prec ] .
BIND(STR(?dod2Val) AS ?dod2fullStr)
BIND(IF(?dod2Prec <= 10, 3, 0) AS ?dod2insigDayChars)
BIND(IF(?dod2Prec <= 9, 3, 0) AS ?dod2insigMonthChars)
BIND(IF(?dod2Prec <= 8, 9-?dod2Prec, 0) AS ?dod2insigYearChars)
BIND(?dod2insigDayChars + ?dod2insigMonthChars + ?dod2insigYearChars AS ?dod2insigDateChars)
BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dod2insigDateChars) AS ?dod2significantStr)
BIND(CONCAT(?dod2significantStr, SUBSTR("0000000000000000", 1, ?dod2insigYearChars)) AS ?dod2displayStr)
}
BIND(IF(?dod1insigDateChars > ?dod2insigDateChars, ?dod1insigDateChars, ?dod2insigDateChars) AS ?dodCommonInsigDateChars)
BIND(SUBSTR(?dod1fullStr, 1, STRLEN(?dod1fullStr) - 10 - ?dodCommonInsigDateChars) AS ?dod1CommonSignificantStr)
BIND(SUBSTR(?dod2fullStr, 1, STRLEN(?dod2fullStr) - 10 - ?dodCommonInsigDateChars) AS ?dod2CommonSignificantStr)
BIND(STRDT(CONCAT(?dod1CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodCommonInsigDateChars)), xsd:dateTime) AS ?dod1CommonSignificant)
BIND(STRDT(CONCAT(?dod2CommonSignificantStr, SUBSTR("0000000000000000-01-01T00:00:00Z", 23 - ?dodCommonInsigDateChars)), xsd:dateTime) AS ?dod2CommonSignificant)
BIND((CEIL(ABS((?dod1CommonSignificant - ?dod2CommonSignificant) / 3.652425))) / 100 AS ?doddiff)
BIND(BOUND(?dobdiff) AS ?dobdiffbound)
BIND(BOUND(?doddiff) AS ?doddiffbound)
BIND(IF(BOUND(?dobdiff), ?dobdiff, 0) AS ?dobdiffsafe)
BIND(IF(BOUND(?doddiff), ?doddiff, 0) AS ?doddiffsafe)
BIND((CEIL((?dobdiffsafe + ?doddiffsafe) / (xsd:integer(?dobdiffbound) + xsd:integer(?doddiffbound)) * 100) / 100) AS ?meandiff)
}
ORDER BY (!(?dobdiffbound) && !(?doddiffbound)) ?meandiff (!(?dobdiffbound)) ?dobdiff (!(?doddiffbound)) ?doddiff ?item
LIMIT 500