Last active
November 22, 2022 18:03
-
-
Save NimaBavari/f6ad91c1cad3223f0bc8092ed34f4c9d to your computer and use it in GitHub Desktop.
How to Determine the Credibility of "Authorities" in Social Media
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Credibility Index | |
My friend Amin asked me if it was possible to find how good an opinion leader | |
any online person is. It is! | |
I came up with this algorithm: for each (user, context) grouping, find the mean | |
popularity of that grouping. Using this, compute a credibility score. Then, for | |
each user, find the minimum of the credibility scores across all contexts for | |
that user and assign it as his/her final credibility score (since each chain is | |
as strong as its weakest link). | |
Algorithm & Implementation: Nima Bavari <nima.bavari@gmail.com> | |
https://www.github.com/NimaBavari | |
*/ | |
CREATE TABLE all_tweets( | |
ID UUID NOT NULL, | |
author_id UUID NOT NULL, | |
tweet_text TEXT NOT NULL, | |
context VARCHAR(20) NOT NULL, -- get the context by parsing the tweet_text | |
popularity DOUBLE PRECISION NOT NULL, -- get the popularity based on likes, dislikes, and retweets | |
PRIMARY KEY (ID), | |
FOREIGN KEY (author_id) REFERENCES users(ID) | |
); | |
CREATE TABLE users( | |
ID UUID NOT NULL, | |
schooling DOUBLE PRECISION NOT NULL, | |
experience DOUBLE PRECISION NOT NULL, | |
PRIMARY KEY (ID) | |
); | |
SELECT | |
groups.g_auth_id, | |
MIN(groups.cred) AS real_cred | |
FROM | |
(SELECT | |
t.author_id AS g_auth_id, | |
t.context AS g_context | |
POWER(AVG(t.popularity) * u.schooling * u.experience, 1/3) AS cred | |
FROM all_tweets AS t | |
INNER JOIN users AS u | |
ON t.author_id = u.ID | |
GROUP BY t.author_id, t.context) AS groups | |
GROUP BY groups.g_auth_id | |
ORDER BY real_cred DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment