In this tutorial, I want to show you some details using the Steem SQL Database with Metabase.

I already cover the installation of Metabase. If you don't have installed Metabase before, you can follow the instructions here: https://utopian.io/u/23840769

In this tutorial, we will sort all the contributions made to Utopian.io.
At the end of this tutorial, you will be able to fetch data from category (tag) using the Steem SQL. Also, you will be able to filter this data and cross them on your Metabase dashboard.

Step 1: Let's create a new custom question

To get results to the Steem SQL Database, we need to "ask a question" to Metabase. To do that, go to New question and Custom question.
Select the Steem SQL Database

Step 2: Generate our first answer

We are going to sort all the daily upvoted posts by the Utopian Bot from utopian accepted contributions. The first step is to filter all the posts with the tag "utopian-io."

• On "Data," select the Comments table.

The Comments table is a non-transactional table and contains all posts and comments and the related information resulting from activity related to posts on the blockchain (updates, votes, …)
Source: http://steemsql.com/database-diagram/

• Filter: Category is utopian-io
  "Category" is the primary tag of the Post. This option will sort ALL the posts with the category "utopian-io."
• Created after the 14 November
  This option will only fetch the data from the 14 November 2017 to today.
• Grouped by Created: Day
  We want the total amount of data grouped day by day.
• View: Count of rows
  If there is no "Grouped" option selected, this option will calculate the total amount of row. If you have selected a "Grouped by" option, this option will count the total amount of rows for each group.
• Visualization: Bar, Line, ...

Step 3: Filter rules

The actual result only shows ALL the posts made with the primary tag "utopian-io." We need to add some rules to filter the exact contributions that have been upvoted by the utopian bot.
Let's take a look at an upvoted contribution, to see if there is something we can use as a filter.
Every time the bot upvote a contribution, it leaves the same sentence.

We are going to use this knowledge to filter the results that contain the following words: "@utopian-io" and "upvoted."

To do that, in the Filter section, just use "Body" contains "@utopian-io" and "upvoted." This option will only sort the results that include the keywords in the comments and post body.

Generate a new answer.

Step 4: Create a dedicated Dashboard

When you are satisfied with the results, save your question to quickly work with the answers later, name it and add it to a new Dashboard.

Metabase will redirect you to your new Dashboard in Edit mode. You can drag and drop your chart, resize it and some other options that we are going to cover in this tutorial.

When done, click the Save button to save the current modification. Your Dashboard is dynamic, the results of your chart will regenerate each time you visit your Dashboard. You can also explore your data by clicking on a position of your map and then select the " Zoom " option.

Note: You can, at any time, edit your Dashboard by clicking the pencil icon on the top right of your Dashboard.

Step 5: Exercise

Exercise: Can you create a New Question that sort all the Utopian contributions (accepted or not) since the 14 November 2017? Make sure you sort all the answer on a daily basis.

Here are the first steps to help you:

---

ANSWER BELOW - Before continuing to the answer, I encourage you to try to find the solution by yourself.

---

Some users use the tag utopian-io to get more visibility, but they do not send any contribution at all. We need to find a way to only sort real contribution to utopian.io. Like we have done in the previous steps, let's take a look at genuine participation: every time someone contributes to utopian.io, utopian add the following sentence to the end of the post: " Posted on Utopian.io - Rewarding Open Source Contributors."

We are going to use this phrase to filter the results.

Step 6: Cross the Data

Now that we get the answer to our newly created question let's add it to our freshly created Dashboard to compare the Data.

Having two charts side-by-side is not the more natural way to compare results.

With Metabase, you can mix the results of your saved questions to create cross data charts!

Edit your Dashboard by clicking the pencil icon, then select a chart and click the "+Add" icon. Then pick the last question we created and let the magic happen. Don't forget to save your Dashboard!

You now have a full Bar graphic that shows you the difference between the daily contributions on Utopian and the upvoted contributions by the Utopian Bot. Congratulations!

Step 7: Change the type of a saved answer

This is the final Step. We are going to change the data type of one of our question to improve our Dashboard.
We want to change the result of our last question to be a full number counting the total contributions for the past 24 hours.
To do that, click on "Questions" on Metabase. Select your question and change the following options:
Change the filter "Created" to "Hours" and 24. This will sort all the contributions made this past 24 hours.
Undo the grouped option; we want all the sum of the last 24 hours. We don't need to bundle the result anymore.
Generate the new result, Metabase should automatically change the type of data to "Number" and show you the total amount of contributions made the last 24 hours.
Save and Update (or create a new saved question if you want to keep the original one)
Go back to your Dashboard to see the changes.

We are done with this second tutorial. You now have a fully functionnal Dashboard that compare all the contributions made on Utopian with daily Bot Upvote.

You also have a full number that always show you the number of contributions made the last 24 hours.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---