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.