How to filter the sum of commission per sold product by a specific affiliate?

My client has given me the order that he can see how much an affiliate receives in commission in a period of time. but now it gets complicated, filtered per product sold by the specific affiliate.

to show an example:

for the period of june, the affiliate with the id 4 has sold

product title | sum of commission
– product a | 150 €
– product b | 70 €
– product c | 1,5 €
– product d | 20 €

the goal is to see which product is sold the most by the affiliate id 4.

unfortunately, i have not found the setting. is there perhaps an add-on for this?

i tried to solve it with sql code, but unfortunately, it didn’t work. i think `wp_aff_referral_items` is not permanently inserted into the database.

you can try it out yourself:

select sum(referral_items.amount) as `sum of commission`, posts.post_title
from `wp_aff_referral_items` as referral_items
inner join `wp_posts` as posts on referral_items.object_id = posts.id
inner join `wp_aff_referrals` as referrals on referrals.referral_id = referral_items.referral_id
where posts.post_type = ‘product’
and (date(referrals.datetime) between ‘2022-06-01’ and ‘2022-06-30’)
and referrals.affiliate_id = 4
group by referral_items.object_id

thanks for the help