-- ════════════════════════════════════════════════════════════════════════════
-- 策略打分阈值分布【统一字段版】
--   字段命名按业务量分类（_pred 模型预估 / _stat 历史统计 / 其他），
--   不再暴露模型实现细节（XGB vs DNN）。
--
--   ┌─ 字段映射表 ──────────────────────────────────────────────────────┐
--   │  scoresMap key             →  本 SQL 字段        含义              │
--   │  ── 模型预估（_pred）──                                            │
--   │  fmRov                     →  str_pred           STR 分享率预估    │
--   │  fmRovOrigin               →  str_pred_origin    STR 反归一化前    │
--   │  NorXGBScore | NorDNNScore →  ros_pred           ROS 分享后回流率  │
--   │  ── 历史统计（_stat）──                                            │
--   │  vor                       →  vor_stat           视频历史曝光裂变  │
--   │  hasReturnRovScore         →  ros_stat           视频历史回流率    │
--   │  c1RovnScore               →  c1_rovn_stat       一级品类 ROVN     │
--   │  b0StrScore                →  b0_str_stat        B0 流 STR         │
--   │  b0RorScore                →  b0_ror_stat        B0 流 ROR         │
--   │  cnRovnScore               →  cn_rovn_stat       CN 流 ROVN        │
--   │  dnRovnScore               →  dn_rovn_stat       DN 流 ROVN        │
--   │  ── 其他 ──                                                        │
--   │  pLeave                    →  p_leave            离开概率原值      │
--   │  newPLeave                 →  new_p_leave        离开概率变换      │
--   │  reduceCoefficient         →  reduce_coef        重排降权系数      │
--   │  sample_all.score          →  final_score        线上最终分        │
--   └────────────────────────────────────────────────────────────────────┘
--
--   ros_pred 的 COALESCE 行为：
--     ┌─ apptype × suffix_group → 模型路径 ──┐
--     │ XGB 路径：(0/01,34,67,89,ab) (4/全部除 2c/ef) → ros_pred = NorXGBScore │
--     │ DNN 路径：(0/2c,5d,ef) (4/2c,ef)              → ros_pred = NorDNNScore │
--     └────────────────────────────────────────┘
--     COALESCE(NorXGBScore, NorDNNScore) 在主流量行级可信；
--     极少数（<1%）幻象写入会让 COALESCE 返回错误值——靠 cov 列识别（cov<5% 时 _avg 不可信）
--
--   每个分数字段输出 7 列：avg / std / min / max / p50 / p95 / cov
-- ════════════════════════════════════════════════════════════════════════════
WITH t_suffix_group AS
(
    SELECT 'a' AS suffix, 'ab' AS suffix_group
    UNION ALL SELECT 'b', 'ab'
    UNION ALL SELECT '0', '01'
    UNION ALL SELECT '1', '01'
    UNION ALL SELECT '2', '2c'
    UNION ALL SELECT 'c', '2c'
    UNION ALL SELECT '3', '34'
    UNION ALL SELECT '4', '34'
    UNION ALL SELECT '5', '5d'
    UNION ALL SELECT 'd', '5d'
    UNION ALL SELECT '6', '67'
    UNION ALL SELECT '7', '67'
    UNION ALL SELECT '8', '89'
    UNION ALL SELECT '9', '89'
    UNION ALL SELECT 'e', 'ef'
    UNION ALL SELECT 'f', 'ef'
)
,t_experiment_map AS
(
    SELECT "4" AS apptype, "ab" AS suffix_group, "实验组：变更str*ros建模目标实验" AS abcode, "20260416" AS start_dt, "29991231" AS end_dt
    UNION ALL SELECT "4", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "67", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "5d", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "34", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "4", "ef", "实验组：DNN模型",                 "20260407", "29991231"
    UNION ALL SELECT "4", "2c", "实验组：DNN模型-调参",            "20260413", "29991231"
    UNION ALL SELECT "4", "89", "对照组",                          "20260301", "29991231"
    UNION ALL SELECT "0", "ab", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "01", "实验组：变更str*ros建模目标实验", "20260416", "29991231"
    UNION ALL SELECT "0", "34", "实验组：变更str*ros建模目标实验", "20260426", "29991231"
    UNION ALL SELECT "0", "67", "实验组：变更str*ros建模目标实验", "20260417", "29991231"
    UNION ALL SELECT "0", "5d", "实验组：曝光建模v1+DNN模型",      "20260425", "29991231"
    UNION ALL SELECT "0", "ef", "实验组：DNN模型-调参",            "20260410", "29991231"
    UNION ALL SELECT "0", "2c", "实验组：DNN模型",                 "20260413", "29991231"
    UNION ALL SELECT "0", "89", "对照组",                          "20260301", "29991231"
)
,t_exp_code_map AS
(
    SELECT "0" AS apptype, "89" AS suffix_group, "839" AS expcode, "20260425" AS start_dt, "29991231" AS end_dt
    UNION ALL SELECT "4", "89", "839", "20260425", "29991231"
    UNION ALL SELECT "4", "ef", "562", "20260425", "29991231"
    UNION ALL SELECT "0", "2c", "562", "20260425", "29991231"
    UNION ALL SELECT "4", "2c", "563", "20260425", "29991231"
    UNION ALL SELECT "0", "ef", "563", "20260425", "29991231"
    UNION ALL SELECT "0", "5d", "565", "20260425", "29991231"
    UNION ALL SELECT "0", "34", "566", "20260425", "29991231"
)
-- 单条曝光 + 解 scoresmap，按业务量统一字段
,t_scored AS
(
    SELECT  a.dt
            ,a.apptype
            ,a.mid
            ,a.vid
            ,sg.suffix_group
            ,COALESCE(m.abcode, "对照组") AS abcode
            ,e.expcode
            -- ── 业务 label 透传 ──
            ,a.is_share
            ,a.share_cnt
            ,a.is_return_1
            ,a.is_return_n
            ,a.is_return_noself
            ,a.return_n_uv
            ,a.return_n_uv_noself
            ,a.new_exposure_cnt
            -- ── 模型预估 ──
            ,CAST(a.score AS DOUBLE)                                          AS final_score
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.fmRov')            AS DOUBLE) AS str_pred
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.fmRovOrigin')      AS DOUBLE) AS str_pred_origin
            ,COALESCE(
                CAST(GET_JSON_OBJECT(scoresmap, '$.NorXGBScore')   AS DOUBLE)
              , CAST(GET_JSON_OBJECT(scoresmap, '$.NorDNNScore')   AS DOUBLE)
             )                                                                AS ros_pred
            -- ── 历史统计 ──
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.vor')              AS DOUBLE) AS vor_stat
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.hasReturnRovScore') AS DOUBLE) AS ros_stat
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.c1RovnScore')      AS DOUBLE) AS c1_rovn_stat
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.b0StrScore')       AS DOUBLE) AS b0_str_stat
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.b0RorScore')       AS DOUBLE) AS b0_ror_stat
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.cnRovnScore')      AS DOUBLE) AS cn_rovn_stat
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.dnRovnScore')      AS DOUBLE) AS dn_rovn_stat
            -- ── 其他 ──
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.pLeave')           AS DOUBLE) AS p_leave
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.newPLeave')        AS DOUBLE) AS new_p_leave
            ,CAST(GET_JSON_OBJECT(scoresmap, '$.reduceCoefficient') AS DOUBLE) AS reduce_coef
    FROM    (
                SELECT  dt
                        ,apptype
                        ,mid
                        ,vid
                        ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                                LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
                        ,score
                        ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap
                        -- ── 业务 label（每条曝光的真实结果）──
                        ,is_share              -- 0/1 是否被分享
                        ,share_cnt             -- 分享次数
                        ,is_return_1           -- 0/1 是否首跳回流
                        ,is_return_n           -- 0/1 是否多跳回流
                        ,is_return_noself      -- 0/1 是否回流（不含自己）★ str_real_noself 对应
                        ,return_n_uv           -- 多跳回流 UV
                        ,return_n_uv_noself    -- 多跳回流 UV（不含自己）★ ros_real_noself 对应
                        ,new_exposure_cnt      -- 新增曝光（vovh24）
                FROM    loghubods.dwd_recsys_alg_sample_all_20250212
                WHERE   dt = '${dt}'
                AND     apptype IN ('0','4')
                AND     extend_alg IS NOT NULL
                AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
                AND     abcode NOT IN ("ab100")
                AND     (apptype = "4" OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab8","ab9"))
                AND     (apptype = "0" OR abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9"))
            ) a
    INNER JOIN t_suffix_group sg ON a.suffix = sg.suffix
    LEFT JOIN t_experiment_map m
        ON  a.apptype = m.apptype
        AND sg.suffix_group = m.suffix_group
        AND '${dt}' BETWEEN m.start_dt AND m.end_dt
    LEFT JOIN t_exp_code_map e
        ON  a.apptype = e.apptype
        AND sg.suffix_group = e.suffix_group
        AND '${dt}' BETWEEN e.start_dt AND e.end_dt
)
SELECT  dt
        ,apptype
        ,COALESCE(suffix_group, '总体') AS suffix_group
        ,MAX(abcode) AS abcode
        ,MAX(expcode) AS expcode
        -- ────── 样本量 ──────
        ,COUNT(1) AS exp
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS distinct_vid
        -- ────── 业务真实值（label 聚合）──────
        ,COALESCE(SUM(is_share),0) AS share_cnt_uv      -- 分享 UV 数（is_share=1 的曝光）
        ,COALESCE(SUM(share_cnt),0) AS share_cnt        -- 分享次数总和
        ,COALESCE(SUM(is_return_1),0) AS return_1_cnt   -- 首跳回流曝光数
        ,COALESCE(SUM(is_return_n),0) AS return_n_cnt   -- 多跳回流曝光数
        ,COALESCE(SUM(is_return_noself),0) AS return_noself_cnt  -- 多跳回流曝光数（不含自己）
        ,COALESCE(SUM(return_n_uv),0) AS return_n_uv_sum         -- 多跳回流 UV 总和
        ,COALESCE(SUM(return_n_uv_noself),0) AS return_n_uv_noself_sum -- 多跳回流 UV 不含自己
        ,COALESCE(SUM(new_exposure_cnt),0) AS viewh24            -- 24 小时新增曝光
        -- ────── 业务真实率（_real）──────
        ,ROUND(COALESCE(SUM(is_share) / COUNT(1), 0), 6) AS str_one_real        -- 一跳分享率
        ,ROUND(COALESCE(SUM(share_cnt) / COUNT(1), 0), 6) AS str_real           -- 分享率（含多次）
        ,ROUND(COALESCE(SUM(is_return_noself) / COUNT(1), 0), 6) AS str_real_noself  -- ★ str_pred 的真实对照
        ,ROUND(COALESCE(SUM(return_n_uv) / NULLIF(SUM(share_cnt), 0), 0), 6) AS ros_real
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0), 0), 6) AS ros_real_noself  -- ★ ros_pred 的真实对照
        ,ROUND(COALESCE(SUM(return_n_uv) / COUNT(1), 0), 6) AS rov_real
        ,ROUND(COALESCE(SUM(return_n_uv_noself) / COUNT(1), 0), 6) AS rov_real_noself
        ,ROUND(COALESCE(SUM(new_exposure_cnt) / COUNT(1), 0), 6) AS vovh24_real
        -- ────── final_score（线上最终分）──────
        ,ROUND(AVG(final_score), 6)                          AS finalscore_avg
        ,ROUND(STDDEV_POP(final_score), 6)                   AS finalscore_std
        ,ROUND(MIN(final_score), 6)                          AS finalscore_min
        ,ROUND(MAX(final_score), 6)                          AS finalscore_max
        ,ROUND(PERCENTILE_APPROX(final_score, 0.50), 6)      AS finalscore_p50
        ,ROUND(PERCENTILE_APPROX(final_score, 0.95), 6)      AS finalscore_p95
        ,ROUND(SUM(CASE WHEN final_score IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS finalscore_cov
        -- ────── str_pred（STR 分享率预估）──────
        ,ROUND(AVG(str_pred), 6)                             AS strpred_avg
        ,ROUND(STDDEV_POP(str_pred), 6)                      AS strpred_std
        ,ROUND(MIN(str_pred), 6)                             AS strpred_min
        ,ROUND(MAX(str_pred), 6)                             AS strpred_max
        ,ROUND(PERCENTILE_APPROX(str_pred, 0.50), 6)         AS strpred_p50
        ,ROUND(PERCENTILE_APPROX(str_pred, 0.95), 6)         AS strpred_p95
        ,ROUND(SUM(CASE WHEN str_pred IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS strpred_cov
        -- ★ str_copc = str_real_noself / AVG(str_pred)，> 1 表示模型低估
        ,ROUND(SUM(is_return_noself) / NULLIF(SUM(str_pred), 0), 4) AS str_copc
        -- ────── str_pred_origin（STR 反归一化前）──────
        ,ROUND(AVG(str_pred_origin), 6)                      AS strpredorig_avg
        ,ROUND(STDDEV_POP(str_pred_origin), 6)               AS strpredorig_std
        ,ROUND(MIN(str_pred_origin), 6)                      AS strpredorig_min
        ,ROUND(MAX(str_pred_origin), 6)                      AS strpredorig_max
        ,ROUND(PERCENTILE_APPROX(str_pred_origin, 0.50), 6)  AS strpredorig_p50
        ,ROUND(PERCENTILE_APPROX(str_pred_origin, 0.95), 6)  AS strpredorig_p95
        ,ROUND(SUM(CASE WHEN str_pred_origin IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS strpredorig_cov
        -- ────── ros_pred（ROS 分享后回流率预估，统一 XGB/DNN）──────
        ,ROUND(AVG(ros_pred), 6)                             AS rospred_avg
        ,ROUND(STDDEV_POP(ros_pred), 6)                      AS rospred_std
        ,ROUND(MIN(ros_pred), 6)                             AS rospred_min
        ,ROUND(MAX(ros_pred), 6)                             AS rospred_max
        ,ROUND(PERCENTILE_APPROX(ros_pred, 0.50), 6)         AS rospred_p50
        ,ROUND(PERCENTILE_APPROX(ros_pred, 0.95), 6)         AS rospred_p95
        ,ROUND(SUM(CASE WHEN ros_pred IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rospred_cov
        -- ★ ros_copc = ros_real_noself / AVG(ros_pred when is_return_noself=1)，仅在已分享回流条件下对比
        ,ROUND(
            (SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0))
            / NULLIF(AVG(CASE WHEN is_return_noself = 1 THEN ros_pred END), 0)
        , 4) AS ros_copc
        -- ★ rov_copc = rov_real_noself / AVG(str_pred × ros_pred)
        ,ROUND(SUM(return_n_uv_noself) / NULLIF(SUM(str_pred * ros_pred), 0), 4) AS rov_copc
        -- ────── vor_stat（视频历史曝光裂变率）──────
        ,ROUND(AVG(vor_stat), 6)                             AS vorstat_avg
        ,ROUND(STDDEV_POP(vor_stat), 6)                      AS vorstat_std
        ,ROUND(MIN(vor_stat), 6)                             AS vorstat_min
        ,ROUND(MAX(vor_stat), 6)                             AS vorstat_max
        ,ROUND(PERCENTILE_APPROX(vor_stat, 0.50), 6)         AS vorstat_p50
        ,ROUND(PERCENTILE_APPROX(vor_stat, 0.95), 6)         AS vorstat_p95
        ,ROUND(SUM(CASE WHEN vor_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS vorstat_cov
        -- ────── ros_stat（视频历史回流率）──────
        ,ROUND(AVG(ros_stat), 6)                             AS rosstat_avg
        ,ROUND(STDDEV_POP(ros_stat), 6)                      AS rosstat_std
        ,ROUND(MIN(ros_stat), 6)                             AS rosstat_min
        ,ROUND(MAX(ros_stat), 6)                             AS rosstat_max
        ,ROUND(PERCENTILE_APPROX(ros_stat, 0.50), 6)         AS rosstat_p50
        ,ROUND(PERCENTILE_APPROX(ros_stat, 0.95), 6)         AS rosstat_p95
        ,ROUND(SUM(CASE WHEN ros_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS rosstat_cov
        -- ────── c1_rovn_stat（一级品类 ROVN 统计加权）──────
        ,ROUND(AVG(c1_rovn_stat), 6)                         AS c1rovn_avg
        ,ROUND(STDDEV_POP(c1_rovn_stat), 6)                  AS c1rovn_std
        ,ROUND(MIN(c1_rovn_stat), 6)                         AS c1rovn_min
        ,ROUND(MAX(c1_rovn_stat), 6)                         AS c1rovn_max
        ,ROUND(PERCENTILE_APPROX(c1_rovn_stat, 0.50), 6)     AS c1rovn_p50
        ,ROUND(PERCENTILE_APPROX(c1_rovn_stat, 0.95), 6)     AS c1rovn_p95
        ,ROUND(SUM(CASE WHEN c1_rovn_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS c1rovn_cov
        -- ────── b0_str_stat（B0 流 STR 统计加权；当前权重=0）──────
        ,ROUND(AVG(b0_str_stat), 6)                          AS b0str_avg
        ,ROUND(STDDEV_POP(b0_str_stat), 6)                   AS b0str_std
        ,ROUND(MIN(b0_str_stat), 6)                          AS b0str_min
        ,ROUND(MAX(b0_str_stat), 6)                          AS b0str_max
        ,ROUND(PERCENTILE_APPROX(b0_str_stat, 0.50), 6)      AS b0str_p50
        ,ROUND(PERCENTILE_APPROX(b0_str_stat, 0.95), 6)      AS b0str_p95
        ,ROUND(SUM(CASE WHEN b0_str_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS b0str_cov
        -- ────── b0_ror_stat（B0 流 ROR 统计加权；当前权重=0）──────
        ,ROUND(AVG(b0_ror_stat), 6)                          AS b0ror_avg
        ,ROUND(STDDEV_POP(b0_ror_stat), 6)                   AS b0ror_std
        ,ROUND(MIN(b0_ror_stat), 6)                          AS b0ror_min
        ,ROUND(MAX(b0_ror_stat), 6)                          AS b0ror_max
        ,ROUND(PERCENTILE_APPROX(b0_ror_stat, 0.50), 6)      AS b0ror_p50
        ,ROUND(PERCENTILE_APPROX(b0_ror_stat, 0.95), 6)      AS b0ror_p95
        ,ROUND(SUM(CASE WHEN b0_ror_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS b0ror_cov
        -- ────── cn_rovn_stat（CN 流 ROVN；当前权重=0）──────
        ,ROUND(AVG(cn_rovn_stat), 6)                         AS cnrovn_avg
        ,ROUND(STDDEV_POP(cn_rovn_stat), 6)                  AS cnrovn_std
        ,ROUND(MIN(cn_rovn_stat), 6)                         AS cnrovn_min
        ,ROUND(MAX(cn_rovn_stat), 6)                         AS cnrovn_max
        ,ROUND(PERCENTILE_APPROX(cn_rovn_stat, 0.50), 6)     AS cnrovn_p50
        ,ROUND(PERCENTILE_APPROX(cn_rovn_stat, 0.95), 6)     AS cnrovn_p95
        ,ROUND(SUM(CASE WHEN cn_rovn_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS cnrovn_cov
        -- ────── dn_rovn_stat（DN 流 ROVN；当前权重=0）──────
        ,ROUND(AVG(dn_rovn_stat), 6)                         AS dnrovn_avg
        ,ROUND(STDDEV_POP(dn_rovn_stat), 6)                  AS dnrovn_std
        ,ROUND(MIN(dn_rovn_stat), 6)                         AS dnrovn_min
        ,ROUND(MAX(dn_rovn_stat), 6)                         AS dnrovn_max
        ,ROUND(PERCENTILE_APPROX(dn_rovn_stat, 0.50), 6)     AS dnrovn_p50
        ,ROUND(PERCENTILE_APPROX(dn_rovn_stat, 0.95), 6)     AS dnrovn_p95
        ,ROUND(SUM(CASE WHEN dn_rovn_stat IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS dnrovn_cov
        -- ────── p_leave（PAI 模型预测离开概率原值）──────
        ,ROUND(AVG(p_leave), 6)                              AS pleave_avg
        ,ROUND(STDDEV_POP(p_leave), 6)                       AS pleave_std
        ,ROUND(MIN(p_leave), 6)                              AS pleave_min
        ,ROUND(MAX(p_leave), 6)                              AS pleave_max
        ,ROUND(PERCENTILE_APPROX(p_leave, 0.50), 6)          AS pleave_p50
        ,ROUND(PERCENTILE_APPROX(p_leave, 0.95), 6)          AS pleave_p95
        ,ROUND(SUM(CASE WHEN p_leave IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS pleave_cov
        -- ────── new_p_leave = (1-leaveW·pLeave)^leaveExp ──────
        ,ROUND(AVG(new_p_leave), 6)                          AS newpleave_avg
        ,ROUND(STDDEV_POP(new_p_leave), 6)                   AS newpleave_std
        ,ROUND(MIN(new_p_leave), 6)                          AS newpleave_min
        ,ROUND(MAX(new_p_leave), 6)                          AS newpleave_max
        ,ROUND(PERCENTILE_APPROX(new_p_leave, 0.50), 6)      AS newpleave_p50
        ,ROUND(PERCENTILE_APPROX(new_p_leave, 0.95), 6)      AS newpleave_p95
        ,ROUND(SUM(CASE WHEN new_p_leave IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS newpleave_cov
        -- ────── reduce_coef（重排品类/节日降权）──────
        ,ROUND(AVG(reduce_coef), 6)                          AS reduce_avg
        ,ROUND(STDDEV_POP(reduce_coef), 6)                   AS reduce_std
        ,ROUND(MIN(reduce_coef), 6)                          AS reduce_min
        ,ROUND(MAX(reduce_coef), 6)                          AS reduce_max
        ,ROUND(PERCENTILE_APPROX(reduce_coef, 0.50), 6)      AS reduce_p50
        ,ROUND(PERCENTILE_APPROX(reduce_coef, 0.95), 6)      AS reduce_p95
        ,ROUND(SUM(CASE WHEN reduce_coef IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS reduce_cov
FROM    t_scored
GROUP BY dt, apptype, suffix_group
GROUPING SETS (
    (dt, apptype),
    (dt, apptype, suffix_group)
)
ORDER BY dt DESC, apptype, suffix_group
;
