Query to extract the top three Survey IDs with the highest row count in the SCV for each tab

This query can be amended as per the need.

WITH section_counts AS (
SELECT
id,
survey_id,
COALESCE(jsonb_array_length(response->'companyDetails'), 0) AS companyDetails,
COALESCE(jsonb_array_length(response->'siteList'), 0) AS siteList,
COALESCE(jsonb_array_length(response->'partSiteMap'), 0) AS partSiteMap,
COALESCE(jsonb_array_length(response->'companyContact'), 0) AS companyContact,
COALESCE(jsonb_array_length(response->'siteContact'), 0) AS siteContact,
COALESCE(jsonb_array_length(response->'subTierSupplier'), 0) AS subTierSupplier,
COALESCE(jsonb_array_length(response->'subTierPartSourcing'), 0) AS subTierPartSourcing,
COALESCE(jsonb_array_length(response->'subTierSites'), 0) AS subTierSites
FROM scv_data_storage
WHERE id = original_row_id
),
top_3_per_section AS (
SELECT
'companyDetails' AS section_name,
survey_id,
companyDetails AS count,
ROW_NUMBER() OVER (ORDER BY companyDetails DESC, survey_id) AS rank
FROM section_counts
WHERE companyDetails > 0

UNION ALL

SELECT
'siteList' AS section_name,
survey_id,
siteList AS count,
ROW_NUMBER() OVER (ORDER BY siteList DESC, survey_id) AS rank
FROM section_counts
WHERE siteList > 0

UNION ALL

SELECT
'partSiteMap' AS section_name,
survey_id,
partSiteMap AS count,
ROW_NUMBER() OVER (ORDER BY partSiteMap DESC, survey_id) AS rank
FROM section_counts
WHERE partSiteMap > 0

UNION ALL

SELECT
'companyContact' AS section_name,
survey_id,
companyContact AS count,
ROW_NUMBER() OVER (ORDER BY companyContact DESC, survey_id) AS rank
FROM section_counts
WHERE companyContact > 0

UNION ALL

SELECT
'siteContact' AS section_name,
survey_id,
siteContact AS count,
ROW_NUMBER() OVER (ORDER BY siteContact DESC, survey_id) AS rank
FROM section_counts
WHERE siteContact > 0

UNION ALL

SELECT
'subTierSupplier' AS section_name,
survey_id,
subTierSupplier AS count,
ROW_NUMBER() OVER (ORDER BY subTierSupplier DESC, survey_id) AS rank
FROM section_counts
WHERE subTierSupplier > 0

UNION ALL

SELECT
'subTierPartSourcing' AS section_name,
survey_id,
subTierPartSourcing AS count,
ROW_NUMBER() OVER (ORDER BY subTierPartSourcing DESC, survey_id) AS rank
FROM section_counts
WHERE subTierPartSourcing > 0

UNION ALL

SELECT
'subTierSites' AS section_name,
survey_id,
subTierSites AS count,
ROW_NUMBER() OVER (ORDER BY subTierSites DESC, survey_id) AS rank
FROM section_counts
WHERE subTierSites > 0
)
SELECT
section_name,
jsonb_agg(
jsonb_build_object(
'survey_id', survey_id,
'count', count,
'rank', rank
) ORDER BY rank
) AS top_3_surveys
FROM top_3_per_section
WHERE rank <= 3
GROUP BY section_name
 ORDER BY section_name;

Decimal's query also returns the same information for all assessments. The engineering query above retrieves only the top three assessments, whereas the Decimal query below returns the results for all SCV assessments, for your reference.

Note: This query displays replicated rows, so each row count appears twice.

SELECT
survey_id,
jsonb_array_length(response->'companyDetails') AS company_details_rows,
jsonb_array_length(response->'siteList') AS site_list_rows,
jsonb_array_length(response->'partSiteMap') AS part_site_map_rows,
jsonb_array_length(response->'companyContact') AS company_contact_rows,
jsonb_array_length(response->'siteContact') AS site_contact_rows,
jsonb_array_length(response->'subTierSupplier') AS sub_tier_supplier_rows,
jsonb_array_length(response->'subTierPartSourcing') AS sub_tier_part_sourcing_rows,
jsonb_array_length(response->'subTierSites') AS sub_tier_sites_rows
FROM scv_data_storage
ORDER BY part_site_map_rows DESC NULLS LAST