--DATA UNDERSTANFING Average amount of characters in each text 
SELECT AVG(text_range) AS average_characters
FROM tweets_extended;
-- EFFECT ON DATA How many tweets
SELECT COUNT(tweet_id) AS tweet_count
FROM tweets;
--DATA UNDERSTANDING Average replies
SELECT AVG(reply_count)
FROM tweets; 
--DATA UNDERSTANDING Which language in which most tweets were made
SELECT lang, COUNT(*) AS frequency
FROM tweets
GROUP BY lang
ORDER BY frequency DESC
--DATA UNDERSTANDING In which country most tweets were made
SELECT country, COUNT(*) AS frequency
FROM tweets
GROUP BY country
ORDER BY frequency DESC

-- Unreplied tweets
select relpy_count from data_db 
where reply_count = 0

where reply_count = 0 
                   
--EFFECT ON DATA HOW MANY DIFFERENT LANGUAGES
SELECT lang , COUNT(*) AS language_count
FROM data_db
GROUP BY lang;
-- DATA UNDERSTANDING how many tweets klm has mande 
 SELECT COUNT(*) AS total_count_klm
    FROM user_data
    WHERE user_id = '56377143'
-- DATA UNDERSTANDING how many tweets about klm where made 
SELECT COUNT(*) AS total_count_klm
    FROM tweets_extend
    WHERE user_mention0_id = '56377143' OR user_mention0_name = 'KLM' OR user_mention0_name = 'klm' OR user_mention1_name = 'klm' OR user_mention1_name = 'KLM' OR user_mention1_id = '56377143'
     
                   
-- How many null 
SELECT COUNT(*) AS count_null_url
FROM data_db
WHERE JSON_VALUE(data, '$.url') IS NULL;

-- HOW MANY TWEETS
SELECT COUNT(*) AS tweet_amount
FROM tweets
WHERE tweet_id IS NOT NULL