team_meeting_2.sql

-- /usr/local/pgsql/bin/psql postgres://citus:o8PyA_-OFBq6cQO88gfipw@c.fx2kdxziaw5epni332ukk4al72q.db.citusdata.com:5432/citus?sslmode=require


-- get the latest commits from the top 5 contributors in a particular repo
PREPARE latest_commits(text) AS
WITH postgres_commits AS (
  SELECT                                                                                                                                                      
    created_at, jsonb_array_elements(payload->'commits') AS comm 
  FROM
    github.events
  WHERE
    repo->>'name' = $1 AND payload->>'ref' = 'refs/heads/master'
),
commits_by_author AS (
  SELECT
    created_at, comm->'author'->>'name' AS author, comm->>'message' AS message
  FROM
    postgres_commits
),
top_contributors AS (                                                                                                                                         
  SELECT                                                                                                                                                      
    author, count(*)
  FROM
    commits_by_author
  GROUP BY 1 ORDER BY 2 DESC LIMIT 5
)
SELECT
  author, created_at, string_agg(message,'\n') AS latest_messages
FROM
  commits_by_author c JOIN top_contributors USING (author)
WHERE
  created_at = (SELECT max(created_at) FROM commits_by_author WHERE author = c.author)
GROUP BY
  author, created_at, top_contributors.count
ORDER BY
  top_contributors.count DESC;

EXECUTE latest_commits ('postgres/postgres');
EXECUTE latest_commits ('citusdata/citus');


-- which other repos have postgres contributors committed to
PREPARE other_repos(text) AS
SELECT
  repo_name,
  repo_authors.author,
  count(*)
FROM (
  SELECT
    repo_id,
    repo->>'name' repo_name,
    jsonb_array_elements(payload->'commits')->'author'->>'name'  author
  FROM
    github.events
  GROUP BY
    1, 2, 3
) all_authors
JOIN (
  SELECT
    DISTINCT jsonb_array_elements(payload->'commits')->'author'->>'name' author
  FROM
    github.events
  WHERE
    repo->>'name' = $1 AND payload->>'ref' = 'refs/heads/master'
) repo_authors
ON (all_authors.author = repo_authors.author)
GROUP BY
  1, 2
ORDER BY
  3 DESC;


EXECUTE other_repos('postgres/postgres');