Filtering for orders based on products included in those orders

[jzielinski-1653]

Hi all! I have (hopefully a fun) little Friday filtering question for you all. I have table with orders and order products, that I join together to end up with something like this
 

order id	product id	product launch date
100	200	1/1/2020
100	201	3/1/2023
100	202	2/1/2022
101	200	1/1/2020
101	202	2/1/2022
102	200	1/1/2020
102	204	3/15/2023

My goal is to enable a user to enter a product launch date, and then show all orders that include at least one product that launched after that date. The trick is that I want to show all the products included in that order as well!

Continuing the example above, if the user wanted to look for orders with products launched after 1/1/2023 they would return the following rows:

order id	product id	product launch date
100	200	1/1/2020
100	201	3/1/2023
100	202	2/1/2022
102	200	1/1/2020
102	204	3/15/2023

Rows for order_id 100 get returned because product 201 launched after 1/1/2023, and rows for order_id 102 get returned because product 204 launched after 1/1/2023. Rows for product 101 don’t get returned because neither included product launched after 1/1/2023.

I’ve been scratching my head thinking about how to set this up, but can’t come up with anything. Hoping this is a pattern that has an answer I just haven’t seen yet!