Untitled

WITH base_repos AS (
    SELECT repo_name 
    FROM `repo_data.repo_list`
),

-- Extract PR metadata from PullRequestEvent
pr_base AS (
    SELECT
        repo.name AS repo_name,
        CAST(JSON_EXTRACT_SCALAR(payload, '$.pull_request.number') AS INT64) AS pr_number,
        JSON_EXTRACT_SCALAR(payload, '$.pull_request.title') AS pr_title,
        JSON_EXTRACT_SCALAR(payload, '$.pull_request.state') AS pr_state,
        CAST(JSON_EXTRACT_SCALAR(payload, '$.pull_request.merged') AS BOOL) AS is_merged,
        JSON_EXTRACT_SCALAR(payload, '$.pull_request.body') AS description,
        JSON_EXTRACT_SCALAR(payload, '$.pull_request.created_at') AS pr_created_at,
        actor.login AS author
    FROM `githubarchive.month.*`
    WHERE 
        _TABLE_SUFFIX BETWEEN '201801' AND '202312'
        AND type = 'PullRequestEvent'
        AND JSON_EXTRACT_SCALAR(payload, '$.action') = 'opened'  -- Get only PR creation events
        AND repo.name IN (SELECT repo_name FROM base_repos)
),

-- Extract PR discussion comments from IssueCommentEvent
pr_comments AS (
    SELECT
        repo.name AS repo_name,
        CAST(JSON_EXTRACT_SCALAR(payload, '$.issue.number') AS INT64) AS pr_number,
        JSON_EXTRACT_SCALAR(payload, '$.comment.body') AS text,
        actor.login AS author,
        JSON_EXTRACT_SCALAR(payload, '$.comment.created_at') AS created_at
    FROM `githubarchive.month.*`
    WHERE 
        _TABLE_SUFFIX BETWEEN '201801' AND '202312'
        AND type = 'IssueCommentEvent'
        AND JSON_EXTRACT_SCALAR(payload, '$.issue.pull_request') IS NOT NULL  -- Ensure it's a PR comment
        AND repo.name IN (SELECT repo_name FROM base_repos)
)

-- Aggregate everything by PR
SELECT 
    pr.repo_name,
    pr.pr_number,
    pr.pr_title,
    pr.pr_state,
    pr.is_merged,
    pr.description,
    pr.pr_created_at,
    pr.author AS pr_author,
    ARRAY_AGG(
        STRUCT(
            pc.text AS text,
            pc.author AS author,
            pc.created_at AS created_at
        )
        ORDER BY pc.created_at ASC
    ) AS comments
FROM pr_base pr
LEFT JOIN pr_comments pc
    ON pr.repo_name = pc.repo_name AND pr.pr_number = pc.pr_number
GROUP BY 
    pr.repo_name, pr.pr_number, pr.pr_title, pr.pr_state, 
    pr.is_merged, pr.description, pr.pr_created_at, pr.author
ORDER BY pr.pr_created_at DESC
LIMIT 100;