query-783ab81339beb86125d101f6695efe02

Ambassadorial roles with the proportion of holders who were MPs (former or future)

Use at

https://query.wikidata.org/sparql

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
select distinct ?item ?itemLabel ?number_of_holders ?percent_mp_before ?percent_mp_after ?percent_mp_ever where
{
  { ?item wdt:P31 wd:Q18115939 . } union { ?item wdt:P31 wd:Q56760832 } . # ambassador or high commissioner
   { select distinct ?item (count(distinct ?holder) as ?number_of_holders) where
    {
      { ?item wdt:P31 wd:Q18115939 . } union { ?item wdt:P31 wd:Q56760832 } .
      ?holder p:P39 ?positionStatement . ?positionStatement ps:P39 ?item . 
    } group by ?item
  }
 optional { select distinct ?item (count(distinct ?holder) as ?after) where
    {
      { ?item wdt:P31 wd:Q18115939 . } union { ?item wdt:P31 wd:Q56760832 } .
      ?holder p:P39 ?positionStatement . ?positionStatement ps:P39 ?item . ?positionStatement pq:P580 ?poststart .
      ?holder p:P39 ?positionStatement2 . ?positionStatement2 ps:P39 ?mp .
      { ?mp wdt:P279* wd:Q16707842 } union { ?mp wdt:P279* wd:Q18015642 } union { ?mp wdt:P279* wd:Q18018860 } . 
      ?positionStatement2 pq:P580 ?mpstart . filter (?mpstart >= ?poststart) . 
    } group by ?item
 }
 optional { select distinct ?item (count(distinct ?holder) as ?before) where
    {
      { ?item wdt:P31 wd:Q18115939 . } union { ?item wdt:P31 wd:Q56760832 } .
      ?holder p:P39 ?positionStatement . ?positionStatement ps:P39 ?item . ?positionStatement pq:P580 ?poststart .
      ?holder p:P39 ?positionStatement2 . ?positionStatement2 ps:P39 ?mp .
      { ?mp wdt:P279* wd:Q16707842 } union { ?mp wdt:P279* wd:Q18015642 } union { ?mp wdt:P279* wd:Q18018860 } . 
      ?positionStatement2 pq:P582 ?mpend . filter (?mpend <= ?poststart) . 
    } group by ?item
  } 
  { select distinct ?item (count(distinct ?holder) as ?ever) where
    {
      { ?item wdt:P31 wd:Q18115939 . } union { ?item wdt:P31 wd:Q56760832 } .
      ?holder p:P39 ?positionStatement . ?positionStatement ps:P39 ?item . 
      ?holder p:P39 ?positionStatement2 . ?positionStatement2 ps:P39 ?mp .
      { ?mp wdt:P279* wd:Q16707842 } union { ?mp wdt:P279* wd:Q18015642 }  union { ?mp wdt:P279* wd:Q18018860 } . 
    } group by ?item
  }
  BIND(ROUND(100*(?after/?number_of_holders)) as ?percent_mp_after) .
  BIND(ROUND(100*(?before/?number_of_holders)) as ?percent_mp_before) .
  BIND(ROUND(100*(?ever/?number_of_holders)) as ?percent_mp_ever) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} order by desc(?number_of_holders)

Query found at

https://www.wikidata.org/wiki/Wikidata:WikiProject_British_Politicians/ambassadors

