How to use conditional group by aggregations correctly, Oracle

Language: Layout:

--Oracle 11g Express Edition
--please drop objects you've created at the end of the script 
--or check for their existance before creating
--'\\' is a delimiter

WITH
  X AS (
    SELECT
      'A1' AS food_item,
      'lxgs' AS food_area,
      'argentina' AS food_loc,
      date'2020-01-01' AS food_exp
    FROM dual UNION ALL
    -- SELECT 'F1', 'lxgs', 'argentina', date'2019-05-01' FROM dual UNION ALL
    SELECT 'B1', 'iyan', 'usa', date'2021-05-31' FROM dual UNION ALL
    SELECT 'C1', 'lxgs', 'peru', date'2021-05-31' FROM dual UNION ALL
    SELECT 'D1', 'wa8e', 'norway', date'2019-10-01' FROM dual UNION ALL
    SELECT 'E1', '894a', 'laos', date'2019-05-01' FROM dual UNION ALL
    SELECT 'F1', '894a', 'laos', date'2019-09-17' FROM dual
  ),
  Y AS (
    SELECT 'A1' AS food_item, 'Y' AS organic FROM dual UNION ALL
    SELECT 'B1', 'Y' FROM dual UNION ALL
    SELECT 'C1', 'N' FROM dual UNION ALL
    SELECT 'D1', 'N' FROM dual UNION ALL
    SELECT 'E1', 'Y' FROM dual UNION ALL
    SELECT 'F1', 'N' FROM dual
  ),
  Z AS (
    SELECT 'A1' AS food_item, '189' AS food_type FROM dual UNION ALL
    SELECT 'A1', '188' FROM dual UNION ALL
    SELECT 'A1', '190' FROM dual UNION ALL
    SELECT 'B1', '191' FROM dual UNION ALL
    SELECT 'C1', '189' FROM dual UNION ALL
    SELECT 'D1', '192' FROM dual UNION ALL
    SELECT 'E1', '193' FROM dual UNION ALL
    SELECT 'F1', '193' FROM dual
  ),
  t AS (
    SELECT
      CASE SUBSTR(X.food_area, LENGTH(X.food_area), 1)
        WHEN 's' THEN 'SA'
        WHEN 'n' THEN 'NA'
        WHEN 'e' THEN 'EU'
        WHEN 'a' THEN 'AS'
        ELSE NULL
      END AS continent,
      x.food_loc AS country,
      COUNT(DISTINCT CASE Y.organic WHEN 'Y' THEN X.food_item END) OVER (
        PARTITION BY x.food_loc
      ) AS organic_fd,
      CASE
        WHEN MIN(Z.food_type) OVER (
               PARTITION BY x.food_loc, X.food_item
             ) = Z.food_type AND
             MAX(Z.food_type) OVER (
               PARTITION BY x.food_loc, X.food_item
             ) > Z.food_type THEN 1 END AS mixed,
      Z.food_type
    FROM X
    JOIN Y ON X.food_item = Y.food_item
    JOIN Z ON Y.food_item = Z.food_item
  )
SELECT
  continent, country, organic_fd,
  COUNT(CASE WHEN food_type = '189' THEN 1 END) AS Granny,
  COUNT(CASE WHEN food_type = '190' THEN 1 END) AS Red_delc,
  COUNT(CASE WHEN food_type = '191' THEN 1 END) AS Golden,
  COUNT(CASE WHEN food_type = '192' THEN 1 END) AS Gala,
  COUNT(CASE WHEN food_type = '193' THEN 1 END) AS Fuji,
  COUNT(mixed) AS mixed_bag
FROM t
GROUP BY continent, country, organic_fd


absolute service time: 0,85 sec