Show HN: Hacker News em dash user leaderboard pre-ChatGPT

  SELECT 
    EXTRACT(YEAR FROM timestamp) AS year, 
    SUM(CASE WHEN text LIKE '%—%' THEN 1 ELSE 0 END) AS withDash, 
    COUNT(*) AS total, 
    SUM(CASE WHEN text LIKE '%—%' THEN 1 ELSE 0 END) / COUNT(*) AS fraction
  FROM `bigquery-public-data.hacker_news.full` 
    WHERE type = 'comment' 
  GROUP BY year 
  ORDER BY year;

  year with—   total  frac
  2006     0      12 0.000
  2007    13   70858 0.000
  2008   461  247922 0.001
  2009  1497  491034 0.003
  2010  3835  842438 0.005
  2011  4719 1044913 0.005
  2012  5648 1246782 0.005
  2013  7881 1665185 0.005
  2014  8400 1510814 0.006
  2015  9967 1642912 0.006
  2016 12081 2093612 0.006
  2017 14530 2361709 0.006
  2018 19246 2384086 0.008
  2019 23662 2755063 0.009
  2020 27316 3243173 0.008
  2021 32863 3765921 0.009
  2022 34657 4062159 0.009
  2023 36611 4221940 0.009
  2024 32543 3339861 0.010
  2025 30608 2231919 0.014

Querying for the users who use "—" most as a proportion of all their comments:

  SELECT
    `by`,
    SUM(CASE WHEN text LIKE '%—%' THEN 1 ELSE 0 END) / COUNT(*) AS fraction,
    COUNT(*) AS total,
    MIN(timestamp) AS minTime,
    MAX(timestamp) AS maxTime
  FROM `bigquery-public-data.hacker_news.full` 
  WHERE 
    type = 'comment' AND 
    timestamp < '2022-11-30' 
  GROUP BY `by`
  HAVING COUNT(*) > 100
  ORDER BY fraction DESC
  LIMIT 250;

[0] https://console.cloud.google.com/marketplace/product/y-combi...

[1] https://news.ycombinator.com/threads?id=zmgsabst

[2] https://news.ycombinator.com/threads?id=westoncb