Bitcoin Forum
September 24, 2018, 04:32:10 AM *
News: ♦♦ New info! Bitcoin Core users absolutely must upgrade to previously-announced 0.16.3 [Torrent]. All Bitcoin users should temporarily trust confirmations slightly less. More info.
 
   Home   Help Search Donate Login Register  
Pages: [1] 2 3 »  All
  Print  
Author Topic: Tool to run online SQL queries over Full Merit Data  (Read 760 times)
Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
June 27, 2018, 07:31:57 AM
Merited by Vod (16), redsn0w (15), Foxpup (9), TMAN (9), mprep (8), suchmoon (5), Welsh (5), Lakai01 (3), fronti (2), qwk (2), Joel_Jantsen (2), Jet Cash (2), DdmrDdmr (2), theyoungmillionaire (2), babo (1), krishnapramod (1), LoyceV (1), bL4nkcode (1), LeGaulois (1), Coin-1 (1), iasenko (1), coinlocket$ (1), spirali (1)
 #1

UPDATES:
This is a tool that allow you to run full SQL queries over the merit data and is based on SQLite. If you are familiar with SQL you know the possibilities are basically endless or limited to your SQL knowledge.

My previous similar tool (here) is perhaps more user friendly, but has some limitation: speed and research flexibility. So this is why i made this.

You can find more information about SQL syntax in here if you are interested to learn, i may also give a hand if somebody want to get out some particular information out, even though my SQL skills are a bit rusty.

Table structure:

Code:
MeritData(
  "Date" TEXT,
  "Merit" Integer,
  "Msg" TEXT,
  "FromID" Integer,
  "ToID" Integer,
  "Board" TEXT,
  "SubBoard" TEXT,
  "TitleThread" TEXT
)

UserData(
   UserId Integer PRIMARY KEY,
   UserName TEXT,
   Rank TEXT,
   Trust TEXT,
   Location TEXT
)

How to use it:

   Just type the query in the box and press Exceute, F5 or Ctrl-Enter to execute it.

Link:

https://albertoit.github.io/Merit-Explorer-SQL/





For those of you who are not familiar with SQL, beside the fact that you will get any result extremely fast, this is what it means:[/b]

We can easily get the Top Receiver for any local section simply using this:

