Tool to run online SQL queries over Full Merit Data - page 6.

Piggy

hero member

Activity: 784

Merit: 1416

Updated with the latest data of today 10th of August

Some information about last week merit distribution:

Top sender per board

Code:

Total Merit	UserName	Top merit sender	Local board
170	chimk	Full Member	Pyccкий (Russian)
156	suchmoon	Legendary	Meta
106	suchmoon	Legendary	Development & Technical Discussion
83	suchmoon	Legendary	Economics
66	iCEBREAKER	Legendary	Announcements (Altcoins)
50	Vlad2Vlad	Legendary	Bitcoin Discussion
45	stingers	Legendary	Marketplace
40	dbshck	Staff	Bahasa Indonesia (Indonesian)
33	EFS	Staff	Türkçe (Turkish)
31	Halab	Member	Français
30	klf	Legendary	
25	paxmao	Sr. Member	Español (Spanish)
25	generalizethis	Legendary	Speculation (Altcoins)
22	qwk	Donator	Deutsch (German)
20	kellendil	Hero Member	Altcoin Discussion
20	grendel25	Legendary	Mining (Altcoins)
19	LoyceV	Legendary	Beginners & Help
16	Micio	Legendary	Italiano (Italian)
16	Vod	Legendary	Project Development
16	Vod	Legendary	Trading Discussion
11	frodocooper	Staff	Mining
10	Jet Cash	Hero Member	Bitcoin Technical Support
10	Leonard2016	Hero Member	Other languages/locations
10	theymos	Administrator	Serious discussion
9	Jurgen	Sr. Member	Nederlands (Dutch)
9	jacee	Legendary	Philippines
9	theymos_away	Member	Politics & Society
8	u9y42	Legendary	Português (Portuguese)
7	RegulusHr	Full Member	Hrvatski (Croatian)
5	Aleister Crowley	Hero Member	Marketplace (Altcoins)
5	rekinthis	Hero Member	中文 (Chinese)
3	irfan_pak10	Copper Member	India
3	malevolent	Staff	Polski
3	hugeblack	Sr. Member	العربية (Arabic)
3	sncc	Full Member	日本語 (Japanese)
2	tvbcof	Legendary	Off-topic
1	Spazzer	Member	Archival

Top receiver per board

Code:

Total Merit	UserName	Top merit receiver	Local board
69	hilariousetc	Hero Member	Meta
53	satoshi	Founder	Bitcoin Discussion
48	Hhampuz	Hero Member	Marketplace
38	micgoossens	Sr. Member	Economics
37	MarcusMillstrom	Full Member	Announcements (Altcoins)
34	ICOEthics	Newbie	Trading Discussion
27	Jet Cash	Hero Member	Serious discussion
25	BitCryptex	Member	Development & Technical Discussion
25	yurimir	Legendary	Speculation (Altcoins)
21	achow101	Staff	Bitcoin Technical Support
21	Cryptoshka	Member	Pyccкий (Russian)
19	abhiseshakana	Member	Bahasa Indonesia (Indonesian)
16	joulion86	Member	Beginners & Help
14	squatz1	Hero Member	Politics & Society
13	dagarair	Sr. Member	Mining (Altcoins)
13	finaleshot2016	Full Member	Philippines
12	javierbaco	Full Member	Español (Spanish)
12	baba0000000000	Full Member	Français
12	Halminer	Newbie	Mining
12	WaDa	Full Member	Türkçe (Turkish)
10	hilariousandco	Global Moderator	
10	BTCforJoe	Hero Member	Project Development
9	Valuyet	Member	Altcoin Discussion
9	SjoerdBakker	Jr. Member	Nederlands (Dutch)
8	mole0815	Full Member	Deutsch (German)
6	Broon	Jr. Member	Archival
6	nngella	Jr. Member	Marketplace (Altcoins)
6	hakka	Member	日本語 (Japanese)
4	RegulusHr	Full Member	Hrvatski (Croatian)
4	Ale88	Sr. Member	Italiano (Italian)
4	texgeek	Jr. Member	Português (Portuguese)
2	hugeblack	Sr. Member	العربية (Arabic)
2	smileflowers	Jr. Member	中文 (Chinese)
1	Decker	Jr. Member	Off-topic
1	wgd	Legendary	Polski
1	SergiuDemerji	Member	Română (Romanian)

Circulation per board

Code:

