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

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.

Aspects of interest:

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

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

Aspects of interest:

• Joins annotations table and document_uri table

• Looks for doi-related metadata

Aspects of interest:

• Counting annotations that are replies

Illustrates use of the document_uri table where aliases are gathered.

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

Aspects of interest

• Uses over() to build a % of total column

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

Aspects of interest

• Shows how to count different things in the same query

• Shows how to use distinct to deduplicate

Aspects of interest:

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

• Formats results as a bar chart

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

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

-