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
