View a Collection of Projects

Get a high level view of key metrics for a collection of projects. New to OSO? Check out our Getting Started guide to set up your BigQuery or API access.

tip

All collections are defined as YAML files in OSS Directory. View our current collections here.

Getting Started

Before running any analysis, you'll need to set up your environment:

SQL

If you haven't already, subscribe to OSO public datasets in BigQuery by clicking the "Subscribe" button on our Datasets page.

You can run all queries in this guide directly in the BigQuery console.

Python

Start your Python notebook with the following:

from google.cloud import bigquery
import pandas as pd
import os

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = # PATH TO YOUR CREDENTIALS JSON
GCP_PROJECT = # YOUR GCP PROJECT NAME

client = bigquery.Client(GCP_PROJECT)

For more details on setting up Python notebooks, see our guide on writing Python notebooks.

GraphQL

The following queries should work if you copy-paste them into our GraphQL sandbox. For more information on how to use the GraphQL API, check out our GraphQL guide.

Query Collections

All Collections

Get the names of all collections on OSO:

SQL

select
  collection_name,
  display_name
from `oso_production.collections_v1`
order by collection_name

Python

query = """
  select
    collection_name,
    display_name
  from `oso_production.collections_v1`
  order by collection_name
"""
df = client.query(query).to_dataframe()

GraphQL

query Collections {
  oso_collectionsV1 {
    collectionName
    displayName
  }
}

Projects in a Collection

Get the names of all projects in a collection:

SQL

select
  project_id,
  project_name
from `oso_production.projects_by_collection_v1`
where collection_name = 'gg-01'

Python

query = """
  select
    project_id,
    project_name
  from `oso_production.projects_by_collection_v1`
  where collection_name = 'gg-01'
"""
df = client.query(query).to_dataframe()

GraphQL

query ProjectsInCollection {
  oso_projectsByCollectionV1(where: { collectionName: { _eq: "gg-01" } }) {
    projectId
    projectName
  }
}

Collection-level Metrics

Code Metrics

Get code metrics for all projects in a collection:

SQL

select cm.*
from `oso_production.code_metrics_by_project_v1` as cm
join `oso_production.projects_by_collection_v1` as pbc
  on cm.project_id = pbc.project_id
where pbc.collection_name = 'ethereum-crypto-ecosystems'

Python

query = """
  select cm.*
  from `oso_production.code_metrics_by_project_v1` as cm
  join `oso_production.projects_by_collection_v1` as pbc
    on cm.project_id = pbc.project_id
  where pbc.collection_name = 'ethereum-crypto-ecosystems'
"""
df = client.query(query).to_dataframe()

GraphQL

query CodeMetricsQuery {
  metrics: oso_codeMetricsByProjectV1 {
    projectId
    starCount
    forkCount
    commitCount6Months
    contributorCount6Months
  }
  projects: oso_projectsByCollectionV1(
    where: { collectionName: { _eq: "ethereum-crypto-ecosystems" } }
  ) {
    projectId
  }
}

Onchain Metrics

Get onchain metrics for all projects in a collection:

SQL

select om.*
from `oso_production.onchain_metrics_by_project_v1` as om
join `oso_production.projects_by_collection_v1` as pbc
  on om.project_id = pbc.project_id
where pbc.collection_name = 'optimism'

Python

query = """
  select om.*
  from `oso_production.onchain_metrics_by_project_v1` as om
  join `oso_production.projects_by_collection_v1` as pbc
    on om.project_id = pbc.project_id
  where pbc.collection_name = 'optimism'
"""
df = client.query(query).to_dataframe()

GraphQL

query OnchainMetricsQuery {
  metrics: oso_onchainMetricsByProjectV1 {
    projectId
    transactionCount6Months
    gasFeesSum6Months
    newAddressCount90Days
  }
  projects: oso_projectsByCollectionV1(
    where: { collectionName: { _eq: "optimism" } }
  ) {
    projectId
  }
}

Funding Metrics

Get funding metrics for all projects in a collection:

SQL

select fm.*
from `oso_production.funding_metrics_by_project_v1` as fm
join `oso_production.projects_by_collection_v1` as pbc
  on fm.project_id = pbc.project_id
where pbc.collection_name = 'op-rpgf3'

Python

query = """
  select fm.*
  from `oso_production.funding_metrics_by_project_v1` as fm
  join `oso_production.projects_by_collection_v1` as pbc
    on fm.project_id = pbc.project_id
  where pbc.collection_name = 'op-rpgf3'
"""
df = client.query(query).to_dataframe()

GraphQL

query FundingMetricsQuery {
  metrics: oso_fundingMetricsByProjectV1 {
    projectId
    totalFundingReceivedUsd6Months
  }
  projects: oso_projectsByCollectionV1(
    where: { collectionName: { _eq: "op-rpgf3" } }
  ) {
    projectId
  }
}

Adding Collections and Projects

Projects and collections are defined as YAML files in our OSS Directory repo. You can add or update your own collections and projects by submitting a pull request.

For more information on how collections work, see our guide here.