Courses/CGBV SS24/Graph: Difference between revisions

Latest revision as of 11:53, 23 May 2024

Contains queries about the graph of the "CGBV SS24" course to help debug and structure it.

All Sessions

Returns a graph containing all sessions included in the course, as well as all included topics.

All existing CGBV SS24 sessions with included topics

All Categories

Returns a graph with all categories included in the course. See the query here

Indegrees

Returns a graph that uses the indegrees to size items. I.e. the size of a node depends on how many prerequisites it has.

Nodes that aren't cicles are items, that are linked to course-items, but are themselves not included in the course.

Items

Orphans

Unlinked Items	No dependencies
Items that don't depend on and aren't depended on	Items that don't depend on other items
Link to the query	Link to the query

Resources

All Resources

Returns a table with all resources that are linked to from inside the course.

Items used: CGBV 24SS (Q932)

Properties used: includes (P14), resource (P21), url (P20), instance of (P3)

#defaultView:Table
PREFIX wdt: <https://graphit.ur.de/prop/direct/>
PREFIX wd: <https://graphit.ur.de/entity/>
SELECT DISTINCT ?item ?itemLabel ?resource ?resourceLabel ?alias ?url ?type ?typeLabel # ?resLabelLength 
WHERE {
  wd:Q932 wdt:P14/wdt:P14 ?item.
  ?item wdt:P21 ?resource.
  OPTIONAL {?resource wdt:P20 ?link.
            BIND("true" as ?url) }
  
  OPTIONAL {?resource wdt:P3 ?type.}
  OPTIONAL {?resource skos:altLabel ?alias.}

  service wikibase:label { bd:serviceParam wikibase:language "en" }
} ORDER BY ASC(?resourceLabel)

Try it!

Items without Resources

Returns a table with all items in the CGBV 24SS course, that don't link to a resource.

"Interests" and "Knowledge"

✒️ Note: The plot doesn't really show overlapping points

Items used: CGBV 24SS (Q932)

Properties used: includes (P14), has completed (P12), interested in (P23)

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

SELECT DISTINCT ?known ?interests ?item ?itemLabel # ?known ?interests ?item ?itemLabel 
WHERE {
  wd:Q932 wdt:P14/wdt:P14 ?item.
  OPTIONAL {
    SELECT ?item ?itemLabel (COUNT(DISTINCT ?student) as ?known) WHERE {
      ?student wdt:P12 ?item.
    } GROUP BY ?item ?itemLabel
  }
  OPTIONAL {
    SELECT ?item ?itemLabel (COUNT(DISTINCT ?student) as ?interests) WHERE {
      ?student wdt:P23 ?item.
    } GROUP BY ?item ?itemLabel
  }.
  BIND (IF(!BOUND(?interests), 0, ?interest) as ?interests).
  BIND (IF(!BOUND(?known), 0, ?known) as ?known).
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Alternative Visualization

Interests and knowledge are mapped to color and to size. The size equals to the sum of interest and knowledge.

A red node means that more students are interested in a topic. A green node means that more students know a topic.

A olive node means that one more student knows a topic than are interested in.

A gray node meant that the same amount of students know/are interested in a topic

@@ Line 30: / Line 30: @@
 Returns a graph with all categories '''included in''' the course.
 See the query '''[https://tinyurl.com/yorv5zu2 here]'''
+== Indegrees ==
+Returns a graph that uses the indegrees to size items. I.e. the size of a node depends on how many prerequisites it has.
+{{#widget:SPARQLquery|code=
+#defaultView:Graph
+PREFIX wd: <https://graphit.ur.de/entity/>
+PREFIX wdt: <https://graphit.ur.de/prop/direct/>
+SELECT distinct ?topic ?topicLabel ?rgb1 # ?shape1
+?dCount
+?dependency ?dependencyLabel # ?rgb2 ?shape2
+?tCount
+# ?category ?categoryLabel ?rgb3 ?shape3
+WHERE {
+  wd:Q932 wdt:P14/wdt:P14 ?topic.
+  OPTIONAL { ?topic wdt:P1 ?dependency }
+  OPTIONAL {
+    SELECT ?topic ?topicLabel (COUNT(DISTINCT ?pre) as ?dCount) WHERE {
+      ?pre wdt:P1 ?topic.
+    } GROUP BY ?topic ?topicLabel
+  } # number of items that -depend-on-> ?topic
+  BIND(IF(!BOUND(?dCount), 0, ?dCount) as ?dCount).
+  # STYLING #
+  BIND (if(?dCount = 0, "FFFFFF", # white
+          if(?dCount = 1, "e6e6e6",
+          if(?dCount = 2, "DFDFDF",
+          if(?dCount = 3, "b3b3b3",
+          if(?dCount = 4, "9F9F9F",
+          if(?dCount = 5, "808080",
+          if(?dCount = 6,  "606060",
+          if(?dCount = 7, "4d4d4d",
+          if(?dCount = 8, "333333",
+          if(?dCount = 9, "1919191",
+          if(?dCount = 10, "202020",
+          if(?dCount > 10, "292929",
+       "FFFFFF" )))))))))))) as ?rgb1).
+  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
+}
+}}
+<small> Nodes that aren't cicles are items, that are linked to course-items, but are themselves not included in the course.</small>
 == Items ==
@@ Line 118: / Line 160: @@
 }
 }}
