-- ============================================================
-- Patent Intelligence Report: VEGA Grieshaber KG
-- Generated: 2026-03-04 | PATSTAT Edition: Autumn 2025
-- Stack: PATSTAT BigQuery + patstat-mcp + Claude AI
-- ============================================================
--
-- HOW TO USE:
-- 1. Replace all instances of @ENTITY_NAME with your search term
-- 2. Adjust @START_YEAR and @END_YEAR
-- 3. Execute queries in order (Query 1-3 define the scope)
-- 4. Queries 4+ can be run independently
--
-- DEPENDENCIES:
-- Query 1: Name variants -> defines entity scope
-- Query 2: Core CTE (vega_apps) -> used by all subsequent queries
-- Query 3: Annual overview -> standalone
-- Queries 4-12: All use vega_apps CTE from Query 2
--
-- @ENTITY_NAME = '%vega grieshaber%'
-- @START_YEAR = 2014
-- @END_YEAR = 2024
-- ============================================================


-- ============================================================
-- Query 1: Name Variant Identification
-- ============================================================
SELECT DISTINCT p.han_name, p.person_name, p.person_ctry_code,
    COUNT(DISTINCT a.docdb_family_id) AS families
FROM tls206_person p
JOIN tls207_pers_appln pa ON p.person_id = pa.person_id
JOIN tls201_appln a ON pa.appln_id = a.appln_id
WHERE pa.applt_seq_nr > 0
  AND (LOWER(p.person_name) LIKE '%vega grieshaber%'
    OR LOWER(p.han_name) LIKE '%vega grieshaber%')
  AND a.appln_filing_year BETWEEN 2000 AND 2024
GROUP BY p.han_name, p.person_name, p.person_ctry_code
ORDER BY families DESC
LIMIT 30;


-- ============================================================
-- Query 2: KPI Summary
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id, a.appln_filing_year,
        a.appln_auth, a.granted, a.nb_applicants
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0
      AND a.ipr_type = 'PI'
)
SELECT
    COUNT(DISTINCT docdb_family_id) AS total_families,
    COUNT(*) AS total_applications,
    COUNT(DISTINCT appln_auth) AS offices,
    COUNT(DISTINCT CASE WHEN granted = 'Y' THEN docdb_family_id END) AS granted_families,
    COUNT(DISTINCT CASE WHEN nb_applicants > 1 THEN docdb_family_id END) AS co_filed_families
FROM vega_apps;


-- ============================================================
-- Query 3: Annual Filing Trend (earliest year per family)
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id, a.appln_filing_year
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
),
family_year AS (
    SELECT docdb_family_id, MIN(appln_filing_year) AS earliest_year
    FROM vega_apps GROUP BY docdb_family_id
)
SELECT earliest_year AS year, COUNT(*) AS families
FROM family_year
GROUP BY earliest_year ORDER BY earliest_year;


-- ============================================================
-- Query 4: Filing Jurisdictions
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id, a.appln_auth
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
)
SELECT appln_auth AS office,
    COUNT(DISTINCT docdb_family_id) AS families,
    COUNT(*) AS applications
FROM vega_apps
GROUP BY appln_auth
ORDER BY families DESC;


-- ============================================================
-- Query 5: Technology Profile (CPC subclass)
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
)
SELECT SUBSTR(c.cpc_class_symbol, 1, 4) AS cpc4,
    COUNT(DISTINCT va.docdb_family_id) AS families
FROM vega_apps va
JOIN tls224_appln_cpc c ON va.appln_id = c.appln_id
GROUP BY cpc4
ORDER BY families DESC
LIMIT 25;


-- ============================================================
-- Query 6: Technology Sub-areas (measurement techniques)
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
),
tech_areas AS (
    SELECT DISTINCT va.docdb_family_id,
        CASE
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/28') THEN 'Radar Level (G01F23/28x)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/296') THEN 'Ultrasonic Level (G01F23/296)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/26') THEN 'Capacitive Level (G01F23/26x)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/80') THEN 'Signal Processing (G01F23/80x)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/22') THEN 'Physical Variable Level (G01F23/22)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*25/') THEN 'Calibration/Testing (G01F25/)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01L') THEN 'Pressure Measurement (G01L)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01S\s*13/') THEN 'Radar Systems (G01S13/)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01S\s*7/') THEN 'Radar Signal Proc. (G01S7/)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^H01Q') THEN 'Antenna Tech (H01Q)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G05B') THEN 'Industrial Control (G05B)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^H04[QLW]') THEN 'Communication/IoT (H04)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01N\s*9/') THEN 'Density Measurement (G01N9/)'
            WHEN REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01T') THEN 'Radiation Measurement (G01T)'
            ELSE NULL
        END AS tech_area
    FROM vega_apps va
    JOIN tls224_appln_cpc c ON va.appln_id = c.appln_id
)
SELECT tech_area, COUNT(DISTINCT docdb_family_id) AS families
FROM tech_areas WHERE tech_area IS NOT NULL
GROUP BY tech_area ORDER BY families DESC;


-- ============================================================
-- Query 7: Competitor Landscape in G01F 23/ (Level Measurement)
-- ============================================================
WITH competitor_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id, p.han_name, p.person_ctry_code
    FROM tls201_appln a
    JOIN tls224_appln_cpc c ON a.appln_id = c.appln_id
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND (REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/')
        OR REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*25/')
        OR REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*22/'))
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
)
SELECT han_name, person_ctry_code,
    COUNT(DISTINCT docdb_family_id) AS families
