Notes

WITH m AS (
SELECT wage_determination_number AS wd, revision_number AS rev
FROM govsearch_ai.wd_master
),
loc AS (
SELECT wage_determination_number AS wd, revision_number AS rev, COUNT(*) AS loc_cnt
FROM govsearch_ai.wd_location
GROUP BY 1,2
),
occ AS (
SELECT wage_determination_number AS wd, revision_number AS rev, COUNT(*) AS occ_cnt
FROM govsearch_ai.wd_occupation
GROUP BY 1,2
)
SELECT
COUNT(*) AS total_wds,
COUNT(*) FILTER (WHERE d.wage_determination_number IS NOT NULL) AS wds_with_details,
COUNT(*) FILTER (WHERE COALESCE(loc.loc_cnt,0) > 0) AS wds_with_locations,
COUNT(*) FILTER (WHERE COALESCE(occ.occ_cnt,0) > 0) AS wds_with_occupations
FROM m
LEFT JOIN govsearch_ai.wd_master_details d
ON d.wage_determination_number = m.wd AND d.revision_number = m.rev
LEFT JOIN loc ON loc.wd = m.wd AND loc.rev = m.rev
LEFT JOIN occ ON occ.wd = m.wd AND occ.rev = m.rev;