#plebchain — Public Fediverse posts

@NEETzsche @alex @lain just have a bot publish it every hour

pleroma=> select t.tag->>'name' as tag, count(t.tag) as count, count(distinct t.actor) as users from (select jsonb_array_elements(data->'tag') as tag, data->'actor' as actor from objects where inserted_at > now() - interval '7 days' and data->>'tag' <> '[]') as t where t.tag->>'type' = 'Hashtag' group by t.tag->>'name' order by count(t.tag) desc limit 100;
#press | 16117 | 75
#news | 7746 | 307
#nsfw | 4874 | 247
#rssfeed | 3505 | 47
#photography | 2826 | 892
#ryanair | 2777 | 5
#ei | 2692 | 2
#russia | 2143 | 171
#ukraine | 1919 | 263
#art | 1744 | 553
#hashtaggames | 1725 | 230
#roc | 1580 | 5
#nowplaying | 1498 | 142
#nature | 1490 | 515
#usa | 1482 | 154
#music | 1412 | 465
#reddit | 1405 | 40
#shota | 1393 | 30
#nude | 1361 | 64
#bot | 1358 | 50
#sexpervertsyndicate | 1350 | 5
#hornywaifu | 1342 | 6
#новости | 1321 | 3
#catsofmastodon | 1296 | 702
#yandere | 1273 | 6
#planealert | 1247 | 6
#meme | 1243 | 113
#ass | 1167 | 59
#tits | 1137 | 29
#darkskin | 1105 | 3
#history | 1102 | 238
#ship | 1087 | 13
#vessel | 1062 | 2
#ais | 1061 | 1
#vesselalert | 1061 | 1
#rssi | 1061 | 1
#mmsi | 1061 | 1
#kx1t | 1061 | 1
#flag | 1054 | 3
#travel | 1019 | 167
#aiart | 1013 | 81
#tech | 1006 | 133
#bitcoin | 1005 | 317
#politics | 994 | 241
#pussy | 986 | 33
#girls | 963 | 17
#milf | 959 | 22
#mastodon | 921 | 528
#russiaukrainewar | 919 | 22
#porn | 882 | 55
#nostr | 864 | 248
#lewd | 858 | 31
#știri | 857 | 2
#mastoart | 851 | 273
#bbc | 847 | 38
#stablediffusion | 845 | 39
#metal | 844 | 68
#linux | 834 | 426
#cats | 831 | 413
#mastindia | 827 | 12
#caturday | 819 | 635
#trump | 815 | 311
#россия | 814 | 1
#рбк | 814 | 1
#rbc | 814 | 1
#gaming | 794 | 239
#memes | 791 | 106
#ponytail | 782 | 1
#heavymetal | 773 | 16
#bulligf | 770 | 1
#tanime | 766 | 1
#putinendtheinvasion | 760 | 2
#9yrinvasionofukraine | 760 | 2
#wanteddeadoralive | 759 | 2
#apple | 748 | 148
#cameltoe | 738 | 1
#climatechange | 737 | 391
#southafrica | 713 | 22
#ai | 672 | 383
#aiwanger | 668 | 324
#climate | 666 | 226
#mastodonindians | 665 | 7
#bloomscrolling | 656 | 266
#cyberpunkedgerunners | 651 | 2
#aihorde | 642 | 4
#canada | 632 | 242
#india | 631 | 79
#fediverse | 631 | 348
#disaster | 623 | 9
#idalia | 617 | 180
#cybersecurity | 606 | 190
#plebchain | 599 | 112
#businesscontinuity | 596 | 1
#interruption | 596 | 1
#smem | 596 | 1
#naturephotography | 573 | 182
#furry | 571 | 238
#気象警報注意報 | 570 | 1
#external | 564 | 1
#topwar | 563 | 1

or by distinct actors
pleroma=> select t.tag->>'name' as tag, count(t.tag) as count, count(distinct t.actor) as users from (select jsonb_array_elements(data->'tag') as tag, data->'actor' as actor from objects where inserted_at > now() - interval '7 days' and data->>'tag' <> '[]') as t where t.tag->>'type' = 'Hashtag' group by t.tag->>'name' order by count(distinct t.actor) desc limit 100;
#photography | 2825 | 892
#catsofmastodon | 1297 | 702
#caturday | 819 | 635
#art | 1744 | 553
#mastodon | 921 | 528
#nature | 1491 | 516
#music | 1412 | 465
#linux | 834 | 426
#silentsunday | 489 | 417
#cats | 831 | 413
#climatechange | 738 | 392
#ai | 672 | 383
#fediverse | 631 | 348
#aiwanger | 668 | 324
#dogsofmastodon | 520 | 318
#bitcoin | 1005 | 317
#trump | 815 | 311
#covid | 555 | 310
#news | 7746 | 307
#introduction | 317 | 299
#mastoart | 851 | 273
#bloomscrolling | 656 | 266
#ukraine | 1919 | 263
#nostr | 864 | 248
#nsfw | 4874 | 247
#books | 349 | 247
#canada | 633 | 243
#politics | 994 | 241
#gaming | 794 | 239
#history | 1102 | 238
#furry | 571 | 238
#cat | 456 | 234
#opensource | 454 | 231
#hashtaggames | 1725 | 230
#birds | 523 | 227
#climate | 666 | 226
#privacy | 323 | 223
#climatecrisis | 508 | 223
#gardening | 464 | 222
#science | 493 | 222
#twitter | 304 | 220
#podcast | 361 | 208
#infosec | 445 | 203
#cybersecurity | 607 | 190
#writing | 348 | 187
#google | 272 | 185
#florida | 508 | 183
#naturephotography | 573 | 182
#dogs | 334 | 181
#flowers | 370 | 180
#idalia | 617 | 180
#covid19 | 467 | 179
#security | 460 | 177
#food | 355 | 176
#photo | 486 | 176
#moon | 228 | 172
#python | 224 | 171
#russia | 2143 | 171
#furryart | 299 | 169
#travel | 1019 | 167
#landscape | 394 | 166
#fensterfreitag | 189 | 159
#startrek | 386 | 159
#shindanmaker | 470 | 158
#wildlife | 411 | 157
#writingcommunity | 386 | 157
#lgbtq | 487 | 155
#usa | 1481 | 154
#starfield | 317 | 153
#china | 401 | 152
#youtube | 234 | 151
#bookstodon | 212 | 149
#apple | 748 | 148
#android | 305 | 147
#scifi | 207 | 144
#uk | 382 | 143
#nowplaying | 1501 | 142
#vegan | 389 | 142
#gop | 414 | 140
#covidisnotover | 301 | 140
#movies | 288 | 140
#ttrpg | 249 | 138
#sunset | 180 | 137
#tech | 1005 | 133
#bluemoon | 186 | 133
#buildasongoutofsongs | 435 | 131
#poetry | 337 | 130
#australia | 334 | 129
#socialmedia | 194 | 126
#fantasy | 187 | 125
#environment | 448 | 124
#rubiales | 201 | 124
#chatgpt | 180 | 124
#trans | 385 | 122
#japan | 477 | 122
#health | 205 | 122
#videogames | 354 | 119
#blackandwhite | 217 | 118
#foss | 162 | 118
#education | 209 | 116