Because it's not as simple as it looks, which is apparent if you see both the database schema and some
test data.
within lookups, there can be (and for this query there is) two levels of lookup. given a 'HYG' type, there are 'HYG0', 'HYG1', 'HYG2' and 'HYG3' subtypes. And each of these is a product, but 3 different products for each, filtered by the range for which it is valid.
Starting from innermost:
Query1: find subtypes for 'HYG'
Query2: find products in the above list
Query3: find the name of the 'super type' for each product that falls within a certain date range.