Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products 
(
    product_id NUMBER PRIMARY KEY,
    available_from DATE NOT NULL
);
CREATE TABLE orders 
(
    order_id NUMBER PRIMARY KEY,
    product_id NUMBER NOT NULL,
    quantity NUMBER NOT NULL,
    dispatch_date DATE NOT NULL,

    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
EXPLAIN PLAN FOR
SELECT products_old.product_id,
       COALESCE(SUM(orders_past_year.quantity), 0) AS sold_past_year
FROM   (SELECT order_id,
               product_id,
               quantity
        FROM   orders
        WHERE  orders.dispatch_date >= ADD_MONTHS(SYSDATE, -12)) orders_past_year
       RIGHT JOIN (SELECT product_id
                   FROM   products
                   WHERE  products.available_from <= ADD_MONTHS(SYSDATE, -1))
              products_old
               ON orders_past_year.product_id = products_old.product_id
GROUP  BY products_old.product_id
HAVING COALESCE(SUM(orders_past_year.quantity), 0) < 20; 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

Plan hash value: 123453394

---------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |     1 |    57 |     6  (34)| 00:00:01 |

|*  1 |  FILTER              |          |       |       |            |          |

|   2 |   HASH GROUP BY      |          |     1 |    57 |     6  (34)| 00:00:01 |

|*  3 |    HASH JOIN OUTER   |          |     1 |    57 |     5  (20)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL| PRODUCTS |     1 |    22 |     2   (0)| 00:00:01 |

|*  5 |     TABLE ACCESS FULL| ORDERS   |     1 |    35 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(COALESCE(SUM("QUANTITY"),0)<20)

   3 - access("PRODUCT_ID"(+)="PRODUCT_ID")

   4 - filter("PRODUCTS"."AVAILABLE_FROM"<=ADD_MONTHS(SYSDATE@!,-1))

   5 - filter("ORDERS"."DISPATCH_DATE"(+)>=ADD_MONTHS(SYSDATE@!,-12))

Note

-----

   - dynamic sampling used for this statement (level=2)

EXPLAIN PLAN FOR
SELECT p.product_id,
       COALESCE(SUM(o.quantity), 0) AS sold_past_year
FROM   products p
       LEFT OUTER JOIN (
         SELECT product_id,
                quantity
         FROM   orders
         WHERE  dispatch_date  >= ADD_MONTHS(SYSDATE, -12))
       ) o
       ON (o.product_id = p.product_id)
WHERE  p.available_from <= ADD_MONTHS(SYSDATE, -1))
GROUP  BY p.product_id
HAVING COALESCE(SUM(o.quantity), 0) < 20; 
ORA-00905: missing keyword

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

Plan hash value: 123453394

---------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |     1 |    57 |     6  (34)| 00:00:01 |

|*  1 |  FILTER              |          |       |       |            |          |

|   2 |   HASH GROUP BY      |          |     1 |    57 |     6  (34)| 00:00:01 |

|*  3 |    HASH JOIN OUTER   |          |     1 |    57 |     5  (20)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL| PRODUCTS |     1 |    22 |     2   (0)| 00:00:01 |

|*  5 |     TABLE ACCESS FULL| ORDERS   |     1 |    35 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(COALESCE(SUM("QUANTITY"),0)<20)

   3 - access("PRODUCT_ID"(+)="PRODUCT_ID")

   4 - filter("PRODUCTS"."AVAILABLE_FROM"<=ADD_MONTHS(SYSDATE@!,-1))

   5 - filter("ORDERS"."DISPATCH_DATE"(+)>=ADD_MONTHS(SYSDATE@!,-12))

Note

-----

   - dynamic sampling used for this statement (level=2)