This step by step tutorial will show how to use Microsoft Excel to retrieve data from SteemSQL.com SQL server database and present your result in a simple but nice pivot chart .
Let’s say we want to look how transactions count per month evolves since Steem blockchain has been created.
Writing a SQL query
An efficient SQL query to get such information will be:
SELECT
YEAR(Blocks.timestamp) AS [Year],
MONTH(Blocks.timestamp) AS [Month],
DAY(Blocks.timestamp) AS [Day],
COUNT(*) AS [Transactions]
FROM
Transactions
INNER JOIN Blocks ON Transactions.block_num = Blocks.block_num
GROUP BY
YEAR(Blocks.timestamp),
MONTH(Blocks.timestamp),
DAY(Blocks.timestamp)
As of today, you will get 174 rows like
That's a bunch of rows and hard to visualize data evolution.
Excel to the rescue
Let’s create a nice chart for this with Microsoft Excel
Start Excel, go to the Data tab and select SQL Server as your datasource
Enter the connection information is the wizard dialog box as shown
Select any table (it doesn’t matter at this stage) and click Finish to close the wizard
Click on properties
Select the definition tab
Check the Save password checkbox to avoid Excel asking you for credentials each time you connect to SteemSQL
You can safely ignore the warning as the user and password are public
Open the dropdown control and select SQL
Paste your SQL query and click OK
Select the type of data presentation you want in Excel.
Finalizing the presentation
OK, we got all our data. Let’s make a nice chart.
Organize your data fields in the pivot properties
Tadaa ! You have created your first analytic report using Steem blockchain data!
Support
If you need help, have any comment or request, join steemsql channel on steemit.chat
You like this tutorial, please upvote and follow me for more advanced used of SteemSQL.
I'm trying to use Java for the connection to the database it it wont connect. It says database not responding!
Very cool idea for a service! Having lots of different available data sources & formats like this will speed up cool developments and experimentation.
edited: Saw the other postThis guide is also really well presented and written, thanks for doing this @arcange!
Spent a lot of time to make it as SteemSQL for dummies :P
Thanks for your positive feedback !
It's really a great post. I didn't know that this stuff was doable from excel, and I'll certainly be experimenting.
Reblogged so more folks can see your awesome-level in action.
He he, sharing and spreading knowledge. Good move.
Thanks!
Thanks a lot for the tutorial. I look forward to apply it.
Enjoy :)
wow ,, your writing is very helpful to me, i am very layman with sql server .. thanks. I wait every article from you, hopefully my science in this field is increasing
I am onboard for this. Cool tutorial
Thanks
Leaving a comment since i cant seem to find the resteem button
I am glad I found this. A big thank you to you. I will study it and I hope to be able to use it.
Thank you for these posts @arcange. I am sure your bots will find this message ;) I just started python and sql with a bit of html experience, because of steemit, I want to dig deep into data, and I am trying your codes etc...I am learning while doing. Thanks again.
Glad you have fun digging into data ;)
got my gloves and shovel. Do you know which table.row contains steempower total?
These blogs are well set out and still relevant. Thanks!
Steen Power is not stored in the database because it is a dynamic value that has to be computed/reevaluated each time
Arghhh. Do you know if there is a place i can scrape recent SP from?
Its strange, because my Steem in the Steem Power Wallet is static until i power-up, or collect a reward. Do you know where this number is held if not in the DB?
Thanks for your time!
Hi, top! Question: Can you extract the historical steem_per_mvests? I would use it to make calculations
ありがとうございました。Nice work!!!
I tried to login SteemSQL, but ' ID/PW: steemit ' isn't working, Is it changed??
Check SteemSQL website: https://steemsql.com
This has a negative impact on the server performances and bandwidth and penalize all SteemSQL users.@callmewill, please stop downloading the full contents of tables!
oh, really? sorry for that. I just tried to download a few contents and figured out how can i do this..
Is there a steemsql server on discord?
Support for SteemSQL is provided on https://steem.chat/channel/steemsql