Growth of non-English communities on Steem

Introduction

Steem blogging is not just posts in English. There are many communities here that use a different language. For example, I am actively involved in the #polish community using the @jacekw account. This post is an update of the previous post added 3 months ago.

There are different naming conventions for non-English communities tags:

• country name: #indonesia, #venezuela
• language used, in English: #spanish, #polish
• language used, in the mother tongue: #deutsch
• two-letter code: #kr, #cn

Tools

• SteemSQL
• python 3.7
  • matplotlib
  • jupyter notebook

Queries

Charts were generated using the matplotlib library inpython. The data comes from the SteemSQL database.

Posts:

query = lambda year, month, tag: """\
SELECT COUNT(*)
FROM Comments (NOLOCK)
WHERE depth = 0 AND
    YEAR(created) = {0} AND 
    MONTH(created) = {1} AND 
    (category = '{2}' OR 
        (ISJSON(json_metadata) > 0 AND 
        CONTAINS(json_metadata, '{2}') AND 
        JSON_QUERY(json_metadata, '$.tags') LIKE '%"{2}"%'))
""".format(year, month, tag)

---

Authors

query = lambda year, month, tag: """\
SELECT COUNT(DISTINCT author)
FROM Comments (NOLOCK)
WHERE depth = 0 AND
    YEAR(created) = {0} AND 
    MONTH(created) = {1} AND 
    (category = '{2}' OR 
        (ISJSON(json_metadata) > 0 AND 
        CONTAINS(json_metadata, '{2}') AND 
        JSON_QUERY(json_metadata, '$.tags') LIKE '%"{2}"%'))
""".format(year, month, tag)

---

Author rewards

query = lambda year, month, tag: """\
SELECT SUM(author_rewards) / 1000.0
FROM Comments (NOLOCK)
WHERE depth = 0 AND
    YEAR(created) = {0} AND 
    MONTH(created) = {1} AND 
    (category = '{2}' OR 
        (ISJSON(json_metadata) > 0 AND 
        CONTAINS(json_metadata, '{2}') AND 
        JSON_QUERY(json_metadata, '$.tags') LIKE '%"{2}"%'))
""".format(year, month, tag)

Stats

Posts

.	Tag	Posts
1	spanish	417439
2	kr	367268
3	indonesia	364806
4	aceh	294365
5	deutsch	121544
6	cn	109614
7	venezuela	83652
8	tr	74893
9	polish	49348
10	japanese	31798
11	ru	30380
12	fr	30283
13	pt	29774
14	ita	29426
15	myanmar	27682
16	thai	23747
17	ua	23727
18	steemit-austria	13279
19	morocco	11272
20	pilipinas	10189
21	arab	8862
22	mexico	8121
23	cesky	6838
24	russian	3333
25	br	3197
26	hindi	3102
27	arabic	1960

---

1-10

_{Charts have bad quality on steemit.com, so it's better to look at them on SteemPeak.com or busy.org.}

---

11-20

---

21-30

---

Authors

.	Tag	Authors
1	spanish	74311
2	indonesia	52691
3	aceh	48833
4	kr	37392
5	venezuela	26281
6	deutsch	14754
7	cn	13056
8	tr	9441
9	polish	7881
10	japanese	4805
11	fr	4435
12	myanmar	3905
13	ita	3843
14	pt	3216
15	ru	3153
16	mexico	3035
17	thai	3012
18	steemit-austria	1799
19	arab	1470
20	morocco	1458
21	ua	1452
22	pilipinas	1235
23	russian	1034
24	hindi	1020
25	cesky	669
26	arabic	563
27	br	545

---

1-10

---

11-20

---

21-30

---

Author rewards

.	Tag	Author rewards
1	kr	702170
2	spanish	399950
3	cn	293115
4	deutsch	204453
5	indonesia	172147
6	tr	127160
7	ita	109700
8	japanese	77073
9	aceh	75150
10	fr	73977
11	venezuela	55576
12	pt	55359
13	polish	50173
14	steemit-austria	36973
15	myanmar	31972
16	ru	31596
17	ua	26784
18	thai	24245
19	mexico	17615
20	br	17322
21	morocco	14190
22	arab	11157
23	cesky	8833
24	pilipinas	5601
25	hindi	4084
26	arabic	2486
27	russian	1990

1-10

---

11-20

---

21-30

---

Average author rewards

.	Tag	Average author rewards per post
1	br	5.418
2	ita	3.728
3	steemit-austria	2.784
4	cn	2.674
5	fr	2.443
6	japanese	2.424
7	mexico	2.169
8	kr	1.912
9	pt	1.859
10	tr	1.698
11	deutsch	1.682
12	hindi	1.316
13	cesky	1.292
14	arabic	1.268
15	arab	1.259
16	morocco	1.259
17	myanmar	1.155
18	ua	1.129
19	ru	1.040
20	thai	1.021
21	polish	1.017
22	spanish	0.958
23	venezuela	0.664
24	russian	0.597
25	pilipinas	0.550
26	indonesia	0.472
27	aceh	0.255

1-10

---

11-20

Something interesting happening here :)

---

21-30

Nice progress #venezuela!

---

Conclusions

In the analysis, 27 non-English communities, were taken into account . There are probably more, but the process of finding them is not a simple one, because of the different tagging conventions that have been adopted. All these communities are probably waiting for the appearance of the functionality Communities, because this will make it easier for them to function.

If you know a non-English community that was not included here please let me know.