How to use Google Sheets with SteemSQL
Here is a quick and simple tutorial of using Google Sheets with SteemSQL
A sample query included thanks to @carlgnash for his help with the query.
Why Google Sheets ?
- Everyone who has a Gmail account will have access to Google Sheets. How to access it is pretty simple, just head to http://drive.google.com and from there you login your Gmail account and you can create a new spreadsheet
- You can work in a collaboration mode where someone who is really good at writing a SQL query can execute it and then is able to share the results thru the spreadsheet with anyone they wish to.
- You don't need to install anything, full accessibility thru your desktop / mobile / pads
- Google Drive is FREE up to 15GB of usage
Getting started !
First you would need to open up a spreadsheet from http://drive.google.com then from there you need to click Add-Ons --> Get Add-Ons then search for an add-on called SeekWell
this add-on is not free and has a fee of only USD19 per month. 14 days trial period You would need to purchase the license as we are using SteemSQL which is a Microsoft SQL Server and not MySQL
Once you've added the add-on. You can now click Add-Ons --> SeekWell --> Launch
You would now be required to put in the credentials of SteemSQL
- Select SQL Server
- Hostname : sql.steemsql.com:1433
- Database : DBSteem
- Username : steemit
- Password : steemit
Thanks to @arcange who has kept SteemSQL consistently up and updated for us to be able to use for analytical purposes.
Once you are logged in you can now start to create your query.
Lets start with this scenario. We want to do a quick analysis of who has the most pending payouts over the past few days on the tag utopian-io . So copy and paste this query on the query area
select
author,
count(author) as Posts,
sum(net_votes) as Votes,
sum(pending_payout_value) as Pending_Payout,
sum(children) as Comments
from
Comments (NOLOCK)
where
dirty = 'False' and
json_metadata LIKE('%"utopian-io"%') and
parent_author = '' and
datediff(day, created, GETDATE()) between 0 and 7
group by
author
order by
Pending_Payout desc
You can then click run and it would run the query and display the results in a either the active sheet or a new sheet whichever you select.
Your results should look something similar to this.
So now once you've pulled in the data into the spreadsheet you can do whatever you want with it for your analytical purposes. For example thru this query here are the static from what was found for people using the utopian-io tags over the past 7 days
No. of Authors : 671
No. of Posts made : 2,256
Total of votes received on all posts : 43,906
Total no. of comment : 10,873
Total pending payout : $40,229.05
Here are the top 30 authors with the most number of posts over the past 7 days using the tag utopian-io
author | Posts | Votes | Pending_Payout | Comments |
---|---|---|---|---|
arcjen02 | 21.00 | 170 | 124.24 | 38 |
omeratagun | 20.00 | 310 | 442.91 | 79 |
irfandogan | 20.00 | 285 | 392.36 | 46 |
nuges | 20.00 | 604 | 272.17 | 100 |
realinfo | 19.00 | 220 | 383.81 | 82 |
kalvas | 18.00 | 376 | 480.89 | 69 |
omersurer | 17.00 | 422 | 346.56 | 66 |
myjourney | 16.00 | 174 | 308.55 | 31 |
ranielbrianulan | 16.00 | 308 | 224.90 | 63 |
by-yesilbag | 15.00 | 293 | 330.64 | 76 |
drigweeu | 15.00 | 189 | 182.01 | 63 |
h4ck3rm1k3st33m | 15 | 86 | 83.604 | 41 |
flauwy | 14.00 | 813 | 551.24 | 219 |
aymenz | 14.00 | 154 | 190.53 | 71 |
kizilelma | 13.00 | 150 | 314.19 | 31 |
guinsoo | 13.00 | 184 | 310.40 | 42 |
redjepi | 13.00 | 154 | 297.34 | 28 |
raptorjesus | 13.00 | 107 | 273.52 | 36 |
anwei | 13.00 | 121 | 271.06 | 35 |
anggaariska | 13.00 | 204 | 220.56 | 36 |
ihtiht | 13.00 | 99 | 196.34 | 34 |
mikekenlytungal | 13.00 | 131 | 131.21 | 38 |
holabisi | 13.00 | 155 | 121.62 | 60 |
andravasko | 13.00 | 131 | 104.59 | 23 |
sametceylan | 12.00 | 175 | 229.94 | 30 |
kwonn | 12.00 | 169 | 227.10 | 47 |
gilangarif131294 | 12.00 | 85 | 119.60 | 27 |
olaivart | 12 | 149 | 34.406 | 90 |
monomyth | 11.00 | 333 | 276.03 | 35 |
I do hope that this quick tutorial was useful for you and especially for those who are curating certain tags and would like to do analysis and then share the results within the community. Cheers and have a great week ahead
Posted on Utopian.io - Rewarding Open Source Contributors
Very informative ! So I resteemed :)
ty :)
nice one.... this is how they extract the data....tq
educational knowledg
Superbly useful!
Just Setup on my Desktop with JetBrains DataGrip. Thanks for the articles.
Never use MSSQL before, but is a great chance to test out haha 😆
nice !!
The tutorial is indeed simple... I'm just a hammer head. Either way, I came here to thank you for your contribution as people like you... who are not hammerheads like me... is what keeps the community going.
Cheers
Great tutorial for a starter like me. hehe
we all start somewhere :)
yup, thanks boss!
Wow, that's something needed for me to take note. Google now has seems to be overtaking everything on the internet. I once heard that the rich get richer while the poor will be bullied to the ground. Seems like Google is expanding with all their capacity to permanent their status globally. Btw, thanks for sharing! #teammalaysia
oh yea .. they've updated google drive with a lot of new features and its still free !! likw whaaatt ?
Exactly what I am predicting, soon they will overtake Microsoft. Just the matter of time..
They have already beaten out Microsoft in the cloud services sector. As for mobile phone OS, Google wins. Apps store, Google wins. So far the only market Google hasn't dared to touch is the professional media creation toolset market. Imagine one day Google overtakes Adobe. (Google has already begun working on improving their photos service )
its just the matter of time before Google Ventures buys a competitor and just sexyfy the entire thing
Yeah,,,Great. very valuable post.
Thank you so much for your important post
cheers @rahulsen
Yeah,,,Cheers brother :) :) @bitrocker2020
Verry essential knowledge Which is beneficial for us.
thanks for sharing @bitrocker2020