← Back to context

Comment by conradkay

4 days ago

There's an online playground with the data here: https://play.clickhouse.com/

Wrote up this query:

  SELECT
    db_name,
    sum(if(type = 'comment', 1, 0)) AS comment_mentions,
    sum(if(type = 'story', 1, 0)) AS post_mentions,
    count(*) AS total_mentions,
    sum(score) as total_score
  FROM hackernews
  ARRAY JOIN
    extractAll(replaceAll(LOWER(text), ' ', ''), '(sqlite|postgres|mysql|mongodb|redis|clickhouse|mariadb|oracle|sqlserver|duckdb)') AS db_name
  WHERE toYear(time) >= 2022
  GROUP BY
    db_name
  ORDER BY
    post_mentions DESC;

2 comments

conradkay

Reply