Fix vulnerability statistics calculation for A grade projects

What does this MR do and why?

This change fixes the vulnerability statistics calculation query for A letter grade projects(projects without detected, and confirmed vulnerabilities).

Database review

This MR changes a query to always return a row for all the given project IDs.

New query

WITH project_ids AS (
  SELECT unnest(ARRAY[278964]::bigint[]) AS project_id
)
SELECT
  project_ids.project_id AS project_id,
  COALESCE(severity_counts.total, 0) AS total,
  COALESCE(severity_counts.info, 0) AS info,
  COALESCE(severity_counts.unknown, 0) AS unknown,
  COALESCE(severity_counts.low, 0) AS low,
  COALESCE(severity_counts.medium, 0) AS medium,
  COALESCE(severity_counts.high, 0) AS high,
  COALESCE(severity_counts.critical, 0) AS critical,
  (
    CASE
    WHEN severity_counts.critical > 0 THEN
      4
    WHEN severity_counts.high > 0 OR severity_counts.unknown > 0 THEN
      3
    WHEN severity_counts.medium > 0 THEN
      2
    WHEN severity_counts.low > 0 THEN
      1
    ELSE
      0
    END
  ) AS letter_grade,
  now() AS created_at,
  now() AS updated_at
FROM
  project_ids
LEFT OUTER JOIN(
  SELECT
    vulnerability_reads.project_id AS project_id,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE severity = 1) as info,
    COUNT(*) FILTER (WHERE severity = 2) as unknown,
    COUNT(*) FILTER (WHERE severity = 4) as low,
    COUNT(*) FILTER (WHERE severity = 5) as medium,
    COUNT(*) FILTER (WHERE severity = 6) as high,
    COUNT(*) FILTER (WHERE severity = 7) as critical
  FROM vulnerability_reads
  WHERE
    vulnerability_reads.project_id IN (278964) AND
    vulnerability_reads.state IN (1, 4)
  GROUP BY vulnerability_reads.project_id
) AS severity_counts ON severity_counts.project_id = project_ids.project_id

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22988/commands/74083

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.