In this project, we analyze a social media's data using SQL. The project includes a variety of questions that cover different aspects of the data, ranging from easy to advance level.
- MySQL
- MySQL Workbench
ig_clone_data: This SQL schema forms the backbone of our analysis. The column names are
- comments
- follows
- likes
- photo_tags
- photos
- tags
- users
-
Create an ER diagram or draw a schema for the given database.
-
We want to reward the user who has been around the longest. Find the 5 oldest users.
-
To understand when to run the ad campaign, figure out the day of the week most users register on?
-
To target inactive users in an email ad campaign, find the users who have never posted a photo.
-
Suppose you are running a contest to find out who got the most likes on a photo. Find out who won?
-
The investors want to know how many times does the average user post.
-
A brand wants to know which hashtag to use on a post. Find the top 5 most used hashtags.
-
To find out if there are bots, find users who have liked every single photo on the site.
-
To know who the celebrities are, find users who have never commented on a photo.
-
Now it's time to find both of them together, find the users who have never commented on any photo or have commented on every photo.
to view the solution for this case study check social-media-data-analysis-with-sql(1).sql file!