I was asked a couple Lobsters stats questions and wanted to paste the answers both because there was some detail and because I might as well share the numbers with more than just the submitter. The policy is that I'm happy to answer questions or run queries as long as they don't create a lot of work for me or a worst-of list.
schema if you want to write queries: https://github.com/lobsters/lobsters/blob/master/db/schema.rb
https://lobste.rs/s/cqnzl5/lobste_rs_access_pattern_statistics_for might be interesting for some recent stats
- roughly how many people visit each day/month
Weekdays I see typically 12-15k unique IPs, so probably a a few more visitors than that to account for shared wifi like coffeshops and coworkers. I think weekends are around half that. I haven't kept a close eye on this because it wouldn't have changed any decision I've made (https://push.cx/2015/will-knowledge-change-a-decision-you-make).
- how many people make submissions each day/month
ef you want to work out a per-day breakdown for this please write a query, but I'd guess it runs around 9:1 weekdays:weekends.
ariaDB [lobsters]> select extract(year from created_at), extract(month from created_at), count(distinct user_id) from stories group by extract(year from created_at), extract(month from created_at);
+-------------------------------+--------------------------------+-------------------------+
| extract(year from created_at) | extract(month from created_at) | count(distinct user_id) |
+-------------------------------+--------------------------------+-------------------------+
| 2012 | 6 | 1 |
| 2012 | 7 | 17 |
| 2012 | 8 | 41 |
| 2012 | 9 | 123 |
| 2012 | 10 | 89 |
| 2012 | 11 | 64 |
| 2012 | 12 | 60 |
| 2013 | 1 | 71 |
| 2013 | 2 | 61 |
| 2013 | 3 | 46 |
| 2013 | 4 | 50 |
| 2013 | 5 | 49 |
| 2013 | 6 | 54 |
| 2013 | 7 | 54 |
| 2013 | 8 | 44 |
| 2013 | 9 | 36 |
| 2013 | 10 | 55 |
| 2013 | 11 | 64 |
| 2013 | 12 | 60 |
| 2014 | 1 | 169 |
| 2014 | 2 | 155 |
| 2014 | 3 | 168 |
| 2014 | 4 | 195 |
| 2014 | 5 | 188 |
| 2014 | 6 | 168 |
| 2014 | 7 | 170 |
| 2014 | 8 | 165 |
| 2014 | 9 | 160 |
| 2014 | 10 | 135 |
| 2014 | 11 | 177 |
| 2014 | 12 | 145 |
| 2015 | 1 | 170 |
| 2015 | 2 | 175 |
| 2015 | 3 | 183 |
| 2015 | 4 | 188 |
| 2015 | 5 | 154 |
| 2015 | 6 | 155 |
| 2015 | 7 | 197 |
| 2015 | 8 | 170 |
| 2015 | 9 | 169 |
| 2015 | 10 | 193 |
| 2015 | 11 | 200 |
| 2015 | 12 | 221 |
| 2016 | 1 | 245 |
| 2016 | 2 | 228 |
| 2016 | 3 | 235 |
| 2016 | 4 | 224 |
| 2016 | 5 | 244 |
| 2016 | 6 | 236 |
| 2016 | 7 | 220 |
| 2016 | 8 | 226 |
| 2016 | 9 | 240 |
| 2016 | 10 | 229 |
| 2016 | 11 | 246 |
| 2016 | 12 | 248 |
| 2017 | 1 | 289 |
| 2017 | 2 | 289 |
| 2017 | 3 | 297 |
| 2017 | 4 | 270 |
| 2017 | 5 | 320 |
| 2017 | 6 | 297 |
| 2017 | 7 | 322 |
| 2017 | 8 | 310 |
| 2017 | 9 | 322 |
| 2017 | 10 | 345 |
| 2017 | 11 | 305 |
| 2017 | 12 | 278 |
| 2018 | 1 | 344 |
| 2018 | 2 | 305 |
| 2018 | 3 | 358 |
| 2018 | 4 | 339 |
| 2018 | 5 | 333 |
| 2018 | 6 | 156 |
+-------------------------------+--------------------------------+-------------------------+
73 rows in set (2.40 sec)
- how many people comment each day/month
MariaDB [lobsters]> select extract(year from created_at), extract(month from created_at), count(distinct user_id) from comments group by extract(year from created_at), extract(month from created_at);
+-------------------------------+--------------------------------+-------------------------+
| extract(year from created_at) | extract(month from created_at) | count(distinct user_id) |
+-------------------------------+--------------------------------+-------------------------+
| 2012 | 7 | 12 |
| 2012 | 8 | 47 |
| 2012 | 9 | 131 |
| 2012 | 10 | 88 |
| 2012 | 11 | 51 |
| 2012 | 12 | 66 |
| 2013 | 1 | 57 |
| 2013 | 2 | 37 |
| 2013 | 3 | 45 |
| 2013 | 4 | 31 |
| 2013 | 5 | 29 |
| 2013 | 6 | 42 |
| 2013 | 7 | 44 |
| 2013 | 8 | 42 |
| 2013 | 9 | 24 |
| 2013 | 10 | 35 |
| 2013 | 11 | 45 |
| 2013 | 12 | 43 |
| 2014 | 1 | 224 |
| 2014 | 2 | 227 |
| 2014 | 3 | 210 |
| 2014 | 4 | 264 |
| 2014 | 5 | 300 |
| 2014 | 6 | 236 |
| 2014 | 7 | 265 |
| 2014 | 8 | 216 |
| 2014 | 9 | 215 |
| 2014 | 10 | 202 |
| 2014 | 11 | 275 |
| 2014 | 12 | 217 |
| 2015 | 1 | 247 |
| 2015 | 2 | 246 |
| 2015 | 3 | 294 |
| 2015 | 4 | 319 |
| 2015 | 5 | 268 |
| 2015 | 6 | 287 |
| 2015 | 7 | 369 |
| 2015 | 8 | 326 |
| 2015 | 9 | 288 |
| 2015 | 10 | 350 |
| 2015 | 11 | 377 |
| 2015 | 12 | 373 |
| 2016 | 1 | 410 |
| 2016 | 2 | 420 |
| 2016 | 3 | 467 |
| 2016 | 4 | 455 |
| 2016 | 5 | 479 |
| 2016 | 6 | 499 |
| 2016 | 7 | 461 |
| 2016 | 8 | 441 |
| 2016 | 9 | 485 |
| 2016 | 10 | 503 |
| 2016 | 11 | 507 |
| 2016 | 12 | 484 |
| 2017 | 1 | 575 |
| 2017 | 2 | 569 |
| 2017 | 3 | 577 |
| 2017 | 4 | 580 |
| 2017 | 5 | 618 |
| 2017 | 6 | 579 |
| 2017 | 7 | 687 |
| 2017 | 8 | 652 |
| 2017 | 9 | 631 |
| 2017 | 10 | 633 |
| 2017 | 11 | 641 |
| 2017 | 12 | 613 |
| 2018 | 1 | 688 |
| 2018 | 2 | 651 |
| 2018 | 3 | 703 |
| 2018 | 4 | 723 |
| 2018 | 5 | 719 |
| 2018 | 6 | 426 |
+-------------------------------+--------------------------------+-------------------------+
72 rows in set (0.26 sec)
- what % of users have been active in the last month
MariaDB [lobsters]> select count(*) from users where deleted_at is null and banned_at is null;
+----------+
| count(*) |
+----------+
| 9297 |
+----------+
1 row in set (0.04 sec)
MariaDB [lobsters]> select count(distinct id) from users where
-> id in (select distinct user_id from stories where created_at >= 2018-05-22)
-> or
-> id in (select distinct user_id from comments where created_at >= 2018-05-22)
-> or
-> id in (select distinct user_id from votes where created_at >= 2018-05-22)
-> ;
+--------------------+
| count(distinct id) |
+--------------------+
| 6144 |
+--------------------+
1 row in set, 3 warnings (2.17 sec)
MariaDB [lobsters]> select count(distinct id) from users where
-> id in (select distinct user_id from stories where created_at >= 2018-05-22)
-> ;
+--------------------+
| count(distinct id) |
+--------------------+
| 2926 |
+--------------------+
1 row in set, 1 warning (2.25 sec)
MariaDB [lobsters]> select count(distinct id) from users where id in (select distinct user_id from votes where created_at >= 2018-05-22);
+--------------------+
| count(distinct id) |
+--------------------+
| 6141 |
+--------------------+
1 row in set, 1 warning (0.27 sec)
MariaDB [lobsters]> select count(distinct id) from users where id in (select distinct user_id from comments where created_at >= 2018-05-22);
+--------------------+
| count(distinct id) |
+--------------------+
| 3636 |
+--------------------+
1 row in set, 1 warning (0.90 sec)
so 6144/9297 = 66%, holy shit!