;WITH cte (package_id, product_id, child_acct_id)
AS
(
    SELECT S.package_id, SP.product_id, SP.child_acct_id
    FROM subscription S
        JOIN subscription_package SP
            ON S.sub_id = SP.sub_id
    WHERE S.sub_active = 1
)
SELECT DISTINCT C.product_id, C.child_acct_id
FROM cte C
    JOIN
    (
        -- Products with multiple packages
        SELECT D1.ProductID
        FROM
        (
            SELECT DISTINCT C1.product_id, C1.package_id
            FROM cte C1
        ) D1
        GROUP BY D1.ProductID
        HAVING COUNT(*) > 1
    ) D
    ON C.product_id = D.product_id
ORDER BY product_id, child_acct_id