modquery

gistfile1.txt

WITH aggregate_data AS (
    SELECT
        crossref.published_year as year,
        -- crossref.type as type,
        COUNT(*) AS n_outputs,
        SUM(CASE WHEN coki.oa.coki.open = TRUE THEN 1 ELSE 0 END) AS n_outputs_open,
        SUM(CASE WHEN coki.oa.coki.closed = TRUE THEN 1 ELSE 0 END) AS n_outputs_closed,
        SUM(CASE WHEN coki.oa.coki.publisher_only = TRUE THEN 1 ELSE 0 END) as n_publisher_open,
        SUM(CASE WHEN coki.oa.coki.other_platform_only = TRUE THEN 1 ELSE 0 END) as n_other_platform_open,
        SUM(CASE WHEN coki.oa.coki.both = TRUE THEN 1 ELSE 0 END) as n_both_open,
    FROM
        `academic-observatory.observatory.doi20231022`
    WHERE 
        crossref.published_year >= 2000 AND crossref.published_year <= 2023
        AND crossref.type NOT IN ("dataset","database","component","report-component","peer-review","grant","proceedings","journal-issue","report-series","book-track")
        AND ARRAY_LENGTH(affiliations.institutions) > 0
    GROUP BY
        crossref.published_year
        -- crossref.type
    ORDER BY crossref.published_year DESC
)

SELECT
  year,
  -- type,
  n_outputs,
  n_outputs_open,
  n_outputs_closed,
  CASE 
    WHEN n_outputs = 0 THEN 0
    ELSE n_outputs_open * 100.0 / n_outputs
  END AS p_outputs_open,
  CASE 
    WHEN n_outputs = 0 THEN 0
    ELSE n_publisher_open * 100.0 / n_outputs
  END AS p_publisher_open,
  CASE 
    WHEN n_outputs = 0 THEN 0
    ELSE n_other_platform_open * 100.0 / n_outputs
  END AS p_other_platform_closed,
  CASE 
    WHEN n_outputs = 0 THEN 0
    ELSE n_both_open * 100.0 / n_outputs
  END AS p_both_open,
  CASE 
    WHEN n_outputs = 0 THEN 0
    ELSE n_outputs_closed * 100.0 / n_outputs
  END AS p_outputs_closed,
  (n_outputs - n_outputs_open - n_outputs_closed) as diff,
FROM aggregate_data