How to use Google Sheets with SteemSQL

in #utopian-io7 years ago (edited)

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 ?
  1. 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
  2. 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.
  3. You don't need to install anything, full accessibility thru your desktop / mobile / pads
  4. 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
Screen Shot 2017-12-20 at 5.18.45 PM.png
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
Screen Shot 2017-12-20 at 5.19.36 PM.png


Screen Shot 2017-12-20 at 5.42.09 PM.png


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.

Screen Shot 2017-12-20 at 5.21.34 PM.png

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.

Screen Shot 2017-12-20 at 6.02.07 PM.png

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

authorPostsVotesPending_PayoutComments
arcjen0221.00170124.2438
omeratagun20.00310442.9179
irfandogan20.00285392.3646
nuges20.00604272.17100
realinfo19.00220383.8182
kalvas18.00376480.8969
omersurer17.00422346.5666
myjourney16.00174308.5531
ranielbrianulan16.00308224.9063
by-yesilbag15.00293330.6476
drigweeu15.00189182.0163
h4ck3rm1k3st33m158683.60441
flauwy14.00813551.24219
aymenz14.00154190.5371
kizilelma13.00150314.1931
guinsoo13.00184310.4042
redjepi13.00154297.3428
raptorjesus13.00107273.5236
anwei13.00121271.0635
anggaariska13.00204220.5636
ihtiht13.0099196.3434
mikekenlytungal13.00131131.2138
holabisi13.00155121.6260
andravasko13.00131104.5923
sametceylan12.00175229.9430
kwonn12.00169227.1047
gilangarif13129412.0085119.6027
olaivart1214934.40690
monomyth11.00333276.0335

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




Sort:  

Very informative ! So I resteemed :)

nice one.... this is how they extract the data....tq

educational knowledg

Superbly useful!

Screen Shot 2017-12-20 at 8.22.26 PM.png

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 😆

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

Verry essential knowledge Which is beneficial for us.
thanks for sharing @bitrocker2020