Document dependency graph use cases

[ccerv1]

What is it?

DYI developer report

select distinct
  p.display_name,
  pkg.package_artifact_source as pkg_source,
  pkg.package_owner_artifact_namespace as pkg_maintainer,
  round(cm.avg_active_devs_6_months) as avg_active_devs,
  count(distinct pkg.package_artifact_name) as num_packages_maintained
from `oso.sboms_v0` sbom
join `oso.projects_v1` p
  on sbom.from_project_id = p.project_id
join `oso.package_owners_v0` pkg
  on sbom.to_package_artifact_name = pkg.package_artifact_name
  and sbom.to_package_artifact_source = pkg.package_artifact_source
join `oso.code_metrics_by_project` cm
  on pkg.package_owner_project_id = cm.project_id
where p.project_name like '%kiwi-news%'
group by 1,2,3,4
order by 4 desc

[ccerv1]

SELECT
  onchain_projects.project_name AS `onchain_builder`,
  onchain_metrics.event_source AS `network`,
  onchain_metrics.address_count_90_days,
  onchain_metrics.gas_fees_sum_6_months,
  onchain_metrics.transaction_count_6_months AS transactions_6_months,
  code_metrics.project_name AS `dev_tool_maintainer`,
  package_owners.package_artifact_source AS `package_source`,
  code_metrics.active_developer_count_6_months,
  code_metrics.contributor_count_6_months,
  code_metrics.commit_count_6_months,
  code_metrics.opened_issue_count_6_months,
  code_metrics.opened_pull_request_count_6_months,
  code_metrics.fork_count,
  code_metrics.star_count,
  code_metrics.last_updated_at_date
FROM `oso.sboms_v0` sboms
JOIN `oso.projects_v1` onchain_projects
  ON sboms.from_project_id = onchain_projects.project_id
JOIN `oso.projects_by_collection_v1` projects_by_collection
  ON onchain_projects.project_id = projects_by_collection.project_id
JOIN `oso.onchain_metrics_by_project_v1` onchain_metrics
  ON onchain_projects.project_id = onchain_metrics.project_id
JOIN `oso.package_owners_v0` package_owners
  ON sboms.to_package_artifact_name = package_owners.package_artifact_name
JOIN `oso.code_metrics_by_project_v1` code_metrics
  ON package_owners.package_owner_project_id = code_metrics.project_id
WHERE
  projects_by_collection.collection_name = 'op-retrofunding-4'
  and transaction_count_6_months >= 1000
  and address_count_90_days >= 420

[ccerv1]

Determine the most commonly used dependencies in OSO:

select
  p.project_id,
  pkgs.package_artifact_source,
  pkgs.package_artifact_name,
  count(distinct sboms.from_project_id) as num_dependents
from `oso.package_owners_v0` pkgs
join `oso.sboms_v0` sboms
  on pkgs.package_artifact_name = sboms.to_package_artifact_name
  and pkgs.package_artifact_source = sboms.to_package_artifact_source
join `oso.projects_v1` p
  on pkgs.package_owner_project_id = p.project_id
where pkgs.package_owner_project_id is not null
group by 1,2,3
order by 4 desc

[ccerv1]

What is hardhat a dependency of?

select *
from `oso.sboms_v0`
where
  to_package_artifact_name = 'hardhat'
  and concat(from_artifact_namespace, '/', from_artifact_name) in (
    'prysmaticlabs/prysm','sigp/lighthouse','consensys/teku','status-im/nimbus-eth2','chainsafe/lodestar','grandinetech/grandine'
    'ethereum/go-ethereum','nethermindeth/nethermind','hyperledger/besu','erigontech/erigon','paradigmxyz/reth','ethereum/solidity'
    'ethereum/remix-project','vyperlang/vyper','ethereum/web3.py','ethereum/py-evm','eth-infinitism/account-abstraction','safe-global'
    'safe-smart-account','a16z/helios','web3/web3.js','ethereumjs/ethereumjs-monorepo'
  )

[ccerv1]

Getting historic Gitcoin Grants funding:

with gitcoin_projects as (
  select
    oso_project_id,
    sum(amount_in_usd) as gitcoin_grants_usd
  from `oso.gitcoin_funding_events_by_project_v0`
  group by 1
)

select distinct
  p.display_name as package_owner_project,
  pkg.package_owner_artifact_namespace as package_owner_github_org,  
  gitcoin_projects.gitcoin_grants_usd,
  round(cm.avg_active_devs_6_months) as avg_active_devs,
  count(distinct pkg.package_artifact_name) as num_packages_maintained,
  array_agg(distinct pkg.package_artifact_source) as package_types