graph TD classDef projected fill:lightgreen; classDef literal fill:orange; classDef iri fill:yellow; v9("?after") v11("?before") v12("?ever") v3("?holder") v2("?item"):::projected v8("?mp") v10("?mpend") v5("?mpstart") v5("?number_of_holders"):::projected v13("?percent_mp_after"):::projected v14("?percent_mp_before"):::projected v15("?percent_mp_ever"):::projected v4("?positionStatement") v7("?positionStatement2") v6("?poststart") c2(["wd:Q18115939"]):::iri c3(["wd:Q56760832"]):::iri c10(["wd:Q18018860"]):::iri c8(["wd:Q16707842"]):::iri c13(["bd:serviceParam"]):::iri c15(["#91;AUTO_LANGUAGE#93;,en"]):::literal c9(["wd:Q18015642"]):::iri subgraph union0[" Union "] subgraph union0l[" "] style union0l fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c3 end subgraph union0r[" "] style union0r fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c2 end union0r <== or ==> union0l end subgraph union1[" Union "] subgraph union1l[" "] style union1l fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c3 end subgraph union1r[" "] style union1r fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c2 end union1r <== or ==> union1l end v3 --"p:P39"--> v4 v4 --"p:statement/P39"--> v2 bind1[/"count(?holder)"/] v3 --o bind1 bind1 --as--o v5 subgraph optional0["(optional)"] style optional0 fill:#bbf,stroke-dasharray: 5 5; f2[["?mpstart >= ?poststart"]] f2 --> v5 f2 --> v6 subgraph union2[" Union "] subgraph union2l[" "] style union2l fill:#abf,stroke-dasharray: 3 3; v2 -."p:direct/P31".-> c3 end subgraph union2r[" "] style union2r fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c2 end union2r <== or ==> union2l end v3 --"p:P39"--> v4 v4 --"p:statement/P39"--> v2 v4 --"p:qualifier/P580"--> v6 v3 --"p:P39"--> v7 v7 --"p:statement/P39"--> v8 subgraph union3[" Union "] subgraph union3l[" "] style union3l fill:#abf,stroke-dasharray: 3 3; subgraph union4[" Union "] subgraph union4l[" "] style union4l fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c10 end subgraph union4r[" "] style union4r fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c9 end union4r <== or ==> union4l end end subgraph union3r[" "] style union3r fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c8 end union3r <== or ==> union3l end v7 --"p:qualifier/P580"--> v5 bind4[/"count(?holder)"/] v3 --o bind4 bind4 --as--o v9 end subgraph optional1["(optional)"] style optional1 fill:#bbf,stroke-dasharray: 5 5; f5[["?mpend <= ?poststart"]] f5 --> v10 f5 --> v6 subgraph union5[" Union "] subgraph union5l[" "] style union5l fill:#abf,stroke-dasharray: 3 3; v2 -."p:direct/P31".-> c3 end subgraph union5r[" "] style union5r fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c2 end union5r <== or ==> union5l end v3 --"p:P39"--> v4 v4 --"p:statement/P39"--> v2 v4 --"p:qualifier/P580"--> v6 v3 --"p:P39"--> v7 v7 --"p:statement/P39"--> v8 subgraph union6[" Union "] subgraph union6l[" "] style union6l fill:#abf,stroke-dasharray: 3 3; subgraph union7[" Union "] subgraph union7l[" "] style union7l fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c10 end subgraph union7r[" "] style union7r fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c9 end union7r <== or ==> union7l end end subgraph union6r[" "] style union6r fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c8 end union6r <== or ==> union6l end v7 --"p:qualifier/P582"--> v10 bind7[/"count(?holder)"/] v3 --o bind7 bind7 --as--o v11 end subgraph union8[" Union "] subgraph union8l[" "] style union8l fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c3 end subgraph union8r[" "] style union8r fill:#abf,stroke-dasharray: 3 3; v2 --"p:direct/P31"--> c2 end union8r <== or ==> union8l end v3 --"p:P39"--> v4 v4 --"p:statement/P39"--> v2 v3 --"p:P39"--> v7 v7 --"p:statement/P39"--> v8 subgraph union9[" Union "] subgraph union9l[" "] style union9l fill:#abf,stroke-dasharray: 3 3; subgraph union10[" Union "] subgraph union10l[" "] style union10l fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c10 end subgraph union10r[" "] style union10r fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c9 end union10r <== or ==> union10l end end subgraph union9r[" "] style union9r fill:#abf,stroke-dasharray: 3 3; v8 --"p:direct/P279"--> c8 end union9r <== or ==> union9l end bind9[/"count(?holder)"/] v3 --o bind9 bind9 --as--o v12 bind10[/"numeric-round('100^^xsd:integer' * ?after / ?number_of_holders)"/] v9 --o bind10 v5 --o bind10 bind10 --as--o v13 bind11[/"numeric-round('100^^xsd:integer' * ?before / ?number_of_holders)"/] v11 --o bind11 v5 --o bind11 bind11 --as--o v14 bind12[/"numeric-round('100^^xsd:integer' * ?ever / ?number_of_holders)"/] v12 --o bind12 v5 --o bind12 bind12 --as--o v15 subgraph s1["http://wikiba.se/ontology#label"] style s1 stroke-width:4px; c13 --"wikibase:language"--> c15 end