Total Merit	SubBoard
10	Other languages/locations
9	Nederlands (Dutch)
3	India
10	日本語 (Japanese)
119	deleted
163	Altcoin Discussion
457	Announcements (Altcoins)
7	Archival
105	Bahasa Indonesia (Indonesian)
127	Beginners & Help
242	Bitcoin Discussion
38	Bitcoin Technical Support
77	Deutsch (German)
146	Development & Technical Discussion
415	Economics
62	Español (Spanish)
65	Français
21	Hrvatski (Croatian)
22	Italiano (Italian)
461	Marketplace
43	Marketplace (Altcoins)
644	Meta
76	Mining
114	Mining (Altcoins)
11	Off-topic
60	Philippines
32	Politics & Society
3	Polski
26	Português (Portuguese)
36	Project Development
1	Română (Romanian)
85	Serious discussion
49	Speculation (Altcoins)
127	Trading Discussion
105	Türkçe (Turkish)
617	Pyccкий (Russian)
6	العربية (Arabic)
8	中文 (Chinese)

Piggy

hero member

Activity: 784

Merit: 1416

Quote from: lukeburchill on August 03, 2018, 04:15:36 AM

@Piggy
your tool is awesome, I tried to rebuild it in a local db (xampp).

btw,
You are a star on my local board Grin

see one member on the local board, made a topic about the distribution of merit also he included your name with the title and said a special thank you in the last sentence.

Merit Analisis Local Indonesian (use a tool by Piggy)

Nice to see somebody is actually using it, i don't understand what is written, but i see there are quite interesting informations shown in there

lukeburchill

member

Activity: 78

Merit: 17

@Piggy
your tool is awesome, I tried to rebuild it in a local db (xampp).

btw,
You are a star on my local board Grin

see one member on the local board, made a topic about the distribution of merit also he included your name with the title and said a special thank you in the last sentence.

Merit Analisis Local Indonesian (use a tool by Piggy)

Piggy

hero member

Activity: 784

Merit: 1416

Updated with the latest data and few informations about the last week:

Total merit amount so far
201185

Top merit receivers in all boards in the last week:

Code:

Total Merit	UserName	Top merit receiver	Local board
65	theyoungmillionaire	Member	Meta
63	dagarair	Sr. Member	Mining (Altcoins)
50	secousa	Full Member	Announcements (Altcoins)
40	BitCryptex	Member	Off-topic
29	ryanc	Member	Development & Technical Discussion
25	erguse	Sr. Member	Türkçe (Turkish)
22	Jet Cash	Hero Member	Beginners & Help
21	Spendulus	Legendary	Politics & Society
20	nullCoiner	Sr. Member	Deutsch (German)
20	DaveF	Hero Member	Marketplace
17	Kakmakr	Legendary	Bitcoin Discussion
17	yahoo62278	Legendary	Trading Discussion
16	micgoossens	Sr. Member	Economics
15	finaleshot2016	Full Member	Philippines
13	achow101	Staff	Bitcoin Technical Support
12	malekcap	Member	Marketplace (Altcoins)
12	esmanthra	Full Member	Pyccкий (Russian)
10	Compiler	Member	Español (Spanish)
10	bittraffic	Hero Member	Speculation (Altcoins)
9	Steamtyme	Full Member	Mining
7	Shazam!!!	Full Member	
7	Nusa Platform	Copper Member	Bahasa Indonesia (Indonesian)
7	Halab	Member	Français
7	texgeek	Jr. Member	Português (Portuguese)
6	reypinyoko	Newbie	Altcoin Discussion
5	HostFat	Staff	Italiano (Italian)
4	andrew4	Jr. Member	Eλληνικά (Greek)
3	slackovic	Full Member	Hrvatski (Croatian)
3	Vod	Legendary	Project Development
2	kenzawak	Member	Serious discussion
2	riosakamoto	Full Member	العربية (Arabic)
1	Arrrvin	Member	Nederlands (Dutch)
1	wego	Sr. Member	中文 (Chinese)
1	sncc	Full Member	日本語 (Japanese)

Merit distibuted per board the last week

Code:

Total Merit	SubBoard
39	deleted
140	Altcoin Discussion
270	Announcements (Altcoins)
47	Bahasa Indonesia (Indonesian)
75	Beginners & Help
150	Bitcoin Discussion
16	Bitcoin Technical Support
133	Deutsch (German)
114	Development & Technical Discussion
186	Economics
31	Español (Spanish)
31	Français
14	Hrvatski (Croatian)
7	India
9	Italiano (Italian)
161	Marketplace
26	Marketplace (Altcoins)
606	Meta
45	Mining
129	Mining (Altcoins)
1	Nederlands (Dutch)
46	Off-topic
44	Philippines
76	Politics & Society
26	Português (Portuguese)
13	Project Development
8	Serious discussion
62	Speculation (Altcoins)
127	Trading Discussion
202	Türkçe (Turkish)
5	Eλληνικά (Greek)
331	Pyccкий (Russian)
6	العربية (Arabic)
2	中文 (Chinese)
2	日本語 (Japanese)

