Do you know how many articles have you posted on steemit.com? Would you like to find out? Would you like to get the list of them, complete with titles and links?

This is completely doable even for a non-programmer. All you need is a bit of excel magic and SteemSQL.

Introducing SteemSQL

SteemSQL is

A public SQL database with all blockchain data

created and maintained by @arcange.

Check my Steem Data Resources - Collection Of Posts About Steem Bots, Data And Mining, Issue No. 4 for more.

Check these three resources which will show you how to connect to it:

1. [STEEMSQL.COM] A public SQL server database with all Steemit blockchain data by @arcange
2. [STEEMSQL.COM] How to create a Steem analytic report with Microsoft Excel by @arcange
3. My First STEEMSQL.COM Query! by @bbrewer

Getting Data From SteemSQL Database

Since SteemSQL is an MS SQL Database Server the most natural way of obtaining the data from it is through MS tools. Excel, for example.

Yes, you can use any tool capable of ODBC connection.

This short tutorial will show you how to do it in excel.

Simply open new excel spreadsheet and create a connection as is beautifully explained in My First STEEMSQL.COM Query! by @bbrewer.

The Select Sentence

The aim is to get a list of all my articles which I posted so far.

Due to the peculiarities of Steem blockchain and SteemSQL structure I do have to be careful and consider several things.

The select sentence that I am using is:

select a.* 
from TxComments a
inner join 
    (select permlink,
         min(tx_id) as tx_id
     from TxComments 
     where author = 'ervin-lemark' and title <> ''
     group by permlink
    ) as b
on a.tx_id = b.tx_id and
    a.permlink = b.permlink
order by a.tx_id desc

The explanation

1. Inner select is filtered by author = 'ervin-lemark' to get only the posts which I authored
2. Inner select is filtered by title <> '' to get only root posts - comments which are posts too, if you ask Steem blockchain, don't have titles
3. inner select is grouped by permlink and returns only the first occurence (min(tx_id)) of a post because later edits of a post have the same permlink and title
4. outer select is then joined with inner to return only the records which are filtered through the rules above

I do hope that the explanation and the select used makes sense to you. If not - ask, please!

The Result

268 articles by @ervin-lemark so far. Compared to the total number of posts, which can be seen on my profile page which is 2577 now it means that I've commented (2577 - 268) 2309 times. Quite a lot.

Here is a screenshot of first fifty-something records. Click on it to see it full size.

I hid some columns in the spreadsheet above to make it readable. Body and json-medatadata fields are hidden, for example.

What I did add after the select gathered the data was a article_url column with the following excel formula:

=CONCATENATE("https://steemit.com/";[@[parent_permlink]];"/@";[@author];"/";[@permlink])

This way you can compose the working url link to the post. Neat, isn't it?

A formula for the complete link with a markdown code would be:

=CONCATENATE("[";[@title];"](";[@[article_url]];") by @";[@author])

Resulting in, for example:

[Steem Data Resources - Collection Of Posts About Steem Bots, Data And Mining, Issue No. 4](https://steemit.com/steem/@ervin-lemark/steem-data-resources-collection-of-posts-about-steem-bots-data-and-mining-issue-no-4) by @ervin-lemark

Which is rendered as:

Steem Data Resources - Collection Of Posts About Steem Bots, Data And Mining, Issue No. 4 by @ervin-lemark

Store And Ask Again

Since this solution is boxed inside excel I can save the file and open it at any time to get the most current data.

Create once and use many times is the mantra of programmers :)

Issues And Future Steps

I would very much like to get the timestamp of the posts too. I guess that SteemSQL currently doesn't display this info. Am I wrong?

I could remember latest tx_id and reread the data from this point onwards. But the select sentence as it is now, reading all the data, is quite fast. Congrats to @arcange for setting up a great server.

The body and the representation of it is another story all together. I think that I'll turn to steemjs for this challenge.

Your Thoughts?

I would very much like to hear your feedback. Thank you!

---

Better and better!