Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmWkI-007nQz-HA for pgsql-general@arkaria.postgresql.org; Sat, 30 Sep 2023 09:58:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qmWkH-000OkW-60 for pgsql-general@arkaria.postgresql.org; Sat, 30 Sep 2023 09:58:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmWkG-000Ohn-EO for pgsql-general@lists.postgresql.org; Sat, 30 Sep 2023 09:58:44 +0000 Received: from mail-qk1-x731.google.com ([2607:f8b0:4864:20::731]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qmWkD-007QQ9-2x for pgsql-general@lists.postgresql.org; Sat, 30 Sep 2023 09:58:43 +0000 Received: by mail-qk1-x731.google.com with SMTP id af79cd13be357-775810b032aso209886385a.1 for ; Sat, 30 Sep 2023 02:58:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696067920; x=1696672720; darn=lists.postgresql.org; h=in-reply-to:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:from:to:cc:subject:date :message-id:reply-to; bh=VgzQ+OfL9FuR2ZngU8bWj5YDOZYpUZ3jHho6MTQ0wys=; b=ifFYrdVpUhgcszF1mPfesJMpV3p32ccV38tmm0a2KThGDI/J/z5mEB3MpOW09Ao6hA Q9Z3xvXxt0Ucnfbn7zE08CSXl8B+Kvz0r2a+Ym2ja5702W05Se9DgdwWDEDhvysA/tve RQvHD4SZ4pPveFuc9KzdhvaZpj7w8jlKnvuRvEv+Rm/Ms/7HV7sZ3kbjtcoB/ILq/s/Z OvWJWMEz8X9Ea8vgC0i7dKtTCrsQfbNLBZe2Gzo2nAr/8N3SYM/EqZtJElW0mFGvO3rn v63VQ52eCEAltrEV56PJl1Mhsnhg9n4zx70grGqZMp6NfpE3lJiBBebKWND/TbpNH0bq pYuA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696067920; x=1696672720; h=in-reply-to:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=VgzQ+OfL9FuR2ZngU8bWj5YDOZYpUZ3jHho6MTQ0wys=; b=UQscoj1/y+H57UO8vdLuBCjmlQDpJM3YrJNEHMmmqtggE+y1mHrQNFcaaal+PYxZHQ nLrZdmV9TmyELijXnTGotSOnZWtavXd2udRMEIh5mjCdVlC7DJxvA7i48gNetCaA0i34 4PF9vxEIuLhi+R9tV9qmZnuxJfHiFAIqAg+mqVfv9yVI94XCSgdbnfvkuSXFEX0iRxL4 M5RH69Opk5yVqgYcqqRK7cv09Pma6F2k6ytd6Vub3Mkx6aP6lXs7svoXwCPytzwhcdvW GaziU+PISvh210UnMWoqSNX/U8p2D79q/MI1uQFUaUU+/TqoUI4D3EuR7fkAArA63k22 6NLA== X-Gm-Message-State: AOJu0YyYvXLgw6aVw+gIdLyjqacEpb6B9TfW1NwMFXoDpDmAgxfcXJLI dfWJTAn6KOtRJwnQA1xARvjVtqlEMnk= X-Google-Smtp-Source: AGHT+IGyUKY+We9VSlLwDqoKyz6KRGGk4vmT9StBc2aUvh6MM5PBvaLsqQ/nvYhtFyREWujVEiFJmw== X-Received: by 2002:a05:620a:29c7:b0:76c:96e9:7d5 with SMTP id s7-20020a05620a29c700b0076c96e907d5mr7861264qkp.26.1696067919923; Sat, 30 Sep 2023 02:58:39 -0700 (PDT) Received: from [192.168.2.170] ([64.229.206.43]) by smtp.gmail.com with ESMTPSA id d27-20020a05620a159b00b007759a81d88esm435405qkk.50.2023.09.30.02.58.39 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 30 Sep 2023 02:58:39 -0700 (PDT) From: Amn Ojee Uw X-Google-Original-From: Amn Ojee Uw Content-Type: multipart/alternative; boundary="------------pTuYX7s8Got5j10NJ6kEr1ng" Message-ID: Date: Sat, 30 Sep 2023 05:58:27 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Subject: Re: [EXT] YNT: Need help tuning a query To: pgsql-general@lists.postgresql.org References: <07fc01d9f21f$30e48190$92ad84b0$@gmail.com> Content-Language: en-CA, es-ES In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------pTuYX7s8Got5j10NJ6kEr1ng Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > --------------pTuYX7s8Got5j10NJ6kEr1ng Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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

--------------pTuYX7s8Got5j10NJ6kEr1ng--