__STYLES__
There are more than 4,000 tweets from 2015 originating from different parts of the United States, all related to New Year's resolutions, included in the dataset.
After a first glance at the data and reviewing the suggested questions, the tweets analysis was divided into 4 sections: Category, Time, State and Gender. Through the dataset, 15 questions are answered through 2 to 8 insights in each section.
A. Category: A.1. What is the most popular resolution category? Least popular?
SELECT DISTINCT COUNT(tweet_category) AS category_count, tweet_category
FROM [New_years_resolutions_2020]
GROUP BY tweet_category
ORDER BY category_count DESC;
A.2. What is the most popular resolution category by state?
WITH ranks AS (
select DISTINCT COUNT(tweet_category) AS total_count,
tweet_category,
tweet_state,
row_number() over (partition by tweet_state order by COUNT(tweet_category) DESC) AS state_rank
from New_years_resolutions_2020
GROUP BY tweet_category, tweet_state)
SELECT * FROM ranks
where ranks.state_rank <= 1
group by ranks.tweet_category, ranks.tweet_state, ranks.total_count, ranks.state_rank;
A.3. What is the least popular resolution category by state?
WITH ranks AS (
select DISTINCT COUNT(tweet_category) AS total_count,
tweet_category,
tweet_state,
row_number() over (partition by tweet_state order by COUNT(tweet_category) ASC) AS state_rank
from New_years_resolutions_2020
GROUP BY tweet_category, tweet_state)
SELECT * FROM ranks
where ranks.state_rank <= 1
group by ranks.tweet_category, ranks.tweet_state, ranks.total_count, ranks.state_rank
A.4. Which resolution category was retweeted the most? Least?
SELECT DISTINCT SUM(retweet_count) AS total_retweet, tweet_category
FROM [New_years_resolutions_2020]
GROUP BY tweet_category
ORDER BY total_retweet DESC;
A.5. What is the most popular resolution topic?
SELECT DISTINCT TOP 5 COUNT(tweet_category) AS category_count, tweet_topics, tweet_category
FROM [New_years_resolutions_2020]
GROUP BY tweet_topics, tweet_category
ORDER BY category_count DESC;
A.6. What is the least popular resolution topic?
SELECT DISTINCT TOP 5 COUNT(tweet_category) AS category_count, tweet_topics, tweet_category
FROM [New_years_resolutions_2020]
GROUP BY tweet_topics, tweet_category
ORDER BY category_count ASC;
A.7. Which resolution topic was retweeted the most?
SELECT TOP 5 SUM(retweet_count) AS retweet_count, tweet_topics, tweet_category
FROM [New_years_resolutions_2020]
WHERE retweet_count IS NOT NULL
GROUP BY tweet_topics, tweet_category
ORDER BY SUM(retweet_count) DESC;
A.8. Which resolution topic was retweeted the least?
SELECT TOP 5 SUM(retweet_count) AS retweet_count, tweet_topics, tweet_category
FROM [New_years_resolutions_2020]
WHERE retweet_count IS NOT NULL
GROUP BY tweet_topics, tweet_category
ORDER BY SUM(retweet_count) ASC;
B. Time
B.1 Rounding to the nearest hour, what was the most popular hour of day to tweet? How many resolutions
were tweeted?
SELECT DISTINCT TOP 5 datepart(hh,tweet_created) AS hours_tweeted, COUNT(tweet_text) AS total_tweets
FROM [New_years_resolutions_2020]
GROUP BY datepart(hh,tweet_created)
ORDER BY total_tweets DESC;
B.2 Rounding to the nearest hour, what was the most popular hour of day to tweet by State? How many
resolutions were tweeted?
WITH ranks AS (
select DISTINCT datepart(hh,tweet_created) AS hours_tweeted,
COUNT(tweet_text) AS total_tweets,
tweet_state,
row_number() over (partition by tweet_state order by COUNT(tweet_category) DESC) AS state_rank
from New_years_resolutions_2020
GROUP BY datepart(hh,tweet_created), tweet_state)
SELECT * FROM ranks
where ranks.state_rank <= 1
group by ranks.tweet_state, ranks.total_tweets, ranks.state_rank, ranks.hours_tweeted;
B.3 What day was the most tweeted? How many resolutions were tweeted?
SELECT DISTINCT datepart(dd,tweet_created) AS days_tweeted, datepart(MONTH,tweet_created) AS month_tweeted, COUNT(tweet_text) AS total_tweets
FROM [New_years_resolutions_2020]
GROUP BY datepart(dd,tweet_created), datepart(MONTH,tweet_created)
ORDER BY total_tweets DESC;
C. State C.1 What U.S. State tweeted the highest number of NYE resolutions?
SELECT TOP 5 tweet_state, COUNT(tweet_topics) AS total_tweets
FROM [New_years_resolutions_2020]
GROUP BY tweet_state
ORDER BY total_tweets DESC;
C.2 What U.S. State tweeted the least number of NYE resolutions?
SELECT TOP 5 tweet_state, COUNT(tweet_topics) AS total_tweets
FROM [New_years_resolutions_2020]
GROUP BY tweet_state
ORDER BY total_tweets ASC;
D. Gender D.1 Who tweeted the most?
SELECT DISTINCT COUNT(tweet_category) AS category_count, user_gender,
FORMAT(round(COUNT(tweet_category) * 1.0/(SELECT COUNT(tweet_category) FROM [New_years_resolutions_2020]),2,1),'0.00') AS perctg_tweets
FROM [New_years_resolutions_2020]
GROUP BY user_gender
ORDER BY category_count DESC;
D.2. What twitter category was the most tweeted by gender?
SELECT DISTINCT COUNT(tweet_category) AS category_count, tweet_category, user_gender
FROM [New_years_resolutions_2020]
GROUP BY user_gender, tweet_category
ORDER BY category_count DESC;
D.3. What twitter category was the most retweeted by gender?
SELECT DISTINCT SUM(retweet_count) AS total_retweet, tweet_category, user_gender
FROM [New_years_resolutions_2020]
GROUP BY user_gender, tweet_category
ORDER BY total_retweet DESC;
D.4. What topic was the most tweeted by gender?
SELECT DISTINCT TOP 2 COUNT(tweet_category) AS category_count, tweet_topics, tweet_category, user_gender
FROM [New_years_resolutions_2020]
GROUP BY tweet_category, user_gender, tweet_topics
ORDER BY category_count DESC;
D.5. What topic was the least tweeted by gender?
SELECT DISTINCT TOP 5 COUNT(tweet_category) AS category_count, tweet_topics, tweet_category, user_gender
FROM [New_years_resolutions_2020]
GROUP BY tweet_category, user_gender, tweet_topics
ORDER BY category_count ASC;
© Maven Analytics, LLC | All Rights Reserved