WITH
reqipbin AS
(
     SELECT
           r.ID
         , r.FORWARDEDIP
         , lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 1))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 2))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 3))), 8, '0')
             || lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 4))), 8, '0') AS IP_BIN
     FROM request r
),
ipcidr AS
(
     SELECT
           ipb.id
         , ipb.forwardedip
         , rpad(substr(ipb.IP_BIN, 0, 16), 32, '0') ip_16
         , rpad(substr(ipb.IP_BIN, 0, 17), 32, '0') ip_17
         , rpad(substr(ipb.IP_BIN, 0, 18), 32, '0') ip_18
         , rpad(substr(ipb.IP_BIN, 0, 19), 32, '0') ip_19
         , rpad(substr(ipb.IP_BIN, 0, 20), 32, '0') ip_20
         , rpad(substr(ipb.IP_BIN, 0, 21), 32, '0') ip_21
         , rpad(substr(ipb.IP_BIN, 0, 22), 32, '0') ip_22
         , rpad(substr(ipb.IP_BIN, 0, 23), 32, '0') ip_23
         , rpad(substr(ipb.IP_BIN, 0, 24), 32, '0') ip_24
         , rpad(substr(ipb.IP_BIN, 0, 25), 32, '0') ip_25
         , rpad(substr(ipb.IP_BIN, 0, 26), 32, '0') ip_26
         , rpad(substr(ipb.IP_BIN, 0, 27), 32, '0') ip_27
         , rpad(substr(ipb.IP_BIN, 0, 28), 32, '0') ip_28
         , rpad(substr(ipb.IP_BIN, 0, 29), 32, '0') ip_29
         , rpad(substr(ipb.IP_BIN, 0, 30), 32, '0') ip_30
         , rpad(substr(ipb.IP_BIN, 0, 31), 32, '0') ip_31
     FROM reqipbin ipb
),
iprangecounts AS (
  SELECT
        c.id
      , c.forwardedip
      , COUNT(c.ip_16) OVER (PARTITION BY c.ip_16) AS count16
      , COUNT(c.ip_17) OVER (PARTITION BY c.ip_17) AS count17
      , COUNT(c.ip_18) OVER (PARTITION BY c.ip_18) AS count18
      , COUNT(c.ip_19) OVER (PARTITION BY c.ip_19) AS count19
      , COUNT(c.ip_20) OVER (PARTITION BY c.ip_20) AS count20
      , COUNT(c.ip_21) OVER (PARTITION BY c.ip_21) AS count21
      , COUNT(c.ip_22) OVER (PARTITION BY c.ip_22) AS count22
      , COUNT(c.ip_23) OVER (PARTITION BY c.ip_23) AS count23
      , COUNT(c.ip_24) OVER (PARTITION BY c.ip_24) AS count24
      , COUNT(c.ip_25) OVER (PARTITION BY c.ip_25) AS count25
      , COUNT(c.ip_26) OVER (PARTITION BY c.ip_26) AS count26
      , COUNT(c.ip_27) OVER (PARTITION BY c.ip_27) AS count27
      , COUNT(c.ip_28) OVER (PARTITION BY c.ip_28) AS count28
      , COUNT(c.ip_29) OVER (PARTITION BY c.ip_29) AS count29
      , COUNT(c.ip_30) OVER (PARTITION BY c.ip_30) AS count30
      , COUNT(c.ip_31) OVER (PARTITION BY c.ip_31) AS count31
  FROM ipcidr c
)
SELECT * FROM iprangecounts rc
WHERE count16 > 1
ORDER BY count16 desc, count17 desc, count18 desc, count19 desc, count20 desc;