(Fair disclosure this prompt is generated with AI, not sure if I should add this to the website, but I definitely can. but I was really curious and so I am gonna run it on you but anyone else whose interested can simply go to the clickhouse playground and run it with their own username as well, where changing Macha to their username if they are interested )

SELECT year, yearly_words, concat(toString(yearly_rank), ' / ', toString(total_active_in_year)) AS yearly_placement, round(100 * (1 - (yearly_rank / total_active_in_year)), 2) AS yearly_percentile FROM ( SELECT toYear(time) AS year, -- Simply use toYear(time) by AS username, sum(length(splitByWhitespace(text))) AS yearly_words, rank() OVER (PARTITION BY toYear(time) ORDER BY sum(length(splitByWhitespace(text))) DESC) AS yearly_rank, count(*) OVER (PARTITION BY toYear(time)) AS total_active_in_year FROM hackernews_history WHERE type = 'comment' AND deleted = 0 AND notEmpty(by) GROUP BY year, by ) WHERE lower(username) = lower('Macha') ORDER BY year DESC

(Ctrl/Cmd+Enter)

16 rows in result, 1.38 sec. 100.0%, Read 46.73 million rows, 16.18 GB (33.80 million/sec, 11.70 GB/sec)

№ year yearly_words yearly_placement yearly_percentile1

1 2025 26600 1248 / 140446 99.11

2 2024 29340 1056 / 141890 99.26

3 2023 54156 617 / 148623 99.58

4 2022 71330 377 / 138716 99.73

5 2021 70545 356 / 134766 99.74

6 2020 35551 849 / 130741 99.35

7 2019 13469 2398 / 117185 97.95

8 2018 7156 3924 / 109038 96.4

9 2017 8856 3137 / 109525 97.14

10 2016 3455 6487 / 99553 93.48

11 2015 3275 5463 / 91696 94.04

12 2014 2419 6601 / 85558 92.28

13 2013 3576 5021 / 79256 93.66

14 2012 3313 4287 / 65485 93.45

15 2011 5402 2245 / 51443 95.64

16 2010 1370 5357 / 32197 83.36

Hope this helps you :)

Have a nice day :]