+== "Interests" and "Knowledge" ==
+{{Note|text=The plot doesn't really show overlapping points}}
+{{SPARQL2|query=
+#defaultView:ScatterChart
+PREFIX wd: <https://graphit.ur.de/entity/>
+PREFIX wdt: <https://graphit.ur.de/prop/direct/>
+SELECT DISTINCT ?known ?interests ?item ?itemLabel # ?known ?interests ?item ?itemLabel
+WHERE {
+  wd:Q932 wdt:P14/wdt:P14 ?item.
+  OPTIONAL {
+    SELECT ?item ?itemLabel (COUNT(DISTINCT ?student) as ?known) WHERE {
+      ?student wdt:P12 ?item.
+    } GROUP BY ?item ?itemLabel
+  }
+  OPTIONAL {
+    SELECT ?item ?itemLabel (COUNT(DISTINCT ?student) as ?interests) WHERE {
+      ?student wdt:P23 ?item.
+    } GROUP BY ?item ?itemLabel
+  }.
+  BIND (IF(!BOUND(?interests), 0, ?interest) as ?interests).
+  BIND (IF(!BOUND(?known), 0, ?known) as ?known).
+  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
+}
+}}
+'''Alternative Visualization'''
+{{#widget:SPARQLquery|code=
+#defaultView:Graph
+PREFIX wd: <https://graphit.ur.de/entity/>
+PREFIX wdt: <https://graphit.ur.de/prop/direct/>
+SELECT DISTINCT ?sum ?item ?itemLabel ?rgb ?known ?interests
+WHERE {
+  wd:Q932 wdt:P14/wdt:P14 ?item.
+  OPTIONAL {
+    SELECT ?item ?itemLabel (COUNT(DISTINCT ?student) as ?known) WHERE {
+      ?student wdt:P12 ?item.
+    } GROUP BY ?item ?itemLabel
+  }
+  OPTIONAL {
+    SELECT ?item ?itemLabel (COUNT(DISTINCT ?student) as ?interests) WHERE {
+      ?student wdt:P23 ?item.
+    } GROUP BY ?item ?itemLabel
+  }.
+  BIND (IF(!BOUND(?interests), 0, ?interest) as ?interests).
+  BIND (IF(!BOUND(?known), 0, ?known) as ?known).
+  BIND ( ?interests + ?known as ?sum)
+  # STYLING #
+  BIND (if(?interests = 0 && ?known = 0, "FFFFFF", # white
+          if(?interests = ?known, "DFDFDF", # grey
+          if(?interests = 1 && ?known = 0, "e3ffd9", # light green
+          if(?interests = 0 && ?known = 1, "F9DCDF", # light red
+          if(?interests+1 = ?known,  "BECF96", # red+green -> both
+          if(?interests < ?known, "9FE586", # green, bc. knowledge is "good" :D
+          if(?interests > ?known, "F0A8AF", # red
+       "FFFFFF" ))))))) as ?rgb).
+  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
+} ORDER BY DESC(?sum)
+}}
+<span style="background-color:#F0A8AF">'''Interests'''</span> and <span style="background-color:#9FE586">'''knowledge'''</span> are mapped to color and to size.
+The '''size''' equals to the sum of interest and knowledge.
+A <span style="background-color:#F0A8AF">red</span> node means that '''more''' students are <span style="background-color:#F0A8AF">interested</span> in a topic.
+A <span style="background-color:#9FE586">green</span> node means that '''more''' students <span style="background-color:#9FE586">know</span> a topic.
+A <span style="background-color:#BECF96">olive</span> node means that '''one more''' student knows a topic than are interested in.
+A <span style="background-color:#DFDFDF">gray</span> node meant that the '''same''' amount of students know/are interested in a topic