Наверное как-то так

SELECT lbt.`discount_card`, COUNT(1) as num
FROM `loy_transaction` as lt
LEFT JOIN `loy_bonus_transactions` as lbt
ON lt.`id` = lbt.`transaction_id`
GROUP BY lt.`shift_number`, lbt.`discount_card`
HAVING COUNT(1) > 3;