Courses/EIMI 25WS/Queries

Total completions

Query gets the count of total number of completions for a node by students that participate in EIMI 25WS and show that counts using colors.

When commenting out the variable ?completions the sizing of the nodes is removed, but also the tooltips that show the number of completions of a node on hover

Items used: EIMI 25/26WS (Q2113)

Properties used: includes (P14), depends on (P1), has completed (P12), participates in (P25)

#defaultView:Graph
PREFIX wd: <https://graphit.ur.de/entity/>
PREFIX wdt: <https://graphit.ur.de/prop/direct/>
SELECT distinct 
?topic ?topicLabel ?rgb1 
?completions # for sizing and value tooltip on hover
?dependency ?dependencyLabel
WHERE {
  # color items according to their number of completions

  { wd:Q2113 wdt:P14/wdt:P14 ?topic. 
    OPTIONAL { 
      ?topic wdt:P1 ?dependency 
      BIND (IF(EXISTS{?user wdt:P12 ?dependency}, "008F0E", "ffffff") as ?rgb2)
    }
  } UNION {
    # get all dependencies not directly included ( for sizing they need to be in the same column as ?topic ()
    wd:Q2113 wdt:P14/wdt:P14 ?item.
    ?item wdt:P1 ?topic.
    FILTER NOT EXISTS {  wd:Q2113 wdt:P14/wdt:P14 ?topic. } 
  }
  
  OPTIONAL {
    SELECT ?topic ?topicLabel (COUNT(DISTINCT ?student) AS ?completions) WHERE {
      ?student wdt:P25 wd:Q2113.
      ?student wdt:P12 ?topic.  
    } GROUP BY ?topic ?topicLabel
  }
  BIND(IF(!BOUND(?completions), 0, ?completions) as ?completions).
  
  # Styling
    bind (if(?completions > 50, "60BB3F",
          if(?completions > 35, "81D064",
          if(?completions > 20, "9FE586", # green
          if(?completions > 10 , "B7ECA5", 
          if(?completions > 5 , "CFF2C3", 
          if(?completions > 2 , "E7F9E1",
       "FFFFFF" )))))) as ?rgb1).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Info on Students

This Query retuns all students that participate in EIMI 25WS and counts their completions, interest and checks if they added the Statement: instance of Student to their user-item.

Students lacking the participate-Property are not part of the result.

Items used: EIMI 25/26WS (Q2113), Student (Q167)

Properties used: participates in (P25), has completed (P12), interested in (P23), instance of (P3)

#defaultView:Table
PREFIX wd: <https://graphit.ur.de/entity/>
PREFIX wdt: <https://graphit.ur.de/prop/direct/>

SELECT DISTINCT ?user ?userLabel ?interest ?known ?is_student
WHERE {
  ?user wdt:P25 wd:Q2113.

  OPTIONAL { # "has completed"
    SELECT ?user ?userLabel (COUNT(DISTINCT ?item) as ?known) WHERE {
      ?user wdt:P12 ?item.
    } GROUP BY ?user ?userLabel
  }
  
  OPTIONAL { # "interested in"
    SELECT ?user ?userLabel (COUNT(DISTINCT ?item) as ?interest) WHERE {
      ?user wdt:P23 ?item.
    } GROUP BY ?user ?userLabel
  }
  
  BIND (IF(!BOUND(?interest), 0, ?interest) as ?interest).
  BIND (IF(!BOUND(?known), 0, ?known) as ?known).
  
  BIND(EXISTS { ?user wdt:P3 wd:Q167 } AS ?is_student) # "instance of" student

  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!