SELECT 'data engineer' AS NAME,
       count(*)        AS mentions
FROM   hackernews_items
WHERE  title ilike '%data engineer%'
OR     text ilike '%data engineer%'
UNION
SELECT 'software engineer' AS NAME,
       count(*)            AS mentions
FROM   hackernews_items
WHERE  title ilike '%software engineer%'
OR     text ilike '%software engineer%';

+-------------------+----------+
| name              | mentions |
|-------------------+----------|
| data engineer     | 1415     |
| software engineer | 14411    |
+-------------------+----------+

SELECT   h.url,
         h.score
FROM     hackernews_items h
WHERE    h.url ilike '%xkcd.com%'
AND      h.time BETWEEN date '2022-01-01' AND date '2023-01-01'
ORDER BY h.score DESC limit 5

+-------------------------------+-------+
| url                           | score |
|-------------------------------+-------|
| https://what-if.xkcd.com/158/ | 387   |
| https://xkcd.com/2617/        | 361   |
| https://xkcd.com/             | 100   |
| https://xkcd.com/2682/        | 77    |
| https://what-if.xkcd.com/161/ | 54    |
+-------------------------------+-------+

SELECT   h.by     AS USER,
         count(*) AS comments
FROM     hackernews_items h
WHERE    h.by != ''
AND      h.type = 'comment'
AND      h.time BETWEEN date '2022-01-01' AND      date '2023-01-01'
GROUP BY h.by
ORDER BY comments DESC limit 3;

+---------+----------+
| user    | comments |
|---------+----------|
| bombcar | 7307     |
| dang    | 6688     |
| pjmlp   | 6450     |
+---------+----------+

SELECT   h.time,
         h.title
FROM     hackernews_items h
WHERE    h.type ='job'
AND      h.title ilike '%remote%'
ORDER BY h.time DESC limit 3;

+---------------------+---------------------------------------------------------------------+
| time                | title                                                               |
|---------------------+---------------------------------------------------------------------|
| 2023-01-09 17:00:05 | Kable (YC W22) Is Hiring Lead Engineer (Remote/US)                  |
| 2023-01-07 12:04:59 | Svix (YC W21) Is Hiring (Remote) – Enterprise-Ready Webhook Service |
| 2022-12-29 21:01:08 | Hive (YC S14) is hiring devs #3-10 in 2023 (Canada remote)          |
+---------------------+---------------------------------------------------------------------+