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.
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
- Python
- GraphQL
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.
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.
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
- Python
- GraphQL
select
collection_name,
display_name
from `oso_production.collections_v1`
order by collection_name
query = """
select
collection_name,
display_name
from `oso_production.collections_v1`
order by collection_name
"""
df = client.query(query).to_dataframe()
query Collections {
oso_collectionsV1 {
collectionName
displayName
}
}
Projects in a Collection
Get the names of all projects in a collection:
- SQL
- Python
- GraphQL
select
project_id,
project_name
from `oso_production.projects_by_collection_v1`
where collection_name = 'gg-01'
query = """
select
project_id,
project_name
from `oso_production.projects_by_collection_v1`
where collection_name = 'gg-01'
"""
df = client.query(query).to_dataframe()
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
- Python
- GraphQL
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'
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()
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
- Python
- GraphQL
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'
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()
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
- Python
- GraphQL
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'
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()
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.