I have two tables:
create table product ( product_id number primary key, item_name varchar2(128 byte) not null, pricenumber not null, available_from date not null ); create table orders ( order_id number primary key, product_id number not null, amount number not null, order_price number not null, date_dispatched date not null, foreign key (product_id) references product(product_id) );
I need a query to find all products that have less than 20 orders in the last year, excluding products that have been available for less than 30 days.
Here is what I suggest. Any comments and corrections are appreciated.
First solution with EXISTS: Select product_id from products where available_from > ADD_MONTHS(systimestamp,-1) AND NOT EXISTS ( Select product_id from orders where dispatch_date > ADD_MONTHS(systimestamp,-12) group by product_id having count(*) > 10 ) Second solution with JOIN: Select p.product_id, count(*) from products p left outer join orders o on p.product_id = o.product_id where p.available_from < ADD_MONTHS(systimestamp,-1) and o.dispatch_date < ADD_MONTHS(systimestamp,-12) group by p.product_id having count(*) < 10