20 Matching Annotations
  1. Apr 2019
    1. Aspects of interest:

      • Build a temporary table that filters annotations down to just those matching a domain, and takes just the group ids from those annotations

      • Get the set of unique group ids from that result, and the user counts for each group

      • Show the group ids and counts

  2. Mar 2019
    1. Points of interest

      • Shows how to get the length of a column value that is a list of things

      • Shows how to convert the list to a string in order to pattern-match across all tags in the list

      • Shows how to use the || operator to wrap the wildcard characters around a variable

  3. Mar 2018
    1. Points of interest:

      • Uses regular expressions -- fancy pattern matching -- to scan the text of annotations

      • This one just looks for ?, the idea being a teacher is looking for students asking questions

      • Could also look for dates, currency, links, images, videos, lots of things

    1. Points of interest

      • A highlight is an annotation with no text or tags.

      • This query has to perform some gymnastics to find the set of annotations that meet those two conditions

      • Gymnastics also required for the percentages

      • This pattern work for other things like: annotations vs pagenotes, public vs private, etc.

    1. Aspects of interest:

      • Uses distinct to get the set of unique users from all users who annotated a given doc
    1. Aspects of interest

      • Looks at the registered_date for users

      • Filters by domain wildcards

      • Then extrapolates to all signups, according to a 56/44 ratio established elsewhere

    1. Aspects of interest:

      • Joins annotations table and document_uri table

      • Looks for doi-related metadata

    1. Aspects of interest:

      • Finds distinct urls annotated by any CF annotator

      • Finds the average of the dates on the annotation for each url, so those sets of annotations can be arranged on a timeline

    1. Aspects of interest

      • Accesses the user_group table

      • Quotes the "group" field to distinguish it from the group keyword

      • Applies the lower string function to make matching more robust

      • Joins the user_group and group tables in order to show user counts per group

    1. Aspects of interest

      • Shows how to count different things in the same query

      • Shows how to use distinct to deduplicate

    1. Aspects of interest:

      • Uses form-based variables to parameterize the query. (

      • Formats results as a bar chart

    1. Aspects of interest:

      • Truncate date to day

      • Filter on a specified recent span of days

      • Use a complex expression to match a specified set of users

    1. Aspects of interest:

      • Uses WITH ("WITH provides a way to write auxiliary statements for use in a larger query. These statements can be thought of as defining temporary tables that exist just for one query")

      • Uses wildcard to match tlds: .edu, .ac.uk

      • Uses regular expression to match subdomains of email addresses (e.g. cornell. nyu)

      • uses array indexing to slice the regexp match

      • use || operator to join strings

      -