Select rows in the first table by frequency condition in the second

Question

PostgreSQL

there is postgresql, you need to pull data matched by condition from two tables:
loy_transaction (with columns id , purchase_number, cash_number, shift_number, shop_number, sale_time, transaction_time, operation_type, status, discountvalue)
and
loy_bonus_transactions ( id, bonus_account_type, bonus_amount, bonus_period_finish, bonus_period_start, discount_card , advert_act_guid, transaction_id )
columns with the same value in bold can be matched rows.
a query is required that will display the card number (discount_card) if the card participated in the same shift (shift_number) more than 3 times.
I settled on this one:

but, apparently, it gives everything, since rows with the same shift are not tied to transactions with accruals from the loy_bonus_transactions table

Answer 1

Probably something like this
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;

Answer 2

img.leprosorium.com/2193396 my request is fullsize

Answer 3

Select 
    * 
FROM 
    loy_transaction lt 
JOIN 
    loy_bonus_transactions lbt 
ON 
    lt.id = lbt.transaction_id 
group by 
    lbt.shift_number , lbt.discount_card 
HAVING count(*) > 3