Weird Join issue in mySQL

Question

Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than one month from today. Assume today is 2019-06-23.

Return the result table in any order.

The query result format is in the following example.

enter image description here

My code:

with orders as 
(
select
    book_Id
    ,sum(quantity) as quantity
from
    Orders
    where dispatch_date > '2018-06-23'
Group by 1
)

select b.book_id
,b.name
from books b left join orders o using (book_id)
where quantity < 10
and available_from < '2019-05-23'

Output: {"headers": ["book_id", "name"], "values": [[1, "Kalila And Demna"]]}

My left join is not grabbing all the books in books table and just taking what is present in both the tables. Scratching my head over it.

Answer 1

If you are interested in order data your primary table should be orders, join the books table on that.