from `oso.sboms_v0` sbom
join `oso.package_owners_v0` pkg
  on sbom.to_package_artifact_name = pkg.package_artifact_name
  and sbom.to_package_artifact_source = pkg.package_artifact_source
join `oso.code_metrics_by_project` cm
  on pkg.package_owner_project_id = cm.project_id
join gitcoin_projects
  on pkg.package_owner_project_id = gitcoin_projects.oso_project_id
join `oso.projects_v1` p
  on pkg.package_owner_project_id = p.project_id
group by 1,2,3,4
order by 3 desc

[ccerv1]

Get funding and repo stats:

with seed_repos as (
  select distinct 
    concat(from_artifact_namespace, '/', from_artifact_name) as repo_full_name
  from `oso.sboms_v0`
  where concat(from_artifact_namespace, '/', from_artifact_name) in (
    'prysmaticlabs/prysm','sigp/lighthouse','consensys/teku','status-im/nimbus-eth2','chainsafe/lodestar','grandinetech/grandine',
    'ethereum/go-ethereum','nethermindeth/nethermind','hyperledger/besu','erigontech/erigon','paradigmxyz/reth',
    'ethereum/solidity','ethereum/remix-project','vyperlang/vyper','ethereum/web3.py','ethereum/py-evm',
    'eth-infinitism/account-abstraction','safe-global/safe-smart-account','a16z/helios',
    'web3/web3.js','ethereumjs/ethereumjs-monorepo'
  )
),

graph as (
  select distinct
    sboms.from_project_id as oso_project_id,
    sboms.from_artifact_namespace as repo_owner,
    sboms.from_artifact_name as repo_name,
    'seed' as repo_type
  from `oso.sboms_v0` sboms
  join seed_repos sr 
    on concat(sboms.from_artifact_namespace, '/', sboms.from_artifact_name) = sr.repo_full_name

  union all

  select distinct
    package_owners.package_owner_project_id as oso_project_id,
    package_owners.package_owner_artifact_namespace as repo_owner,
    package_owners.package_owner_artifact_name as repo_name,
    'dependency' as repo_type
  from `oso.sboms_v0` sboms
  join `oso.package_owners_v0` package_owners
    on sboms.to_package_artifact_name = package_owners.package_artifact_name
    and sboms.to_package_artifact_source = package_owners.package_artifact_source
  join seed_repos sr 
    on concat(sboms.from_artifact_namespace, '/', sboms.from_artifact_name) = sr.repo_full_name
  where sboms.to_package_artifact_source in ('NPM','RUST','GO','PIP')
    and package_owners.package_owner_artifact_namespace is not null
),

gitcoin_projects as (
  select
    oso_project_id,
    sum(amount_in_usd) as gitcoin_grants_usd,
    count(distinct donor_address) as unique_donors,
    count(distinct round_number) as num_rounds
  from `oso.gitcoin_funding_events_by_project_v0`
  where oso_project_id is not null and donor_address is not null
  group by oso_project_id
),

retrofunded_projects as (
  select
    p.project_id,
    sum(f.amount) as retro_funding_usd,
    count(distinct f.grant_pool_name) as num_retro_funding_rounds
  from `static_data_sources.oss_funding_v1` f
  join `oso.projects_v1` p
    on f.to_project_name = p.project_name
  where
    f.grant_pool_name like '%retro%'
    and f.from_funder_name = 'optimism'
  group by p.project_id
)

select
  g.oso_project_id,
  g.repo_owner,
  g.repo_name,
  g.repo_type,
  repos.star_count,
  repos.fork_count,
  repos.is_fork,
  repos.language,
  repos.license_name,
  repos.created_at,
  repos.updated_at,
  coalesce(gp.gitcoin_grants_usd, 0) as gitcoin_grants_usd,
  coalesce(gp.unique_donors, 0) as gitcoin_unique_donors,
  coalesce(gp.num_rounds, 0) as gitcoin_num_rounds,
  coalesce(rf.retro_funding_usd, 0) as retro_funding_usd,
  coalesce(rf.num_retro_funding_rounds, 0) as num_retro_funding_rounds
from graph g
join `oso.repositories_v0` repos
  on
    g.repo_owner = lower(repos.artifact_namespace)
    and g.repo_name = lower(repos.artifact_name)
left join gitcoin_projects gp
  on g.oso_project_id = gp.oso_project_id
left join retrofunded_projects rf
  on g.oso_project_id = rf.project_id