Filter Records Using a Subquery in SQL

SELECT
    p.product_name,
    p.price
FROM
    products p
WHERE
    p.product_id IN (
        SELECT
            oi.product_id
        FROM
            order_items oi
        GROUP BY
            oi.product_id
        HAVING
            COUNT(oi.order_id) > 5
    );

How it works: This query demonstrates how to use a subquery for advanced filtering. The inner subquery first identifies `product_id`s that have appeared in more than 5 distinct orders. The outer query then uses the `IN` operator to retrieve the `product_name` and `price` from the `products` table, but only for those products whose IDs were returned by the subquery.