sql.txt

select p.product_id
from Products as p
where p.available_from < (NOW() - INTERVAL 1 MONTH) 
  and p.product_id not in (
    select o.product_id
    from Orders as o
    where o.dispatch_date > (NOW() - INTERVAL 1 YEAR)
    group by o.product_id
    having sum(o.quantity) >= 10
  )
;


# You can test this out here
# http://sqlfiddle.com/#!9/9aba0db/1


# CREATE TABLE Products (
#     product_id INT NOT NULL AUTO_INCREMENT,
#     name VARCHAR(35) NOT NULL,
#     rrp INT NOT NULL,
#     available_from DATETIME NOT NULL,
#     PRIMARY KEY(product_id)
# );

# CREATE TABLE Orders (
#     order_id INT NOT NULL AUTO_INCREMENT,
#     product_id INT NOT NULL,
#     quantity INT NOT NULL,
#     dispatch_date DATETIME NOT NULL,
#     PRIMARY KEY(order_id),
#     FOREIGN KEY (product_id) REFERENCES Products(product_id)
# );

# -- data
# INSERT INTO Products
#     (product_id, name, rrp, available_from)
# VALUES
#     (1, 'James', 2, str_to_date('25-08-2021', '%d-%m-%Y')),
#     (2, 'John', 14, str_to_date('23-03-2005', '%d-%m-%Y')),
#     (3, 'Michael', 131, str_to_date('12-05-2009', '%d-%m-%Y')),
#     (4, 'Johnathon', 12, str_to_date('24-07-2016', '%d-%m-%Y'))
# ;

# INSERT INTO Orders
#     (order_id, product_id, quantity, dispatch_date)
# VALUES
#     (1, 3, 3, str_to_date('01-01-2021', '%d-%m-%Y')),
#     (2, 2, 1, str_to_date('23-03-2020', '%d-%m-%Y')),
#     (3, 3, 7, str_to_date('12-05-2021', '%d-%m-%Y')),
#     (4, 3, 3,str_to_date('24-07-2020', '%d-%m-%Y'))
# ;