Update Materialized View

[sgoggins]

We need a new definition of the following materialized view, as reflected in the SQL statement below
This is for the 8Knot application

DROP MATERIALIZED VIEW "augur_data"."explorer_contributor_actions";

CREATE MATERIALIZED VIEW "augur_data"."explorer_contributor_actions"
AS
SELECT x.cntrb_id,
    x.created_at,
    x.repo_id,
    x.repo_name,
    x.login,
    x.action,
    x.rank
   FROM ( SELECT b.cntrb_id,
            b.created_at,
            b.month,
            b.year,
            b.repo_id,
            b.repo_name,
            b.full_name,
            b.login,
            b.action,
            b.rank
           FROM ( SELECT a.id AS cntrb_id,
                    a.created_at,
                    date_part('month'::text, (a.created_at)::date) AS month,
                    date_part('year'::text, (a.created_at)::date) AS year,
                    a.repo_id,
                    repo.repo_name,
                    a.full_name,
                    a.login,
                    a.action,
                    rank() OVER (PARTITION BY a.id, a.repo_id ORDER BY a.created_at) AS rank
                   FROM ( SELECT canonical_full_names.canonical_id AS id,
                            issues.created_at,
                            issues.repo_id,
                            'issue_opened'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM ((augur_data.issues
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = issues.reporter_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE (issues.pull_request IS NULL)
                          GROUP BY canonical_full_names.canonical_id, issues.repo_id, issues.created_at, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT canonical_full_names.canonical_id AS id,
                            to_timestamp((commits.cmt_author_date)::text, 'YYYY-MM-DD'::text) AS created_at,
                            commits.repo_id,
                            'commit'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM ((augur_data.commits
                             LEFT JOIN augur_data.contributors ON (((contributors.cntrb_canonical)::text = (commits.cmt_author_email)::text)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_canonical)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          GROUP BY commits.repo_id, canonical_full_names.canonical_email, canonical_full_names.canonical_id, commits.cmt_author_date, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT message.cntrb_id AS id,
                            commit_comment_ref.created_at,
                            commits.repo_id,
                            'commit_comment'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM augur_data.commit_comment_ref,
                            augur_data.commits,
                            ((augur_data.message
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = message.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE ((commits.cmt_id = commit_comment_ref.cmt_id) AND (commit_comment_ref.msg_id = message.msg_id))
                          GROUP BY message.cntrb_id, commits.repo_id, commit_comment_ref.created_at, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT issue_events.cntrb_id AS id,
                            issue_events.created_at,
                            issues.repo_id,
                            'issue_closed'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM augur_data.issues,
                            ((augur_data.issue_events
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = issue_events.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE ((issues.issue_id = issue_events.issue_id) AND (issues.pull_request IS NULL) AND (issue_events.cntrb_id IS NOT NULL) AND ((issue_events.action)::text = 'closed'::text))
                          GROUP BY issue_events.cntrb_id, issues.repo_id, issue_events.created_at, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT pull_request_events.cntrb_id AS id,
                            pull_request_events.created_at,
                            pull_requests.repo_id,
                            ('pull_request_'::text || (pull_request_events.action)::text) AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM augur_data.pull_requests,
                            ((augur_data.pull_request_events
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = pull_request_events.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE ((pull_requests.pull_request_id = pull_request_events.pull_request_id) AND (pull_request_events.cntrb_id IS NOT NULL) AND ((pull_request_events.action)::text = ANY ((ARRAY['merged'::character varying, 'closed'::character varying])::text[])))
                          GROUP BY pull_request_events.cntrb_id, pull_requests.repo_id, pull_request_events.action, pull_request_events.created_at, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT pull_request_reviews.cntrb_id AS id,
                            pull_request_reviews.pr_review_submitted_at AS created_at,
                            pull_requests.repo_id,
                            ('pull_request_review_'::text || (pull_request_reviews.pr_review_state)::text) AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM augur_data.pull_requests,
                            ((augur_data.pull_request_reviews
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = pull_request_reviews.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE ((pull_requests.pull_request_id = pull_request_reviews.pull_request_id) AND (pull_request_reviews.cntrb_id IS NOT NULL))
                          GROUP BY pull_request_reviews.cntrb_id, pull_requests.repo_id, pull_request_reviews.pr_review_state, pull_request_reviews.pr_review_submitted_at, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT pull_requests.pr_augur_contributor_id AS id,
                            pull_requests.pr_created_at AS created_at,
                            pull_requests.repo_id,
                            'open_pull_request'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM ((augur_data.pull_requests
                             LEFT JOIN augur_data.contributors ON ((pull_requests.pr_augur_contributor_id = contributors.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          GROUP BY pull_requests.pr_augur_contributor_id, pull_requests.repo_id, pull_requests.pr_created_at, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT message.cntrb_id AS id,
                            message.msg_timestamp AS created_at,
                            pull_requests.repo_id,
                            'pull_request_comment'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM augur_data.pull_requests,
                            augur_data.pull_request_message_ref,
                            ((augur_data.message
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = message.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE ((pull_request_message_ref.pull_request_id = pull_requests.pull_request_id) AND (pull_request_message_ref.msg_id = message.msg_id))
                          GROUP BY message.cntrb_id, pull_requests.repo_id, message.msg_timestamp, contributors.cntrb_full_name, contributors.cntrb_login
                        UNION ALL
                         SELECT issues.reporter_id AS id,
                            message.msg_timestamp AS created_at,
                            issues.repo_id,
                            'issue_comment'::text AS action,
                            contributors.cntrb_full_name AS full_name,
                            contributors.cntrb_login AS login
                           FROM augur_data.issues,
                            augur_data.issue_message_ref,
                            ((augur_data.message
                             LEFT JOIN augur_data.contributors ON ((contributors.cntrb_id = message.cntrb_id)))
                             LEFT JOIN ( SELECT DISTINCT ON (contributors_1.cntrb_canonical) contributors_1.cntrb_full_name,
                                    contributors_1.cntrb_canonical AS canonical_email,
                                    contributors_1.data_collection_date,
                                    contributors_1.cntrb_id AS canonical_id
                                   FROM augur_data.contributors contributors_1
                                  WHERE ((contributors_1.cntrb_canonical)::text = (contributors_1.cntrb_email)::text)
                                  ORDER BY contributors_1.cntrb_canonical) canonical_full_names ON (((canonical_full_names.canonical_email)::text = (contributors.cntrb_canonical)::text)))
                          WHERE ((issue_message_ref.msg_id = message.msg_id) AND (issues.issue_id = issue_message_ref.issue_id))
                          GROUP BY issues.reporter_id, issues.repo_id, message.msg_timestamp, contributors.cntrb_full_name, contributors.cntrb_login) a,
                    augur_data.repo
                  WHERE ((a.id IS NOT NULL) AND (a.repo_id = repo.repo_id))
                  GROUP BY a.id, a.repo_id, a.action, a.created_at, repo.repo_name, a.full_name, a.login
                  ORDER BY a.created_at DESC) b) x
  ORDER BY x.created_at DESC;

ALTER MATERIALIZED VIEW "augur_data"."explorer_contributor_actions" OWNER TO "augur";

[sgoggins]

This is fixed in the latest release.