New query for group-level dependency list page sorting by `severity`

Why are we doing this work

We need to implement this SQL query to retrieve records performantly.

WITH namespaces AS MATERIALIZED (
  SELECT 
    traversal_ids::bigint[]
  FROM 
    "namespaces" 
  WHERE 
    "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))
)
SELECT
  occurrences.component_version_id,
  SUM(occurrence_count) AS occurrence_count,
  SUM(project_count) AS project_count
FROM
  (SELECT
    DISTINCT ON (component_name, component_version_id)
    occurrences.id,
    occurrences.component_version_id,
    occurrences.component_name
  FROM
    namespaces,
    LATERAL (
      SELECT
        DISTINCT ON (component_name, component_version_id)
        sbom_occurrences.id,
        sbom_occurrences.component_version_id,
        sbom_occurrences.component_name
      FROM
        sbom_occurrences
      WHERE
        sbom_occurrences.archived IS FALSE AND
        sbom_occurrences.traversal_ids = namespaces.traversal_ids
      ORDER BY sbom_occurrences.component_name ASC, sbom_occurrences.component_version_id ASC
      LIMIT 25
    ) occurrences
  ORDER BY occurrences.component_name ASC, occurrences.component_version_id ASC
  LIMIT 25) AS occurrences,
  LATERAL (
    SELECT
      COUNT(project_id) AS occurrence_count,
      COUNT(DISTINCT project_id) project_count
    FROM
      sbom_occurrences
    WHERE
      sbom_occurrences.archived IS FALSE AND
      sbom_occurrences.component_version_id = occurrences.component_version_id AND
      sbom_occurrences.traversal_ids >= '{9970}'::bigint[] AND
      sbom_occurrences.traversal_ids < '{9971}'::bigint[]
  ) occurrences_agg
GROUP BY occurrences.component_name, occurrences.component_version_id
ORDER BY MIN(occurrences.component_name) ASC, MIN(occurrences.component_version_id) ASC

This query will perform keyset pagination.

Implementation plan

• database Implement the aforementioned query
• database Add the necessary indices

Verification steps

TBD