By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE myt (
name VARCHAR(50),
food VARCHAR(50),
food_year INT
);
INSERT INTO myt (name, food, food_year) VALUES
('john', 'pizza', 2010),
('john', 'pizza', 2011),
('john', 'cake', 2012),
('tim', 'apples', 2015),
('david', 'apples', 2020),
('david', 'apples', 2021),
('alex', 'cookies', 2005),
('alex', 'cookies', 2006);
SELECT
name,
MIN(food) AS food,
food_year,
COUNT(*) as food_count,
SUM(COUNT(*)) OVER (PARTITION BY name ORDER BY food_year ROWS UNBOUNDED PRECEDING) as cumulative_count,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY name ORDER BY food_year ROWS UNBOUNDED PRECEDING) as percentage
FROM myt
GROUP BY
name,
food_year;
| NAME | FOOD | FOOD_YEAR | FOOD_COUNT | CUMULATIVE_COUNT | PERCENTAGE |
|---|---|---|---|---|---|
| alex | cookies | 2005 | 1 | 1 | 100.00000000000000000 |
| alex | cookies | 2006 | 1 | 2 | 50.00000000000000000 |
| david | apples | 2020 | 1 | 1 | 100.00000000000000000 |
| david | apples | 2021 | 1 | 2 | 50.00000000000000000 |
| john | pizza | 2010 | 1 | 1 | 100.00000000000000000 |
| john | pizza | 2011 | 1 | 2 | 50.00000000000000000 |
| john | cake | 2012 | 1 | 3 | 33.33333333333333333 |
| tim | apples | 2015 | 1 | 1 | 100.00000000000000000 |