Skip to main content

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:

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.

Query Collections

All Collections

Get the names of all collections on OSO:

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

Projects in a Collection

Get the names of all projects in a collection:

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

Collection-level Metrics

Code Metrics

Get code metrics for all projects in a collection:

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'

Onchain Metrics

Get onchain metrics for all projects in a collection:

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'

Funding Metrics

Get funding metrics for all projects in a collection:

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'

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.