Piggy

hero member

Activity: 784

Merit: 1416

Updated with the new merit data log, 27th of July

Total merits 197.570
Merit given in this last week: 3.864

Piggy

hero member

Activity: 784

Merit: 1416

Quote from: Lakai01 on July 23, 2018, 11:50:23 PM

Thank you very much for the effort you put into this project. I really appreciate it! I built a bunch of statements to get statistics about local boards, I am going to share them asap

Glad to hear it was of help. Feel free to share in here your finding, maybe other people may find it useful or get inspired

Lakai01

legendary

Activity: 2296

Merit: 2721

Top Crypto Casino

Thank you very much for the effort you put into this project. I really appreciate it! I built a bunch of statements to get statistics about local boards, I am going to share them asap

Piggy

hero member

Activity: 784

Merit: 1416

The tool is updated with the data from today, 20th of July.

Piggy

hero member

Activity: 784

Merit: 1416

Updated with the latest data (Friday 13th of July)

Piggy

hero member

Activity: 784

Merit: 1416

Script system

The scripting system is now ready, i may make still some small changes, in any case it can be used already. With this you can break down those few remaining limits coming from using SQL.

I was talking few days back about the problem of calculating the minimum amount of merits generated by simply receiving merits and not spent yet. That has proven to be quite difficult to be calculated solely with SQL, since trying to do many operations at the same time increase the complexity and generate convoluted queries which are hard to understand Sad

Now i can show you how this can be done in an easier and more understandable way with the new scripting system.

How it works?

It’s a mix of Javascript and SQL that get injected and executed.

How to use it:

With the scripting system you can basically use everything Javascript has to offer, i also added a set of functions to facilitate the interaction with the data:

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) given a query result get the field specified by row and column index
GetCoulmHeader(query_result_data,coulmn_index) given a query result get the header data name by column index
Length(query_result_data): number of rows we got from a query result
WriteLine(text): output anything you want to show as a result of your operations
Clear(): clear the output, anything that was written or displayed

If you make some mistake in building up your script, you should get an error hinting where the problem is, however i recommend to look into the Console of your browser if you encounter some problem you cannot wrap your head around (you can open it with F12, Console tab).

In here you can find all the SQL/Script examples and info:
https://github.com/AlbertoIT/Merit-Explorer-SQL/blob/master/README.md

Link to the tool: https://albertoit.github.io/Merit-Explorer-SQL/

Any feedback appreciated, if you need help feel free to ask here

Here is an example on how to solve the problem stated above with an explanation (this is also the default script which will be loaded automatically as you select the Script tab):

Piggy

hero member

Activity: 784

Merit: 1416

Quote from: chiyenbtre on July 08, 2018, 03:22:32 AM

Thank you, OP.
You made great guideline on how to use SQL to analyze merit data.
Honestly, I have never used SQL before, but I gonna learn it for sure after reading your helpful topic.

Good, is always a nice idea learning new skills. And in this case it may be spendable elsewhere, in a working context.

chiyenbtre

jr. member

Activity: 45

Merit: 4

Thank you, OP.
You made great guideline on how to use SQL to analyze merit data.
Honestly, I have never used SQL before, but I gonna learn it for sure after reading your helpful topic.

Piggy

hero member

Activity: 784

Merit: 1416

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

Piggy

hero member

Activity: 784

Merit: 1416

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
)

Piggy

hero member

Activity: 784

Merit: 1416

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.

Makkara

full member

Activity: 1064

Merit: 166

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

Activity: 784

Merit: 1416

Quote from: TheBeardedBaby on June 29, 2018, 10:36:29 AM

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

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

TheBeardedBaby

legendary

Activity: 2184

Merit: 3134

₿uy / $ell

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

Activity: 784

Merit: 1416

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?

Piggy

hero member

Activity: 784

Merit: 1416

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,
'' || fromid || '' as Sender,
'' || toid || '' as Receiver,
merit,
'Link Merited post' as MeritedPost
FROM meritdata 
WHERE merit >= 50
LIMIT 10;

Topic: Tool to run online SQL queries over Full Merit Data - page 6. (Read 23326 times)