FROM competitor_apps
GROUP BY han_name, person_ctry_code
ORDER BY families DESC LIMIT 30;


-- ============================================================
-- Query 8: Competitor Annual Trend in G01F 23/
-- ============================================================
WITH comp_families AS (
    SELECT DISTINCT a.docdb_family_id, MIN(a.appln_filing_year) AS earliest_year,
        p.han_name
    FROM tls201_appln a
    JOIN tls224_appln_cpc c ON a.appln_id = c.appln_id
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND REGEXP_CONTAINS(c.cpc_class_symbol, r'^G01F\s*23/')
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
      AND p.han_name IN ('VEGA GRIESHABER KG', 'ENDRESS HAUSER GMBH CO KG',
          'ROSEMOUNT TANK RADAR AB', 'HONEYWELL INT INC', 'KROHNE MESSTECHNIK GMBH', 'IFM ELECT GMBH')
    GROUP BY a.docdb_family_id, p.han_name
)
SELECT han_name, earliest_year AS year, COUNT(*) AS families
FROM comp_families
GROUP BY han_name, earliest_year
ORDER BY han_name, earliest_year;


-- ============================================================
-- Query 9: Technology Heatmap (Competitors x CPC subclass)
-- ============================================================
WITH core_competitors AS (
    SELECT 'VEGA GRIESHABER KG' AS company UNION ALL
    SELECT 'ENDRESS HAUSER GMBH CO KG' UNION ALL
    SELECT 'ROSEMOUNT TANK RADAR AB' UNION ALL
    SELECT 'HONEYWELL INT INC' UNION ALL
    SELECT 'KROHNE MESSTECHNIK GMBH' UNION ALL
    SELECT 'IFM ELECT GMBH'
),
comp_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id, p.han_name,
        SUBSTR(c.cpc_class_symbol, 1, 4) AS cpc4
    FROM tls201_appln a
    JOIN tls224_appln_cpc c ON a.appln_id = c.appln_id
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND p.han_name IN (SELECT company FROM core_competitors)
      AND SUBSTR(c.cpc_class_symbol, 1, 4) IN ('G01F', 'G01S', 'G01D', 'G01L', 'G01N', 'G05B', 'H01Q')
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
)
SELECT han_name, cpc4,
    COUNT(DISTINCT docdb_family_id) AS families
FROM comp_apps
GROUP BY han_name, cpc4
ORDER BY han_name, families DESC;


-- ============================================================
-- Query 10: Top Inventors (deduplicated by doc_std_name_id)
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
)
SELECT MIN(p2.person_name) AS inventor,
    p2.doc_std_name_id,
    COUNT(DISTINCT va.docdb_family_id) AS families
FROM vega_apps va
JOIN tls207_pers_appln pa2 ON va.appln_id = pa2.appln_id
JOIN tls206_person p2 ON pa2.person_id = p2.person_id
WHERE pa2.invt_seq_nr > 0
  AND p2.doc_std_name_id IS NOT NULL AND p2.doc_std_name_id > 0
GROUP BY p2.doc_std_name_id
ORDER BY families DESC LIMIT 15;


-- ============================================================
-- Query 11: Grant Rates by Office
-- ============================================================
WITH vega_apps AS (
    SELECT DISTINCT a.appln_id, a.docdb_family_id, a.appln_auth, a.granted
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
)
SELECT appln_auth AS office,
    COUNT(DISTINCT docdb_family_id) AS total_families,
    COUNT(DISTINCT CASE WHEN granted = 'Y' THEN docdb_family_id END) AS granted_families,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN granted = 'Y' THEN docdb_family_id END)
        / NULLIF(COUNT(DISTINCT docdb_family_id), 0), 1) AS grant_rate_pct
FROM vega_apps
WHERE appln_auth IN ('DE', 'EP', 'US', 'CN', 'WO')
GROUP BY appln_auth
ORDER BY total_families DESC;


-- ============================================================
-- Query 12: PCT Rate
-- ============================================================
WITH vega_families AS (
    SELECT DISTINCT a.docdb_family_id
    FROM tls201_appln a
    JOIN tls207_pers_appln pa ON a.appln_id = pa.appln_id
    JOIN tls206_person p ON pa.person_id = p.person_id
    WHERE pa.applt_seq_nr > 0
      AND LOWER(p.han_name) LIKE '%vega grieshaber%'
      AND a.appln_filing_year BETWEEN 2014 AND 2024
      AND a.docdb_family_id > 0 AND a.ipr_type = 'PI'
),
wo_families AS (
    SELECT DISTINCT a.docdb_family_id
    FROM tls201_appln a
    WHERE a.docdb_family_id IN (SELECT docdb_family_id FROM vega_families)
      AND a.appln_auth = 'WO'
)
SELECT
    (SELECT COUNT(*) FROM vega_families) AS total_families,
    (SELECT COUNT(*) FROM wo_families) AS families_with_wo,
    ROUND(100.0 * (SELECT COUNT(*) FROM wo_families)
        / NULLIF((SELECT COUNT(*) FROM vega_families), 0), 1) AS pct_rate;