Getting simple signup data from the Steem blockchain

Following on from my previous post Querying Steem blockchain data - Getting started, this post will take another look at SteemSQL, but now we play with data!
As my previous post covered installing and getting connected with a MSSQL client, this post assumes you have already done that.

If you are reading this post, chances are that you have an account or at the very least an interest in Steem and or Steemit.com, so why not start with accounts?
As a firm believer in iterative learning I am going to start small, then we can build on that knowledge as we go, so for this post we will cover a small portion of account data, along with a very simple use case, but one that you will be able to follow along with.

Let's get started

Would you believe it, account data is in the Accounts table! You can see it selected above in the Schema Editor tab in my MSSQL client.

Scroll a bit to the right and you get to see how many fields there are!

So let's take it easy and focus on only a few fields, here is a list of important ones, and what they are used for;

• name the username on the Steem blockchain, I signed up as shaunmza so thats whats in my name field
• owner, active, posting and memo are the public keys for different aspects on your account, you can verify these by looking at your wallet > permissions
• created the date this account was created
• mined this will be important for our exercise, 0 means that the user signed up through Steemit.com, whereas 1 means that the account was created for mining Steem
• post_count the number of posts this user has made, this includes all comments, replies etc.
• voting_power the voting power of the user, divide by 100 to get to a percentage i.e. 8308 / 100 = 83.08%
  and more, but most are irrelevant for the exercise that we are going to do.

Running queries

Would you like to see you information?
Run the following simple query;

SELECT * FROM Accounts WHERE name = 'shaunmza';

Just remember to replace my username shaunmza with yours!
TIP

The query above means SELECT (get me something) * (all the fields) FROM Accounts (from the Accounts table) WHERE (filter the records with ...) name = 'shaunmza' (the field name, only bring back where it matches shaunmza exactly)

Now that we have a basic understanding of the data held in the Accounts table, let's do a small exercise. Let's say we wanted to see how many people signed up in February, what query would we run to get that data?

SELECT COUNT(*) FROM Accounts WHERE CONVERT(DATE, created) >= '2017-02-01';

I get the very exciting number 3507 which will have changed by the time you read this. It's exciting (sarcasm) because it means nothing really. Just one number.
Let's try see how many people signed up in EACH DAY of February 2017!

SELECT DAY(created) AS 'date_created', COUNT(*) AS 'signups' FROM Accounts 
WHERE CONVERT(DATE, created) >= '2017-02-01'
GROUP BY DAY(created);

Aha! Now we have a table of numbers! I feel so much better.

So we altered our query a touch,

• added another field DAY(created) AS 'date_created' which is simply the call to a function to extract the 'day' from a date field
• grouped by the day the account was created, GROUP BY is basically saying 'group all the records where this value is the same together'
  The GROUP BY separated the days for us, and the COUNT(*) add them all up.

But wait!

Now would you believe me if I told you this was not a true reflection of Steemit users?
Would you? No?
Remember that little field I mentioned earlier mined? Well it is important if you want to see only the people who actually signed up manually for an account! Let's see how big of a difference it makes, if at all!

We change our query to;

SELECT DAY(created) AS 'date_created', COUNT(*) AS 'signups' FROM Accounts 
WHERE mined = 0
AND CONVERT(DATE, created) >= '2017-02-01'
GROUP BY DAY(created);

There is a noticeable difference!

On the first alone, we have 184 vs 162, which probably means that 22 of the accounts created on the 1st of February 2017 are purely for mining.
So anytime you write a query that makes use of the Accounts table, be sure to question what type of accounts you are expecting back!

Fin

I think thats all for now, in my next post I will cover fetching more data, grouping it differently and then finally throwing it into a graph, so we can see lines and stuff!

You can help me out a bunch by letting me know what you think! Please leave a comment any with feedback, including mistakes. Thanks!