Merit Explorer SQL

A tool made with Javascript and SQLite to run queries over the merit data on the forum Bitcointalk.

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

Tables:

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
)

Script functions:

PrintTableResult(query_result_data): Draw a tabulated version of the data coming from a SQL query.
RunSQLSynch(sql_command): Execute the SQL command specified and return an object containing the data
RunSQLAsynch(sql_command): Execute the SQL command in asynchronous mode, the output cannot be captured back and just show the result of the query on table
GetDataField(query_result_data,row_index,coulmn_index)
GetCoulmHeader(query_result_data,coulmn_index)
Length(query_result_data,): number of rows we got
WriteLine(text): output anything you want to show as a result of your operations
Clear(): clear the output

Example SQL:

Html links formatting:

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
Inner Join UserData on UserData.userid = meritdata.fromid
WHERE UserData.Username like "theymos"

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

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:

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:

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:

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:

SELECT Sum(Merit) FROM MeritData;

Want the full history for a particular user?

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

Now we can show the user information instead of the user id:

SELECT MAX(result.total) as "Total Merit", UserData.UserName, UserData.Rank 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
INNER JOIN UserData ON UserData.UserId = result.toid
GROUP BY SubBoard
ORDER BY result.total Desc

Selecting data by a particular time frame is quite easy (all merit transaction happened the 1/06/2018):

SELECT * FROM MeritData
WHERE strftime('%m', date) = "06" AND strftime('%d', date) = "01" AND strftime('%y', date) = "2018";

Select by a time frame (from 2018 to 15/06/2018):

SELECT * FROM MeritData
WHERE date BETWEEN '2018-06-14' AND '2018-06-15';

You can also use hour minutes (and seconds) to check a 30 minutes interval:

SELECT * FROM MeritData
WHERE date BETWEEN '2018-06-14T15:00:00' AND '2018-06-14T15:30:00';

How many merits sent for each rank?

SELECT UserData.Rank, SUM(MeritData.Merit) as Total
FROM MeritData
INNER JOIN UserData ON UserData.UserId = MeritData.fromid
GROUP BY UserData.Rank;

How may merits sent from one rank to another? Full member to Legendary

SELECT UserFullMember.Rank, UserLegendary.Rank, SUM(MeritData.Merit) as Total
FROM MeritData
INNER JOIN UserData as UserFullMember ON UserFullMember.UserId = MeritData.fromid AND UserFullMember.Rank like "full mem%"
INNER JOIN UserData as UserLegendary ON UserLegendary.UserId = MeritData.toid AND UserLegendary.Rank like "lege%"
GROUP BY UserFullMember.Rank,UserLegendary.Rank;

Legendary to Full member

SELECT UserFullMember.Rank, UserLegendary.Rank, SUM(MeritData.Merit) as Total
FROM MeritData
INNER JOIN UserData as UserFullMember ON UserFullMember.UserId = MeritData.toid AND UserFullMember.Rank like "full mem%"
INNER JOIN UserData as UserLegendary ON UserLegendary.UserId = MeritData.fromid AND UserLegendary.Rank like "lege%"
GROUP BY UserFullMember.Rank,UserLegendary.Rank;

Example Script:

// Variable holding the sum of unspent merits for each user.
var total_unspent_merits=0;

// List of username and total merit received for every user.
var merit_received = RunSQLSynch('SELECT username,  coalesce(tot,0) From userdata Left join (select toid, sum(merit)  as tot from meritdata group by toid)   on userid = toid;');

// List of username and total merit given for every user.
var merit_given = RunSQLSynch('SELECT username,  coalesce(tot,0) From userdata Left join (select fromid, sum(merit)  as tot from meritdata group by fromid)   on userid = fromid;');

// We go through the lists (they have the same lenght and already ordered in the same way).
for (var i=0;i<Length(merit_received);i++){	
  		
  		// Just to be safe we check if the Username match, by getting the first coulmn of the current row.
		if (GetDataField(merit_received,i,0) == GetDataField(merit_given,i,0)){
          
          // Calculate the unspent merits (using the forumula (Total Merit Receive/2) - Total Merit Given)
          var user_unspent_merits = (GetDataField(merit_received,i,1)/2) - GetDataField(merit_given,i,1);
         
		  // If it’s positive add it to the total.
          if (user_unspent_merits > 0)
          	total_unspent_merits += user_unspent_merits;         
        }    
}

// Clear the output information and write the result.
Clear();
WriteLine("\nTOTAL MERIT UNSPENT:" + total_unspent_merits + "\n");

For more information and discussion: https://bitcointalk.org/index.php?topic=4551881.0

albertoit / merit-explorer-sql Goto Github PK

merit-explorer-sql's Introduction

Merit Explorer SQL

merit-explorer-sql's People

Contributors

Stargazers

Watchers

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent