Re: [EXT] YNT: Need help tuning a query

Wow!! This is what I call cryptic!!

On 9/29/23 2:46 a.m., Vladimir Sitnikov wrote:
> Oh, I misplaced the added where conditions.
> It should have been as follows, however, the overall idea is the same
>
> --- orignial.sql
> +++ tuned_v2.sql
> @@ -83,6 +83,7 @@
>                                 AND (judg1.jrt_opt_out_flag <> 'Y' OR
> judg1.jrt_opt_out_flag IS NULL)
>                      ) sub0
>                 LEFT OUTER JOIN
> +                    LATERAL
>                     (  SELECT sub4.case_year_number,
>                                   sub4.judge_wld_id,
>                                   sub4.judge_id,
> @@ -99,6 +100,7 @@
>                                   jrtf1.higher_judge_id,
>                                   jrtf1.case_document_id
>                              ) sub4
> +                     WHERE sub4.judge_id = sub0.judge_id
>                  GROUP BY sub4.case_year_number,
>                                  sub4.judge_wld_id,
>                                  sub4.judge_id,
> @@ -106,6 +108,7 @@
>                    ) sub1
>                 ON sub1.judge_id = sub0.judge_id
>               LEFT OUTER JOIN
> +                  LATERAL
>                   (SELECT sub5.case_year_number,
>                                 sub5.judge_wld_id,
>                                 sub5.judge_id,
> @@ -129,6 +132,7 @@
>                                 ),
>                                 jrtf2.case_document_id
>                                ) sub5
> +                  WHERE sub5.judge_id = sub0.judge_id
>                GROUP BY sub5.case_year_number,
>                                sub5.judge_wld_id,
>                                sub5.judge_id,
>
> SELECT agg_sub.judge_id,
>        agg_sub.display_name,
>        agg_sub.active_flag,
>        agg_sub.judge_court_level,
>        agg_sub.jrt_fact_first_year_trial,
>        agg_sub.jrt_fact_last_year_trial,
>        agg_sub.jrt_fact_totalcount_trial,
>        agg_sub.filtered_first_year_trial,
>        agg_sub.filtered_last_year_trial,
>        agg_sub.jrt_fact_count_trial,
>        agg_sub.jrt_fact_first_year_appeal,
>        agg_sub.jrt_fact_last_year_appeal,
>        agg_sub.jrt_fact_totalcount_appeal,
>        agg_sub.filtered_first_year_appeal,
>        agg_sub.filtered_last_year_appeal,
>        agg_sub.jrt_fact_count_appeal,
>        appellate_flag_sub.appellate_flag
>    FROM (SELECT sub3.judge_id,
>              sub3.display_name,
>              sub3.active_flag,
>              sub3.judge_court_level,
>              (MIN(sub3.trial_unfilt_case_year_number)) AS
> jrt_fact_first_year_trial,
>              (MAX(sub3.trial_unfilt_case_year_number)) AS
> jrt_fact_last_year_trial,
>              (SUM(sub3.trial_unfilt_subcount)) AS
> jrt_fact_totalcount_trial,
>              (MIN(sub3.trial_filt_case_year_number)) AS
> filtered_first_year_trial,
>              (MAX(sub3.trial_filt_case_year_number)) AS
> filtered_last_year_trial,
>              (SUM(sub3.trial_filt_subcount)) AS jrt_fact_count_trial,
>              (MIN(sub3.appeal_unfilt_case_year_number)) AS
> jrt_fact_first_year_appeal,
>              (MAX(sub3.appeal_unfilt_case_year_number)) AS
> jrt_fact_last_year_appeal,
>              (SUM(sub3.appeal_unfilt_subcount)) AS
> jrt_fact_totalcount_appeal,
>              (MIN(sub3.appeal_filt_case_year_number)) AS
> filtered_first_year_appeal,
>              (MAX(sub3.appeal_filt_case_year_number)) AS
> filtered_last_year_appeal,
>              (SUM(sub3.appeal_filt_subcount)) AS jrt_fact_count_appeal
>            FROM (SELECT sub0.judge_id,
>                     sub0.display_name,
>                     sub0.active_flag,
>                     sub0.judge_court_level,
>                     (CASE WHEN sub2.grouping_flg = 'T' AND
> sub2.judge_wld_id = sub0.judge_wld_id THEN sub2.case_year_number ELSE
> NULL END) AS trial_unfilt_case_year_number,
>                     (CASE WHEN sub2.grouping_flg = 'T'AND
> sub2.judge_wld_id = sub0.judge_wld_id THEN sub2.subcount ELSE NULL END
>                       ) AS trial_unfilt_subcount,
>                     (CASE WHEN sub2.grouping_flg = 'T' AND
> sub2.judge_wld_id = sub0.judge_wld_id THEN (CASE WHEN
> sub2.case_year_number BETWEEN sub0.low_case_year_number AND
> sub0.high_case_year_number
>                            THEN sub2.case_year_number ELSE NULL END)
> ELSE NULL END) AS trial_filt_case_year_number,
>                     (CASE WHEN sub2.grouping_flg = 'T' AND
> sub2.judge_wld_id = sub0.judge_wld_id THEN (CASE WHEN
> sub2.case_year_number BETWEEN sub0.low_case_year_number AND
> sub0.high_case_year_number
>                            THEN sub2.subcount ELSE NULL END       )
> ELSE NULL END        ) AS trial_filt_subcount,
>                     (CASE WHEN sub1.grouping_flg = 'A'AND
> sub1.judge_wld_id = sub0.judge_wld_id THEN sub1.case_year_number WHEN
> sub2.grouping_flg = 'A'   AND sub2.judge_wld_id = sub0.judge_wld_id
>                            THEN sub2.case_year_number ELSE NULL END  
>       ) AS appeal_unfilt_case_year_number,
>                     (
>                     CASE WHEN sub1.grouping_flg = 'A'
>                     AND sub1.judge_wld_id = sub0.judge_wld_id THEN
> sub1.subcount WHEN sub2.grouping_flg = 'A'
>                     AND sub2.judge_wld_id = sub0.judge_wld_id THEN
> sub2.subcount ELSE NULL END
>                     ) AS appeal_unfilt_subcount,
>                     (
>                     CASE WHEN sub1.grouping_flg = 'A'
>                     AND sub1.judge_wld_id = sub0.judge_wld_id THEN (
>                           CASE WHEN sub1.case_year_number BETWEEN
> sub0.low_case_year_number
>                            AND sub0.high_case_year_number THEN
> sub1.case_year_number ELSE NULL END
>                     ) WHEN sub2.grouping_flg = 'A'
>                     AND sub2.judge_wld_id = sub0.judge_wld_id THEN (
>                            CASE WHEN sub2.case_year_number BETWEEN
> sub0.low_case_year_number
>                            AND sub0.high_case_year_number THEN
> sub2.case_year_number ELSE NULL END
>                     ) ELSE NULL END
>                     ) AS appeal_filt_case_year_number,
>                     (
>                     CASE WHEN sub1.grouping_flg = 'A'
>                     AND sub1.judge_wld_id = sub0.judge_wld_id THEN (
>                            CASE WHEN sub1.case_year_number BETWEEN
> sub0.low_case_year_number
>                            AND sub0.high_case_year_number THEN
> sub1.subcount ELSE NULL END
>                     ) WHEN sub2.grouping_flg = 'A'
>                     AND sub2.judge_wld_id = sub0.judge_wld_id THEN (
>                            CASE WHEN sub2.case_year_number BETWEEN
> sub0.low_case_year_number
>                            AND sub0.high_case_year_number THEN
> sub2.subcount ELSE NULL END
>                     ) ELSE NULL END
>                     ) AS appeal_filt_subcount
>                FROM ( SELECT 0104119201 AS judge_wld_id,
>                                  2013 AS low_case_year_number,
>                                  2023 AS high_case_year_number,
>                                  judg1.judge_id,
>                                  judg1.display_name,
>                                  judg1.active_flag,
>                                  judg1.judge_court_level
>                        FROM wln_mart.judge judg1
>                       WHERE judg1.wld_id = 01041192
>                                AND judg1.profile_id = 01
>                                AND (judg1.jrt_opt_out_flag <> 'Y' OR
> judg1.jrt_opt_out_flag IS NULL)
>                     ) sub0
>                LEFT OUTER JOIN
>                     LATERAL
>                    (  SELECT sub4.case_year_number,
>                                  sub4.judge_wld_id,
>                                  sub4.judge_id,
>                                  sub4.grouping_flg,
>                                  COUNT(*) AS subcount
>                       FROM  (SELECT jrtf1.case_year_number,
>                                  jrtf1.higher_judge_wld_id AS
> judge_wld_id,
>                                  jrtf1.higher_judge_id AS judge_id,
>                                  'A' AS grouping_flg,
>                                  jrtf1.case_document_id AS subcount
>                               FROM wln_mart.jrt_fact jrtf1
>                            GROUP BY jrtf1.case_year_number,
>  jrtf1.higher_judge_wld_id,
>                                  jrtf1.higher_judge_id,
>                                  jrtf1.case_document_id
>                             ) sub4
>                      WHERE sub4.judge_id = sub0.judge_id
>                 GROUP BY sub4.case_year_number,
>                                 sub4.judge_wld_id,
>                                 sub4.judge_id,
>                                 sub4.grouping_flg
>                   ) sub1
>                ON sub1.judge_id = sub0.judge_id
>              LEFT OUTER JOIN
>                   LATERAL
>                  (SELECT sub5.case_year_number,
>                                sub5.judge_wld_id,
>                                sub5.judge_id,
>                                sub5.grouping_flg,
>                                COUNT(*) AS subcount
>                    FROM (SELECT jrtf2.case_year_number,
>                                jrtf2.lower_judge_wld_id AS judge_wld_id,
>                                jrtf2.lower_judge_id AS judge_id,
>                                (
>                                         CASE WHEN
> jrtf2.lower_judge_court_level_id = 1004 THEN 'T' ELSE 'A' END
>                                ) AS grouping_flg,
>                                jrtf2.case_document_id AS subcount
>                            FROM wln_mart.jrt_fact jrtf2
>                           WHERE jrtf2.lower_judge_court_level_id > 1000
>                            AND jrtf2.lower_judge_court_level_id <= 1004
>                        GROUP BY jrtf2.case_year_number,
>                                jrtf2.lower_judge_wld_id,
>                                jrtf2.lower_judge_id,
>                                (
>                                         CASE WHEN
> jrtf2.lower_judge_court_level_id = 1004 THEN 'T' ELSE 'A' END
>                                ),
>                                jrtf2.case_document_id
>                               ) sub5
>                   WHERE sub5.judge_id = sub0.judge_id
>               GROUP BY sub5.case_year_number,
>                               sub5.judge_wld_id,
>                               sub5.judge_id,
>                               sub5.grouping_flg
>                  ) sub2
>              ON sub2.judge_id = sub0.judge_id
>       ) sub3
>     GROUP BY sub3.judge_id,
>            sub3.display_name,
>                   sub3.active_flag,
>            sub3.judge_court_level
>   ) agg_sub,
>   (SELECT judge_id,
>         (CASE WHEN (SUM (appellate_flag) < (COUNT(*) / 2)) THEN 0 ELSE
> 1 END    ) AS appellate_flag
>      FROM (SELECT DISTINCT jrtf.case_year_number,
>                jrtf.case_document_id,
>               jrtf.lower_judge_id,
>                jrtf.higher_judge_id,
>                (
>                     CASE WHEN (
>                            lower_judge_wld_id = 0104119201
>                            AND lower_judge_court_level_id = 1004
>               ) THEN 0 ELSE 1 END      ) AS appellate_flag,
>                (
>                     CASE WHEN lower_judge_wld_id = 0104119201 THEN
> lower_judge_id ELSE higher_judge_id END
>                ) AS judge_id
>             FROM wln_mart.JRT_FACT jrtf
>            WHERE LOWER_JUDGE_WLD_ID = 0104119201
>               OR HIGHER_JUDGE_WLD_ID = 0104119201
>         ORDER BY case_year_number DESC
>       ) sub0
>   GROUP BY judge_id
>   LIMIT 11
>   ) appellate_flag_sub
> WHERE
>   Agg_sub.judge_id = appellate_flag_sub.judge_id
>
>
> Vladimir
>