How to Determine the Credibility of "Authorities" in Social Media

credibility_index.sql

/* 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;