How to List Debian Packages Across All Linux Devices

SELECT device_name, name, version FROM linux_debian_packages WHERE name = 'zoom'

WITH 
-- Linux packages can come from two different sources (rpm_packages, and debian_packages)
-- We combine two queries using a UNION ALL and matching the columns
union_linux_packages AS (
  SELECT 
    device_name, 
    'dpkg' AS source, 
    name, 
    version 
  FROM linux_debian_packages 
  UNION ALL
  SELECT 
    device_name, 
    'rpm' AS source, 
    name, 
    version 
  FROM linux_rpm_packages
)
SELECT * FROM union_linux_packages WHERE name = 'clamav'

WITH 
-- Linux packages can come from two different sources (rpm_packages, and debian_packages)
-- We combine two queries using a UNION ALL and matching the columns
union_linux_packages AS (
  SELECT 
    device_name, 
    'dpkg' AS source, 
    name, 
    version AS installed_version,
    version_major AS installed_version_major,
    version_minor AS installed_version_minor,
    version_patch AS installed_version_patch,
    version_subpatch AS installed_version_subpatch
  FROM linux_debian_packages 
  UNION ALL
  SELECT 
    device_name, 
    'rpm' AS source, 
    name, 
    version AS installed_version,
    version_major AS installed_version_major,
    version_minor AS installed_version_minor,
    version_patch AS installed_version_patch,
    version_subpatch AS installed_version_subpatch
  FROM linux_rpm_packages
),
zoom_latest_prompted AS (
  SELECT 
  version AS latest_prompted_version,
  CAST(version_major AS int) AS latest_major,
  CAST(version_minor AS int) AS latest_minor,
  CAST(version_patch AS int) AS latest_patch,
  CAST(version_build AS int) AS latest_build
   FROM zoom_latest_releases 
  WHERE platform = 'linux' AND release_type = 'latest_prompted_release'
),
merge_data AS (
SELECT 
  ulp.*, zlp.*
FROM union_linux_packages ulp, zoom_latest_prompted zlp
WHERE name = 'zoom'
)
-- Evaluate installed version against latest_prompted_release
SELECT device_name, installed_version, latest_prompted_version,
  CASE WHEN (
      installed_version_major <  latest_major
   OR installed_version_major <=  latest_major AND installed_version_minor <  latest_minor
   OR installed_version_major <=  latest_major AND installed_version_minor <=  latest_minor AND installed_version_patch <  latest_patch
   OR installed_version_major <=  latest_major AND installed_version_minor <=  latest_minor AND installed_version_patch <=  latest_patch AND installed_version_subpatch <  latest_build
   ) THEN 'true' ELSE 'false' END AS zoom_requires_update
 FROM merge_data;