New Metric for Developer

This week, as I finish up the new correlations, I will also be working to create a new metric for developers in our database. We are creating an attribute called "missed vulnerability" which will count the number of times a developer committed to a file within a year of that file being fixed for a vulnerability. This query is not very expensive, but it is very complicated and requires a comparison of two different complex join results. I have completed half of the query at the current moment, and will have it finished and added to the analysis by next week. We are also considering another metric "missed vulnerability" that is less strict and will flag not just developers who committed that file within the year, but any developer who participated in a review with that file within the year it was fixed for a vulnerability.

From UPDATE: here is the finished query I completed with lots of help from professor meneely! From this query, we count the number of times a developer occurs in the results.

SELECT DISTINCT ON(code_reviews_cvenums.cvenum_id, code_reviews.issue, missed_code_reviews.issue, participants.dev_id)
      code_reviews_cvenums.cvenum_id,
      code_reviews.issue,
      fix_commits.created_at,
      missed_code_reviews.issue,
      missed_commits.created_at,
      participants.dev_id,
      missed_commit_filepaths.filepath
FROM code_reviews
     INNER JOIN code_reviews_cvenums ON code_reviews.issue = code_reviews_cvenums.code_review_id
     INNER JOIN commits AS fix_commits ON code_reviews.commit_hash = fix_commits.commit_hash
     INNER JOIN commit_filepaths AS fix_commit_filepaths ON fix_commits.commit_hash = fix_commit_filepaths.commit_hash
     INNER JOIN commit_filepaths AS missed_commit_filepaths ON missed_commit_filepaths.filepath = fix_commit_filepaths.filepath
     INNER JOIN commits AS missed_commits ON missed_commits.commit_hash = missed_commit_filepaths.commit_hash AND missed_commits.created_at > (fix_commits.created_at - interval '1 year') AND missed_commits.created_at < fix_commits.created_at
     INNER JOIN code_reviews AS missed_code_reviews ON missed_code_reviews.commit_hash = missed_commits.commit_hash
     INNER JOIN participants ON participants.issue = missed_code_reviews.issue
ORDER BY code_reviews_cvenums.cvenum_id, code_reviews.issue, missed_code_reviews.issue, participants.dev_id;