https://www.youtube.com/watch?v=OdWk-9W2cns

create table polls
(
user_id varchar(4),
poll_id varchar(3),
poll_option_id varchar(3),
amount int,
created_date date
)
;
-- Insert sample data into the investments table
INSERT INTO polls (user_id, poll_id, poll_option_id, amount, created_date) VALUES
('id1', 'p1', 'A', 200, '2021-12-01'),
('id2', 'p1', 'C', 250, '2021-12-01'),
('id3', 'p1', 'A', 200, '2021-12-01'),
('id4', 'p1', 'B', 500, '2021-12-01'),
('id5', 'p1', 'C', 50, '2021-12-01'),
('id6', 'p1', 'D', 500, '2021-12-01'),
('id7', 'p1', 'C', 200, '2021-12-01'),
('id8', 'p1', 'A', 100, '2021-12-01'),
('id9', 'p2', 'A', 300, '2023-01-10'),
('id10', 'p2', 'C', 400, '2023-01-11'),
('id11', 'p2', 'B', 250, '2023-01-12'),
('id12', 'p2', 'D', 600, '2023-01-13'),
('id13', 'p2', 'C', 150, '2023-01-14'),
('id14', 'p2', 'A', 100, '2023-01-15'),
('id15', 'p2', 'C', 200, '2023-01-16');

create table poll_answers
(
poll_id varchar(3),
correct_option_id varchar(3)
)
;
-- Insert sample data into the poll_answers table
INSERT INTO poll_answers (poll_id, correct_option_id) VALUES
('p1', 'C'),('p2', 'A');

WITH

With disamt AS(
select po.poll_id,sum(amount) as losers
from polls po
JOIN poll_answers pa ON po.poll_id = pa.poll_id
WHERE po.poll_option_id <> pa.correct_option_id
GROUP BY po.poll_id
), cte2 AS (
select po.poll_id,po.user_id,round(amount/sum(amount) OVER (partition by poll_id),2) as winner_percent
from polls po
JOIN poll_answers pa ON po.poll_id = pa.poll_id
WHERE po.poll_option_id = pa.correct_option_id
)
select  
c.user_id,c.poll_id,losers*winner_percent AS Winning 
from disamt d JOIN cte2 c ON d.poll_id = c.poll_id