Suppose I have two tables:

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)
);

What's the most efficient way to find products that have sold fewer than 20 units in the last year (i.e., the sum of order quantities for the product over past year is less than 20), excluding products that have been available for less than 1 month?

My current solution is:

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; 

But I'm not sure how efficient this is?

Thanks for any help!

CodePudding user response：

Your query appears fine; however, it can be written in a simpler format without the sub-query on the products table and using a LEFT JOIN which puts the driving table first (which make no difference to how the code runs but may be easier for developers to comprehend).

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; 

Comparing the EXPLAIN PLAN to your code, it is identical. db<>fiddle