WITH s_disrupt AS (
    SELECT s_id, id_firm, id_product, MIN(ts) AS ts, ga_id
    FROM iiabmdb_20250925.without_exp_result
    WHERE `status` = 'D'
    GROUP BY s_id, id_firm, id_product, ga_id
),

s_multiple AS (
    -- 筛选出现超过1次的 s_id
    SELECT s_id, ga_id
    FROM s_disrupt
    GROUP BY s_id, ga_id
    HAVING COUNT(*) > 1
),

product_count AS (
    -- 统计每个 ga_id 下每个产品出现次数
    SELECT s.ga_id, s.id_product, COUNT(*) AS cnt
    FROM s_disrupt s
    JOIN s_multiple m ON s.s_id = m.s_id AND s.ga_id = m.ga_id
    GROUP BY s.ga_id, s.id_product
),

ranked_products AS (
    -- 按每个 ga_id 的产品出现次数排序，并取前35
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ga_id ORDER BY cnt DESC) AS rn
    FROM product_count
)

SELECT ga_id, id_product, cnt
FROM ranked_products
WHERE rn <= 20
ORDER BY ga_id, rn;
