OpenAlex breakdown by DDH journals

ddh_in_openalex.sql

WITH
  ddh_issns AS (
    SELECT
      id_ddh,
      u.issn_val,
      u.issn_type
    FROM
      subugoe-collaborative.resources.diamond_discovery_hub,
      UNNEST(
        [
          STRUCT(issn_print AS issn_val, 'print' AS issn_type),
          STRUCT(issn_electronic AS issn_val, 'electronic' AS issn_type)]) AS u
    WHERE u.issn_val IS NOT NULL
  ),
  oa_with_issn AS (
    SELECT
      id AS openalex_id,
      open_access.oa_status,
      publication_year,
      primary_location.source.issn_l,
      issn_val
    FROM
      subugoe-collaborative.openalex_walden.works,
      UNNEST(primary_location.source.issn) AS issn_val
    WHERE primary_location.source.issn IS NOT NULL AND publication_year BETWEEN 2008 AND 2025
  ),
  deduped AS (
    SELECT
      ddh.id_ddh,
      oa.openalex_id,
      oa.oa_status,
      publication_year
    FROM ddh_issns ddh
    JOIN oa_with_issn oa
      ON ddh.issn_val = oa.issn_val
    QUALIFY
      ROW_NUMBER()
        OVER (
          PARTITION BY ddh.id_ddh, oa.openalex_id
          ORDER BY ddh.issn_type
        )
      = 1
  )
SELECT
  oa_status, publication_year,
  COUNT(DISTINCT openalex_id) AS num_works
FROM deduped
GROUP BY oa_status, publication_year