2025-03-27 lobsters tags query

tags.sql

select category, tag, privileged, is_media, active, permit_by_new_users, hotness_mod, (select count(*) from taggings where tag_id = tags.id) as stories, (select count(*) from tag_filters where tag_id = tags.id) as filters, (select date(created_at) from stories join taggings on stories.id = taggings.story_id and tag_id = tags.id order by created_at asc limit 1) as first from tags join categories on category_id = categories.id order by first asc;
+-------------+----------------+------------+----------+--------+---------------------+-------------+---------+---------+------------+
| category    | tag            | privileged | is_media | active | permit_by_new_users | hotness_mod | stories | filters | first      |
+-------------+----------------+------------+----------+--------+---------------------+-------------+---------+---------+------------+
| platforms   | mobile         |          0 |        0 |      1 |                   1 |           0 |     919 |     148 | 2012-06-30 |
| platforms   | browsers       |          0 |        0 |      1 |                   1 |           0 |    1940 |      97 | 2012-06-30 |
| os          | openbsd        |          0 |        0 |      1 |                   1 |           0 |    1441 |     234 | 2012-06-30 |
| practices   | scaling        |          0 |        0 |      1 |                   1 |           0 |     950 |      81 | 2012-07-01 |
| lobsters    | meta           |          0 |        0 |      1 |                   0 |       -0.25 |     684 |    1383 | 2012-07-01 |
| os          | unix           |          0 |        0 |      1 |                   1 |           0 |    2649 |      68 | 2012-07-02 |
| os          | linux          |          0 |        0 |      1 |                   1 |           0 |    5476 |      78 | 2012-07-02 |
| genre       | news           |          0 |        0 |      0 |                   1 |           0 |     214 |       6 | 2012-07-02 |
| languages   | go             |          0 |        0 |      1 |                   1 |           0 |    3178 |     220 | 2012-07-02 |
| field       | science        |          0 |        0 |      1 |                   1 |           0 |    1202 |     104 | 2012-07-02 |
| languages   | c              |          0 |        0 |      1 |                   1 |           0 |    3231 |     170 | 2012-07-02 |
| practices   | devops         |          0 |        0 |      1 |                   1 |           0 |    2999 |     130 | 2012-07-03 |
| practices   | privacy        |          0 |        0 |      1 |                   1 |           0 |    2231 |      75 | 2012-07-03 |
| practices   | security       |          0 |        0 |      1 |                   1 |           0 |   10950 |      79 | 2012-07-03 |
| interaction | visualization  |          0 |        0 |      1 |                   1 |           0 |    1029 |      91 | 2012-07-03 |
| os          | ios            |          0 |        0 |      1 |                   1 |           0 |     956 |     255 | 2012-07-05 |
| format      | pdf            |          0 |        1 |      1 |                   1 |           0 |    4646 |      66 | 2012-07-06 |
| languages   | python         |          0 |        0 |      1 |                   1 |           0 |    3818 |     196 | 2012-07-06 |
| field       | hardware       |          0 |        0 |      1 |                   1 |           0 |    6484 |      91 | 2012-07-06 |
| languages   | javascript     |          0 |        0 |      1 |                   1 |           0 |    4863 |     226 | 2012-07-06 |
| tools       | vim            |          0 |        0 |      1 |                   1 |           0 |     507 |     179 | 2012-07-07 |
| languages   | php            |          0 |        0 |      1 |                   1 |           0 |     395 |     397 | 2012-07-07 |
| languages   | ruby           |          0 |        0 |      1 |                   1 |           0 |    2360 |     306 | 2012-07-07 |
| os          | android        |          0 |        0 |      1 |                   1 |           0 |     832 |     260 | 2012-07-07 |
| languages   | assembly       |          0 |        0 |      1 |                   1 |           0 |    1279 |     225 | 2012-07-08 |
| interaction | design         |          0 |        0 |      1 |                   1 |           0 |    1695 |     112 | 2012-07-09 |
| compsci     | compsci        |          0 |        0 |      1 |                   1 |           0 |    3908 |      87 | 2012-07-10 |
| format      | video          |          0 |        1 |      1 |                   1 |           0 |    4449 |     162 | 2012-07-10 |
| tools       | compilers      |          0 |        0 |      1 |                   1 |           0 |    2509 |     103 | 2012-07-11 |
| culture     | person         |          0 |        0 |      1 |                   1 |           0 |    1049 |     115 | 2012-07-12 |
| genre       | event          |          0 |        0 |      1 |                   1 |           0 |     784 |     100 | 2012-07-18 |
| format      | book           |          0 |        1 |      1 |                   1 |           0 |     952 |      60 | 2012-07-19 |
| languages   | lua            |          0 |        0 |      1 |                   1 |           0 |     362 |     233 | 2012-07-19 |
| compsci     | networking     |          0 |        0 |      1 |                   1 |           0 |    3332 |      78 | 2012-07-21 |
| languages   | lisp           |          0 |        0 |      1 |                   1 |           0 |    1870 |     257 | 2012-07-22 |
| practices   | reversing      |          0 |        0 |      1 |                   1 |           0 |    1630 |     107 | 2012-07-22 |
| culture     | law            |          0 |        0 |      1 |                   1 |           0 |    1515 |     175 | 2012-07-24 |
| genre       | release        |          0 |        0 |      1 |                   1 |           0 |    4918 |      74 | 2012-07-24 |
| tools       | databases      |          0 |        0 |      1 |                   1 |           0 |    4130 |      95 | 2012-08-03 |
| practices   | virtualization |          0 |        0 |      1 |                   1 |           0 |     856 |      94 | 2012-08-13 |
| platforms   | web            |          0 |        0 |      1 |                   1 |           0 |    7895 |     113 | 2012-08-13 |
| genre       | rant           |          0 |        0 |      1 |                   0 |       -0.25 |    1181 |     178 | 2012-08-15 |
| platforms   | merkle-trees   |          0 |        0 |      1 |                   0 |           0 |     771 |     309 | 2012-08-16 |
| practices   | api            |          0 |        0 |      1 |                   1 |           0 |    1533 |      76 | 2012-08-17 |
| compsci     | distributed    |          0 |        0 |      1 |                   1 |           0 |    2561 |      80 | 2012-08-17 |
| format      | show           |          0 |        1 |      1 |                   0 |           0 |    1902 |      54 | 2012-08-18 |
| os          | freebsd        |          0 |        0 |      1 |                   1 |           0 |    1266 |     247 | 2012-08-31 |
| os          | mac            |          0 |        0 |      1 |                   1 |           0 |    1443 |     184 | 2012-09-05 |
| languages   | rust           |          0 |        0 |      1 |                   1 |           0 |    4227 |     248 | 2012-09-06 |
| lobsters    | announce       |          1 |        0 |      1 |                   0 |           1 |      48 |       0 | 2012-09-06 |
| field       | math           |          0 |        0 |      1 |                   1 |           0 |    2682 |     130 | 2012-09-06 |
| languages   | haskell        |          0 |        0 |      1 |                   1 |           0 |    2161 |     286 | 2012-09-07 |
| culture     | philosophy     |          0 |        0 |      1 |                   1 |           0 |    1096 |     126 | 2012-09-10 |
| field       | finance        |          0 |        0 |      1 |                   1 |           0 |     478 |     171 | 2012-09-11 |
| os          | windows        |          0 |        0 |      1 |                   1 |           0 |    1322 |     324 | 2012-09-13 |
| languages   | erlang         |          0 |        0 |      1 |                   1 |           0 |     736 |     266 | 2012-09-20 |
| languages   | scala          |          0 |        0 |      1 |                   1 |           0 |     436 |     321 | 2012-09-20 |
| genre       | satire         |          0 |        0 |      1 |                   0 |       -0.25 |     710 |     168 | 2012-09-27 |
| tools       | emacs          |          0 |        0 |      1 |                   1 |           0 |     714 |     340 | 2012-10-01 |
| os          | netbsd         |          0 |        0 |      1 |                   1 |           0 |     621 |     181 | 2012-10-17 |
| field       | cogsci         |          0 |        0 |      1 |                   1 |           0 |     383 |     169 | 2012-11-16 |
| languages   | java           |          0 |        0 |      1 |                   1 |           0 |    1141 |     315 | 2012-11-23 |
| languages   | elm            |          0 |        0 |      1 |                   1 |           0 |     247 |     225 | 2012-12-03 |
| platforms   | ipv6           |          0 |        0 |      0 |                   1 |           0 |      17 |      27 | 2012-12-12 |
| practices   | practices      |          0 |        0 |      1 |                   1 |           0 |    7530 |      82 | 2012-12-14 |
| genre       | job            |          0 |        0 |      1 |                   0 |           0 |     224 |     159 | 2013-01-07 |
| lobsters    | interview      |          1 |        0 |      1 |                   0 |           0 |      12 |       0 | 2013-01-28 |
| tools       | vcs            |          0 |        0 |      1 |                   1 |           0 |    1425 |      65 | 2013-02-07 |
| compsci     | programming    |          0 |        0 |      1 |                   1 |           0 |   15456 |      61 | 2013-03-01 |
| platforms   | games          |          0 |        0 |      1 |                   1 |           0 |    2483 |     164 | 2013-03-24 |
| os          | illumos        |          0 |        0 |      1 |                   1 |           0 |      98 |     144 | 2013-07-01 |
| languages   | apl            |          0 |        0 |      1 |                   1 |           0 |     163 |     157 | 2013-07-28 |
| format      | slides         |          0 |        1 |      1 |                   1 |           0 |     506 |      84 | 2013-08-04 |
| languages   | dotnet         |          0 |        0 |      1 |                   1 |           0 |     819 |     402 | 2013-12-11 |
| format      | ask            |          0 |        1 |      1 |                   0 |           0 |    2222 |      70 | 2014-01-06 |
| culture     | culture        |          0 |        0 |      1 |                   0 |       -0.25 |    3686 |     148 | 2014-02-03 |
| languages   | perl           |          0 |        0 |      1 |                   1 |           0 |     292 |     256 | 2014-06-01 |
| genre       | art            |          0 |        0 |      1 |                   1 |           0 |     926 |     118 | 2014-06-10 |
| languages   | ml             |          0 |        0 |      1 |                   1 |           0 |     703 |     221 | 2014-07-16 |
| format      | audio          |          0 |        1 |      1 |                   1 |           0 |     382 |     152 | 2014-08-15 |
| languages   | swift          |          0 |        0 |      1 |                   1 |           0 |     502 |     283 | 2014-09-19 |
| languages   | elixir         |          0 |        0 |      1 |                   1 |           0 |     727 |     242 | 2015-03-11 |
| compsci     | graphics       |          0 |        0 |      1 |                   1 |           0 |    2172 |     141 | 2015-05-02 |
| languages   | kotlin         |          0 |        0 |      1 |                   1 |           0 |      94 |      80 | 2015-05-10 |
| practices   | testing        |          0 |        0 |      1 |                   1 |           0 |    1478 |      58 | 2015-10-27 |
| genre       | historical     |          0 |        0 |      1 |                   1 |           0 |    4172 |      71 | 2016-01-25 |
| languages   | zig            |          0 |        0 |      1 |                   1 |           0 |     382 |     106 | 2016-02-09 |
| tools       | systemd        |          0 |        0 |      1 |                   1 |           0 |     198 |     135 | 2016-03-03 |
| practices   | debugging      |          0 |        0 |      1 |                   1 |           0 |    1131 |      39 | 2016-03-06 |
| field       | cryptography   |          0 |        0 |      1 |                   1 |           0 |    1566 |      83 | 2016-04-13 |
| languages   | css            |          0 |        0 |      1 |                   1 |           0 |     658 |     144 | 2016-06-28 |
| languages   | nodejs         |          0 |        0 |      1 |                   1 |           0 |     506 |     199 | 2016-08-24 |
| field       | education      |          0 |        0 |      1 |                   1 |           0 |     759 |      62 | 2017-02-26 |
| languages   | d              |          0 |        0 |      1 |                   1 |           0 |      98 |     183 | 2017-04-07 |
| compsci     | formalmethods  |          0 |        0 |      1 |                   1 |           0 |    1223 |     131 | 2017-04-23 |
| languages   | objectivec     |          0 |        0 |      1 |                   1 |           0 |      68 |     216 | 2017-05-04 |
| languages   | c++            |          0 |        0 |      1 |                   1 |           0 |    2261 |     163 | 2017-05-04 |
| languages   | fortran        |          0 |        0 |      1 |                   1 |           0 |      63 |     216 | 2017-05-04 |
| practices   | performance    |          0 |        0 |      1 |                   1 |           0 |    2995 |      24 | 2017-05-26 |
| compsci     | ai             |          0 |        0 |      1 |                   1 |           0 |    3254 |     236 | 2017-06-14 |
| os          | dragonflybsd   |          0 |        0 |      1 |                   1 |           0 |      77 |     146 | 2017-09-08 |
| languages   | clojure        |          0 |        0 |      1 |                   1 |           0 |     711 |     168 | 2017-09-09 |
| compsci     | plt            |          0 |        0 |      1 |                   1 |           0 |    2734 |      64 | 2017-11-01 |
| compsci     | osdev          |          0 |        0 |      1 |                   1 |           0 |    1151 |      49 | 2018-01-13 |
| format      | transcript     |          0 |        1 |      1 |                   1 |           0 |     117 |      37 | 2018-04-25 |
| platforms   | wasm           |          0 |        0 |      1 |                   1 |           0 |     537 |      69 | 2018-09-04 |
| interaction | a11y           |          0 |        0 |      1 |                   1 |           0 |     318 |      74 | 2019-01-16 |
| platforms   | email          |          0 |        0 |      1 |                   1 |           0 |     288 |      34 | 2019-09-07 |
| os          | nix            |          0 |        0 |      1 |                   1 |           0 |     487 |     111 | 2021-11-22 |
| languages   | gleam          |          0 |        0 |      1 |                   1 |           0 |      46 |      29 | 2023-10-26 |
| platforms   | retrocomputing |          0 |        0 |      1 |                   1 |           0 |     105 |      19 | 2024-04-28 |
| tools       | editors        |          0 |        0 |      1 |                   1 |           0 |      32 |       3 | 2024-11-14 |
| tools       | vscode         |          0 |        0 |      1 |                   1 |           0 |       3 |      19 | 2024-11-24 |
+-------------+----------------+------------+----------+--------+---------------------+-------------+---------+---------+------------+
113 rows in set (1.214 sec)