Code:
SELECT MAX(result.total) as "Total Merit", result.toid as "Top merit receiver", result.SubBoard as "Local board" FROM (
SELECT toid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.Board in (SELECT Board FROM meritdata Where Board like "Local%" GROUP BY Board)
GROUP BY toid, Board
ORDER BY Board,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc

or for any board:

Code:
SELECT MAX(result.total) as "Total Merit", result.toID as "Top merit receiver", result.SubBoard as "Board" FROM (
SELECT toid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.SubBoard in (SELECT SubBoard FROM meritdata GROUP BY SubBoard)
GROUP BY toid, SubBoard
ORDER BY SubBoard,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc;

Similarly we can find out the Top Giver:

Code:
SELECT MAX(result.total) as "Total Merit", result.fromid as "Top merit giver", result.SubBoard as "Local board" FROM (
SELECT fromid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.Board in (SELECT Board FROM meritdata Where Board like "Local%" GROUP BY Board)
GROUP BY fromid, Board
ORDER BY Board,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc;

or for any any board:

Code:
SELECT MAX(result.total) as "Total Merit", result.fromID as "Top merit giver", result.SubBoard as "Board" FROM (
SELECT fromid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.SubBoard in (SELECT SubBoard FROM meritdata GROUP BY SubBoard)
GROUP BY fromID, SubBoard
ORDER BY SubBoard,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc;

Find out the total merit awarded so far:

Code:
SELECT Sum(Merit) FROM MeritData;

Want the full history for a particular user?

Code:
SELECT * FROM MeritData WHERE toID=35 OR fromID=35 ORDER BY fromid,toid;


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
1537763530
Hero Member
*
Offline Offline

Posts: 1537763530

View Profile Personal Message (Offline)

Ignore
1537763530
Reply with quote  #2

1537763530
Report to moderator
1537763530
Hero Member
*
Offline Offline

Posts: 1537763530

View Profile Personal Message (Offline)

Ignore
1537763530
Reply with quote  #2

1537763530
Report to moderator
1537763530
Hero Member
*
Offline Offline

Posts: 1537763530

View Profile Personal Message (Offline)

Ignore
1537763530
Reply with quote  #2

1537763530
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1537763530
Hero Member
*
Offline Offline

Posts: 1537763530

View Profile Personal Message (Offline)

Ignore
1537763530
Reply with quote  #2

1537763530
Report to moderator
1537763530
Hero Member
*
Offline Offline

Posts: 1537763530

View Profile Personal Message (Offline)

Ignore
1537763530
Reply with quote  #2

1537763530
Report to moderator
1537763530
Hero Member
*
Offline Offline

Posts: 1537763530

View Profile Personal Message (Offline)

Ignore
1537763530
Reply with quote  #2

1537763530
Report to moderator
mdayonliner
Sr. Member
****
Offline Offline

Activity: 294
Merit: 274


Over 13BTC http://bit.ly/BTCLoan


View Profile
June 27, 2018, 07:52:06 AM
 #2

I was interested in SELECT all query.
Code:
SELECT * FROM MeritData;

And...
This things are getting really interesting for me. May be I want to start learning how to scrape data  Smiley

Good job mate.

PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
DdmrDdmr
Sr. Member
****
Offline Offline

Activity: 252
Merit: 980

There are lies, damned lies and statistics. MTwain


View Profile
June 27, 2018, 08:03:13 AM
 #3

Nicely done there Piggy, i'm sure you'll build on it furthermore. Perhaps you could transform de unix date field into common date field for viewers who cannot do it themselves.
lukeburchill
Member
**
Offline Offline

Activity: 82
Merit: 17


View Profile
June 27, 2018, 08:12:57 AM
 #4

Table structure:

Code:
MeritData(
  "Date" Float,
  "Merit" Integer,
  "Msg" TEXT,
  "FromID" Float,
  "ToID" Float,
  "Board" TEXT,
  "SubBoard" TEXT
)
I have been creating your script in XAMPP, but have errors then I add a bit script "create table" and removes the symbol (""). and done created a table in DB

Code:
create table MeritData(
  Date Float,
  Merit Integer,
  Msg TEXT,
  FromID Float,
  ToID Float,
  Board TEXT,
  SubBoard TEXT
)




babo
Legendary
*
Offline Offline

Activity: 1554
Merit: 1063


View Profile WWW
June 27, 2018, 08:21:33 AM
 #5

if you didnt block some operations, do it fast
block

- INSERT
- UPDATE
- DELETE

for avoid trolls and vandals


 
                                                       
                                                         
     ███████████   █████████   ████████     ████████   
        ████         ████        ██▌          ██▌       
         ████         ████      ██▌          ██▌       
          ████         ████    ██▌          ██▌         
           ████         ████  ██▌          ██▌         
            ████         ██████▌          ██▌           
             ████         ████▌          ██▌           
              ████         ████         ██▌             
               ████       ██████       ██▌             
                ████     ██▌ ████     ██▌               
                 ████   ██▌   ████   ██▌               
                  ████ ██▌     ████ ██▌                 
                   █████▌       █████▌                 
                    ███▌         ███▌                   
                     █▌           █▌                   
                                                         
                                                         
  crypto-coins.xyz     
.Best .Wiki. about the Bitcoin World.
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
.Crypto-coins.xyz.
Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
June 27, 2018, 08:34:31 AM
 #6

All the changes are "local", the database cannot be modified really as is hosted in Github and there are no write permissions in there. If you make changes and then refresh the page the original data will be back.

Nicely done there Piggy, i'm sure you'll build on it furthermore. Perhaps you could transform de unix date field into common date field for viewers who cannot do it themselves.

Yes can be further improved for sure.  I'll improve the data quality so can be used easily and more effectively.


Code:
create table MeritData(
  Date Float,
  Merit Integer,
  Msg TEXT,
  FromID Float,
  ToID Float,
  Board TEXT,
  SubBoard TEXT
)

Yes, that above was just to show the field in the table




I was interested in SELECT all query.
Code:
SELECT * FROM MeritData;

And...
This things are getting really interesting for me. May be I want to start learning how to scrape data  Smiley

Good job mate.

PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)

Not much can be done there, prefer to keeping it "unchecked", everything run on local so no harm is done.


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
babo
Legendary
*
Offline Offline

Activity: 1554
Merit: 1063


View Profile WWW
June 27, 2018, 11:58:43 AM
 #7

little advice
use a different repo (and not one repo for all)

i sent you a pm


 
                                                       
                                                         
     ███████████   █████████   ████████     ████████   
        ████         ████        ██▌          ██▌       
         ████         ████      ██▌          ██▌       
          ████         ████    ██▌          ██▌         
           ████         ████  ██▌          ██▌         
            ████         ██████▌          ██▌           
             ████         ████▌          ██▌           
              ████         ████         ██▌             
               ████       ██████       ██▌             
                ████     ██▌ ████     ██▌               
                 ████   ██▌   ████   ██▌               
                  ████ ██▌     ████ ██▌                 
                   █████▌       █████▌                 
                    ███▌         ███▌                   
                     █▌           █▌                   
                                                         
                                                         
  crypto-coins.xyz     
.Best .Wiki. about the Bitcoin World.
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
.Crypto-coins.xyz.
funsponge
Hero Member
*****
Offline Offline

Activity: 728
Merit: 522



View Profile
June 27, 2018, 05:06:25 PM
 #8

This could spit out some interesting data what about gathering a certain rank like "hero members" and spitting out the usernames of the ones which have received at least 5 merits what sort of command would need to be put in? 

░░░░░░░▄▄▄▄▄▄
░░░░▄██████████▄
░░░██████████████
░░██████▐▌██████
█████░░░░░░░▀█████
██████▄▄░░▄▄░░██████
████████░░▀▀▄██████
████████░░▄▄▄░░█████
██████▀▀░░▀▀▀░░█████
█████░░░░░░░░█████
░░██████▐▌██████
░░░██████████████
░░░░▀██████████▀
░░░░░░░▀▀▀▀▀▀
░░░

                   BitCloak Bitcoin Mixer  
  BTC & BCH | API| MULTIADDRESS| PGP PROOF|  FAST MIX |  ESCROW|  MORE ! 

░░░░░░░▄▄▄▄▄▄
░░░░▄██████████▄
░░░██████████████
░░██████▐▌██████
█████░░░░░░░▀█████
██████▄▄░░▄▄░░██████
████████░░▀▀▄██████
████████░░▄▄▄░░█████
██████▀▀░░▀▀▀░░█████
█████░░░░░░░░█████
░░██████▐▌██████
░░░██████████████
░░░░▀██████████▀
░░░░░░░▀▀▀▀▀▀
░░░

jackg
Copper Member
Legendary
*
Offline Offline

Activity: 1134
Merit: 1104


View Profile
June 27, 2018, 06:01:14 PM
 #9

if you didnt block some operations, do it fast
block

- INSERT
- UPDATE
- DELETE

for avoid trolls and vandals

I mean, considering vod's database previously got hacked, I'd be surprised if those commands weren't already blocked.
I was interested in SELECT all query.
Code:
SELECT * FROM MeritData;

And...
This things are getting really interesting for me. May be I want to start learning how to scrape data  Smiley

Good job mate.

PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
I'd suggest against doing this unless you want a copy of the database.
Unless all commands run with more than a few seconds/minutes lag then don't do anything op.

Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
June 27, 2018, 06:03:27 PM
 #10

This could spit out some interesting data what about gathering a certain rank like "hero members" and spitting out the usernames of the ones which have received at least 5 merits what sort of command would need to be put in?  

I will have a look tomorrow and add the rank information per user in another table, perhaps LoyceV has already it available, in that case would be quite fast to include that in.

After that should not be a problem giving an answer to that question.

At the moment since there is no rank you can run this for all the users:

Link because the forum is blocking sql in the message:
https://pastebin.com/3dqMdeK0


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
funsponge
Hero Member
*****
Offline Offline

Activity: 728
Merit: 522



View Profile
June 27, 2018, 06:32:15 PM
 #11

This could spit out some interesting data what about gathering a certain rank like "hero members" and spitting out the usernames of the ones which have received at least 5 merits what sort of command would need to be put in?  

I will have a look tomorrow and add the rank information per user in another table, perhaps LoyceV has already it available, in that case would be quite fast to include that in.

After that should not be a problem giving an answer to that question.

At the moment since there is no rank you can run this for all the users:

Link because the forum is blocking sql in the message:
https://pastebin.com/3dqMdeK0
Nice one I just tried the tool and its incredibly fast so good work on that. Keep on bringing us these sort of things please as I'm sure everyone appreciates these data dumps and tools.

░░░░░░░▄▄▄▄▄▄
░░░░▄██████████▄
░░░██████████████
░░██████▐▌██████
█████░░░░░░░▀█████
██████▄▄░░▄▄░░██████
████████░░▀▀▄██████
████████░░▄▄▄░░█████
██████▀▀░░▀▀▀░░█████
█████░░░░░░░░█████
░░██████▐▌██████
░░░██████████████
░░░░▀██████████▀
░░░░░░░▀▀▀▀▀▀
░░░

                   BitCloak Bitcoin Mixer  
  BTC & BCH | API| MULTIADDRESS| PGP PROOF|  FAST MIX |  ESCROW|  MORE ! 

░░░░░░░▄▄▄▄▄▄
░░░░▄██████████▄
░░░██████████████
░░██████▐▌██████
█████░░░░░░░▀█████
██████▄▄░░▄▄░░██████
████████░░▀▀▄██████
████████░░▄▄▄░░█████
██████▀▀░░▀▀▀░░█████
█████░░░░░░░░█████
░░██████▐▌██████
░░░██████████████
░░░░▀██████████▀
░░░░░░░▀▀▀▀▀▀
░░░

mdayonliner
Sr. Member
****
Offline Offline

Activity: 294
Merit: 274


Over 13BTC http://bit.ly/BTCLoan


View Profile
June 27, 2018, 11:38:48 PM
 #12

I'd suggest against doing this unless you want a copy of the database.
I actually wanted to see all data of the table  Tongue


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
June 28, 2018, 10:31:45 AM
 #13

One thing i forgot to add, since the output is shown on a web page, is possible to give html formatting to it, this means we can add direct links for the users profile page and to the post merited, here is an example:

Code:
SELECT
date,
'<a href=https://bitcointalk.org/index.php?action=profile;u=' || fromid || '>' || fromid || '</a>' as Sender,
'<a href=https://bitcointalk.org/index.php?action=profile;u=' || toid || '>' || toid || '</a>' as Receiver,
merit,
'<a href=https://bitcointalk.org/index.php?topic=' || msg || '#' || substr(msg,instr(msg,'.')+1) || '>Link Merited post</a>' as MeritedPost
FROM meritdata
WHERE merit >= 50
LIMIT 10;


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
June 29, 2018, 11:20:24 AM
Merited by babo (1)
 #14

Updated the data in general (29/06/2018):

New link: https://albertoit.github.io/Merit-Explorer-SQL/

Added User data: 23147 users, with name and rank, that have either sent or received any merits

Table:

Code:
TABLE UserData(
UserId Integer PRIMARY KEY,
UserName TEXT,
Rank TEXT
);

Updated the merit data table, added readable format date and Title of the thread where the comment was merited

Code:
TABLE MeritData(
  "Date" TEXT,
  "Merit" Integer,
  "Msg" TEXT,
  "FromID" Integer,
  "ToID" Integer,
  "Board" TEXT,
  "SubBoard" TEXT,
  "TitleThread" TEXT
)

Few new example based on the update in this link (forum preventing posting SQL for security reason):

https://pastebin.com/PRrQ81Ld

  • Show the user information instead of the user id:
  • Selecting data by a particular time frame is quite easy (all merit transaction happened the 1/06/2018):
  • Select by a time frame (from 2018 to 15/06/2018):
  • You can also use hour minutes (and seconds) to check a 30 minutes interval:
  • How many merits sent for each rank?
  • How may merits sent from one rank to another?




       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
iasenko
Sr. Member
****
Offline Offline

Activity: 322
Merit: 500


Vod's BTT Public Information Project - bpip.org


View Profile WWW
June 29, 2018, 02:36:29 PM
 #15

I can't wait to get my PC back online to play a little with your database.
I just want to list all the users with 9 , 99, 249,... etc. merit and some other fun stats.
 

Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
June 29, 2018, 06:37:25 PM
 #16

I can't wait to get my PC back online to play a little with your database.
I just want to list all the users with 9 , 99, 249,... etc. merit and some other fun stats.
 

Yes that is the idea, if somebody has some particular method or theory to show something interesting within the data, it can be done quite easily, your imagination is the limit Smiley

Just want to remind, If anybody need some help with the SQL i can advise.


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
Makkara
Member
**
Offline Offline

Activity: 350
Merit: 79


View Profile
June 30, 2018, 06:53:53 PM
 #17

Very nice work.  Cool

One suggestion: Why not at this point adding the text of the comment merited itself, that could be good info as could also be used to search for particular words in the message itself. Offcourse could make the db much heavier, but peraphs is worth investigating and make some test?

Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
July 01, 2018, 02:07:11 PM
 #18

I should make some calculation on paper, but i believe the database would become quite large and the main problem i see is the waiting time to download it. Otherwise there should not be big problems speed wise, if the tables will be properly indexed.

What im planning to do next is some sort of scripting system ( that would be the equivalent of store procedures, which are missing in SQLite ), nothing very sophisticated, just some injecting of javascript where you can define the sql, execute it and manipulate the results to get the information you want in some complex case.


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
July 06, 2018, 07:44:31 AM
 #19

Updated with the latest data from today (6th of July), added Trust and Location fields to the user table:

Code:
UserData(
   UserId Integer PRIMARY KEY,
   UserName TEXT,
   Rank TEXT,
   Trust TEXT,
   Location TEXT
)


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
Piggy
Hero Member
*****
Offline Offline

Activity: 490
Merit: 808



View Profile WWW
July 08, 2018, 05:35:20 AM
 #20

Im working on the scripting system at the moment and it should be ready by the next week, with it is going to be easier to extract data without comin up with very long sql queries, like this one i'm about to show you now.



I was actually looking into an interesting question, what is the minimum amount of smerits generated just by sending them to somebody and still unspent ?

We need for each user to calculate the merits received, half that minus the merit sent. We are going to disregard remaining airdropped smerits or smerits given to merit sources. This is why i refer to it as minimum amount of smerits unspent, in reality is higher but this still can be a good indicator for future sustainability.

Code:
Select sum(res) FROM (
SELECT fromid, table2.received, Sum(MERIT) as given, (received/2 - Sum(MERIT)) as res
FROM MeritData
INNER JOIN
(
SELECT toid, Sum(MERIT) as received
FROM MeritData
WHERE toid in (SELECT userid FROM userdata  )
GROUP BY toid
) as table2 ON table2.toid = fromid
WHERE fromID in (SELECT userid FROM userdata )
GROUP BY fromid
Having received/2 - Sum(MERIT) >=0)

Btw the answer is 11846 This Sql above doesn't work as intended unfortunately, must have got something wrong  Cry


       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
   
       █
      ██
     ██
   ██ ██
 █ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
██ ██ ██
  B

          ▄▄▄▄▄▄
     ▄▄████████████▄▄
   ▄█████▀▀    ▀▀█████▄
  ████▀            ▀████
 ████                ████
▐███                  ███▌
███▌                  ▐███
▐███           ▄▄     ███▌
 ████         ▀███▄  ▐███
  ████▄         ▀███▄███
   ▀█████▄▄     ▄█████▀
     ▀▀████████████▀▀
          ▀▀▀▀▀▀
T 
Better. Quick.

Transparent.






             ▄████▄▄   ▄
█▄          ██████████▀▄
███        ███████████▀
▐████▄     ██████████▌
▄▄██████▄▄▄▄█████████▌
▀████████████████████
  ▀█████████████████
  ▄▄███████████████
   ▀█████████████▀
    ▄▄█████████▀
▀▀██████████▀
    ▀▀▀▀▀






▄█████████████████████████▄
███████████████████████████
███████████████▀       ████
██████████████      ▄▄▄████
██████████████    ▐████████
██████████████    ▐████████
██████████            ▐████
██████████            █████
██████████████    ▐████████
██████████████    ▐████████
██████████████    ▐████████
▀█████████████    ▐███████▀






                   ▄▄████
              ▄▄████████▌
         ▄▄█████████▀███
    ▄▄██████████▀▀ ▄███▌
▄████████████▀▀  ▄█████
▀▀▀███████▀   ▄███████▌
      ██    ▄█████████
       █  ▄██████████▌
       █  ███████████
       █ ██▀ ▀██████▌
       ██▀     ▀████
                 ▀█▌
Pages: [1] 2 3 »  All
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!