CREATE TABLE stats_table (
    id INT AUTO_INCREMENT PRIMARY KEY
);
SELECT user_mention0_name, user_mention0_id, COUNT(*) AS num_rows 
FROM tweets_extend 
WHERE user_mention0_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359) 
GROUP BY user_mention0_name, user_mention0_id
ORDER BY num_rows DESC;

ALTER TABLE stats_table ADD COLUMN num_rows1 INT;
INSERT INTO stats_table (user_mention1name, user_mention1id, num_rows1)
SELECT COUNT(*) AS num_rows1
FROM tweets_extend 
WHERE user_mention1_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359)
GROUP BY user_mention1_name, user_mention1_id
ORDER BY num_rows1 DESC

CREATE TABLE stats_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_mention0_name VARCHAR(255),
    user_mention0_id INT,
    user_mention1_name VARCHAR(255),
    user_mention1_id INT,
    num_rows0 INT,
    num_rows1 INT
);

INSERT INTO stats_table (user_mention0_name, user_mention0_id, num_rows0)
SELECT user_mention0_name, user_mention0_id, COUNT(*) AS num_rows
FROM tweets_extend 
WHERE user_mention0_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359) 
GROUP BY user_mention0_name, user_mention0_id
ORDER BY num_rows DESC;

INSERT INTO stats_table (user_mention1_name, user_mention1_id, num_rows1)
SELECT user_mention1_name, user_mention1_id, COUNT(*) AS num_rows1
FROM tweets_extend 
WHERE user_mention1_id IN (56377143, 106062176, 18332190, 22536055, 124476322, 26223583, 2182373406, 38676903, 1542862735, 253340062, 218730857, 45621423, 20626359)
GROUP BY user_mention1_name, user_mention1_id
ORDER BY num_rows1 DESC;

SELECT num_rows0, num_rows1
FROM stats_table
WHERE user_mention0_id = 56377143 OR user_mention1_id = 56377143; -- FOR EXAMPLE TO SEE KLM HOW MANY --TWEEETS IT HAS BEEN MENTIONED

ALTER TABLE stats_table ADD COLUMN avg_char0 FLOAT;
ALTER TABLE stats_table ADD COLUMN avg_char1 FLOAT;
INSERT INTO stats_table (avg_char0, avg_char1)
SELECT 
    CASE WHEN user_mention1_id IS NULL THEN user_mention0_id ELSE user_mention1_id END AS user_mention_id,
    AVG(CHAR_LENGTH(full_text)) AS average_characters
FROM tweets_extend  
GROUP BY user_mention0_id, user_mention1_id;