https://www.youtube.com/watch?v=Bp3XRVDA6SI

Similar to: - Dedup Combos

-- Drop the table if it exists
DROP TABLE IF EXISTS rewards;

-- Create the table
CREATE TABLE rewards (
    club_id INT,
    member_id INT,
    rewards VARCHAR(20)
);

-- Insert data into the table
INSERT INTO rewards (club_id, member_id, rewards)
VALUES
    (100, 110, NULL),
    (100, 111, 'AA:BB'),
    (102, 115, 'CC:AA:DD'),
    (102, 116, 'BB:CC'),
    (102, 117, 'PP:BB'),
    (103, 155, NULL),
    (103, 116, 'PP:AA:BB:CC'),
    (102, 110, 'CC:BB');

-- Select all data from the table
SELECT * FROM rewards;

Points --> AA -> 10, BB -> 20, CC -> 30, DD -> 40, PP -> 50.

WITH RECURSIVE
SUBSTRING()
SUBSTRING_INDEX()
CASE WHEN

with recursive cte as 
	(
		select 
			club_id,
            member_id,
            rewards,
            substring_index(rewards,':',1) as reward_part,
            substring(rewards,LOCATE(':',rewards)+1) as remaining
		from rewards
		union
        select
			club_id,
            member_id,
            rewards,
            substring_index(remaining,':',1),
            substring(remaining,locate(':',remaining)+1)
		from cte
        where remaining != ''    
    )
    
,cte_final as (
select 
	distinct club_id,
    member_id,
    rewards,
    reward_part,
    case when reward_part = 'AA' THEN 10 
    when reward_part = 'BB' THEN 20
    when reward_part = 'CC' THEN 30
    when reward_part = 'DD' THEN 40
    when reward_part = 'PP' THEN 50
    end as points
from cte
)

select club_id,sum(points) as pts from cte_final
group by club_id