Page Comparison

At the end of every Challenge, we aim to collect writeups from all participating users/teams, in which they will provide information about their submission(s). Currently, writeups are collected as Synapse Projects (sample template here), and we ask the participants to include information such as their methodologies, external data sources (if any), and source code and/or Docker images. Typically, writeups are *required* in every Challenge, in order to be considered for “top performer” eligibility and other incentives, including byline authorship.

...

10. (optional but recommended , especially for Displaying Writeups in a Leaderboard) Create a SubmissionView so that you can easily track and monitor the writeup submissions. We recommend the following schema for monitoring writeup submissions:

...

Once you are satisfied that the writeup workflow is to your expectations, remember to open the queue to the Challenge participants!

You can do so by updating its Sharing Settings so that the Participants team has Can submit permissions.

...

Creating this leaderboard can easily be achieved with a Materialized View table.

Requirements

• Synapse ID of SubmissionView for final submissions

• Synapse ID of SubmissionView for writeups

Steps

1. Go to the staging site and click on the Tables tab. Create a new Materialized View by clicking on Add New… > Add Materialized View.

2. Under “Defining SQL”, enter the following query:

SELECT 
  s.id AS id, 
  s.createdOn AS createdOn, 
  s.submitterid AS submitterid, 
  s.status AS status, 
  (score columns),
  w.entityid AS writeup, 
  w.archived AS archived_writeup 
FROM syn123 s 
  JOIN syn456 w
  ON (s.submitterid = w.submitterid)
WHERE s.status = 'ACCEPTED'
  AND w.status = 'ACCEPTED'

This query will join the SubmissionView for submissions (s) with the SubmissionView for writeups (w), using the submitterids from both Views as the index. Rows are filtered to only include valid submissions from both Views (*.status = 'ACCEPTED') – this clause is optional and can be removed if desired.

Columns selected from the submissions View (s) include:

• submission ID (s.id)

• submission date (s.createdOn)

• participant/team (s.submitterid)

• status (s.status)

• any annotations related to scores

Columns selected from the writeups View (w) include:

• Synapse ID of the writeup (w.entityid)

• Synapse ID of the writeup archive (w.archive)

This query is also assigning aliases, e.g. w.entityid AS writeup for readability and easier querying.

3. Click Save. A table with the selected columns from each SubmissionView will now be available for viewing and querying. It should look something like this:

...

The above MaterializedView is the result of this query:

SELECT 
  s.id AS id, 
  s.createdOn AS createdOn, 
  s.submitterid AS submitterid, 
  s.status AS status, 
  w.entityid AS writeup, 
  w.archived AS archived_writeup 
FROM syn51034012 s
  JOIN syn29582834 w
  ON (s.submitterid = w.submitterid)

4. Continuing on the staging site, switch to the Wiki tab and go to the “Results” page (create a new Wiki page if it’s not available). Click on the pencil icon to Edit Project Wiki:

...

5. In the pop-up window, click on + Insert button > Table: Query on a Synapse Table/View:

...

5. For “Query”, enter something like:

SELECT * FROM syn123

where syn123 is the Synapse ID to the MaterializedView created earlier in Step 3 (not SubmissionViews!). Optionally include aliases and/or clauses to the query if desired, e.g. exclude certain columns such as archived_writeup. Click on the Preview button in the top-left corner to view what the leaderboard would look like.

6. Click Save once you are satisfied with the final leaderboard.

7. Sync changes to the live site with challengeutils' mirror-wiki.