INSTAGRAM USER ANALYTICS.

SYNOPSIS

• This project is basically helps the management to take the precious decision accordingly.
• With the help of running various SQL commands one can take all those decision based on the result while running the query.
• These insights are then used by teams across the business to launch a new marketing campaign, decide on features to build for an app.
• It also helps the teams to get various results like engangement time, hashtag used, no of users. Which help teams to used take further decision.

ER DIAGRAM:

YOU ARE REQUIRED TO PROVIDE A DETAILED REPORT ANSWERING THE QUESTIONS BELOW :

Task1. Rewarding Most Loyal Users: People who have been using the platform for the longest time. Your Task: Find the 5 oldest users of the Instagram from the database provided

Ans: Sql-select * from users order by created_at limit 5;

WORKING TASK 1:-�

• In last query, teams wanted to get the output of those customers which are the oldest users through their database.
• We have used ‘LIMIT’ in the last query to get the desired output and the Marketing team can give reward to all these loyal customers as they are oldest in Instagram.
• We have used ‘ORDER BY’ clause in the query which works to make the changes into ascending or descending order, but by default it uses ascending order, so we don’t need to specify the order if we are using it in ascending order but if are using it as descending order, then we need to specify the order too.

TASK2:-FIND THE USERS WHO HAVE NEVER POSTED A SINGLE PHOTO ON INSTAGRAM.

• Ans:- Sql: select username from users left join photos on users.id = photos.user_id where photos.id is null;

WORKING TASK 2:-

• In Task2 we have find the username of those people who never posted any photo in the app, for this we need to connect two table which are ‘USER’ and ‘PHOTOS’.

When we know we have to use join now the question arises which join is best suitable here as there are many joins.

Here in this task we have to use left join, with select we have to choose username from users and else from the left table which is photos, and by joining these two tables we will got the result which is the username.

TASK3:- IDENTIFY THE WINNER OF THE CONTEST AND PROVIDE THEIR DETAILS TO THE TEAM

• Sql-select users.username ,photos.id ,photos.image_url ,count(*) as Total_Likes from likes join photos on photos.id = likes.photo_id join users ON users.id = likes.user_id Ggroup by photos.id order by Total_Likes desc limit 1;

​

​

So this will give us only one result which helps Marketing team to declare the Contest Winner. Here we have used joins to join the 2 tables so that we got the desired filtering and to get the appropriate result.

WORKING TASK3:-

• In Task 3 we have give the details of that person who got the maximum likes in the single photo so that the management can find the winner.

For fetching the result we need to connect three tables ‘USERS’, ‘PHOTOS’ and ‘LIKES’

We have used Count function to count the total no of likes and Order by is also used based on total likes so that the who will get the maximum result will be in the top of the list and then we have used LIMIT clause which is used to give the particular row.

​

TASK4: IDENTIFY AND SUGGEST THE TOP 5 MOST COMMONLY USED HASHTAGS ON THE PLATFORM.

• Ans: Sql- select tag_name, count(tag_name) as total from tags join photo_tags on

tags.id = photo_tags.tag_id group by tags.id order by total desc limit 5;

WORKING TASK 4:-

• In this task we have to find the top 5 most used hashtag by the users while posting their photo in the app
• We have the fetch the result from two table ‘TAGS’ and ‘PHOTOs’
• We have used GROUP BY clause here to make the column of the desired output and LIMIT clause is also used to give the specific result.

TASK 5: WHAT DAY OF THE WEEK DO MOST USERS REGISTER ON? PROVIDE INSIGHTS ON WHEN TO SCHEDULE AN AD CAMPAIGN

Sql: Select date_format (created_at,'%W’) as 'day of the week’, count(*) AS 'total registration’ FROM users group by 1order by 2 DESC

WORKING TASK 5:-�

• In task 5, we have to find what day of the week do most users register on and Provide insights on when to schedule an ad campaign.
• ‘W’ stands for weeks as we need to fetch result based on week so we are using ‘W’ if we have fetch result based on time so we have used ‘T’.
• All the details we can get from the USER table only and we have used count function to count the total registration to get the desired result.

​

​

TASK6:- PROVIDE DATA ON USERS (BOTS) WHO HAVE LIKED EVERY SINGLE PHOTO ON THE SITE (SINCE ANY NORMAL USER WOULD NOT BE ABLE TO DO THIS).

• Sql: select users.id,username, count(users.id) As total_likes from users join likes on users.id = likes.user_id group by users.id having total_likes = (select count(*) from photos);
• This query help to decide whether they want to keep this account or they want to suspend these account forever as they are bot and they can do nothing about it.

WORKING TASK 6:-

• In this task we have to find the list of bots who like every single photo uploaded in the app as it is very difficult to do so by an individual.
• Here we have to connect two tables ‘USERS’ and ‘JOINS’ and here we have to used the subquery also in order to give the desired output

​

CONCLUSION:-�

• All the query which we have written to answer all the Question are used to fetch the best result to take the further decision.
• The top management, or the head of department are going to use all those data and future planning can be done accordingly to engage the traffic in the app and also helps to grow the app or eventually helping the organizational goal.
• The data is very useful for helping each department to grow at its best and also makes the decision which is best suitable for them
• Users are the end user of the app, so they need to be genuine , bots can be removed.

• Every decision is very important, but when all the decision is based on the user activity it becomes very cruicial to take the every decision effectively, by all these query the teams would get to know the each minor details to improve further.
• This case study will improve to take the core decision for the management to improve the quality of the application and improving the traffic by creating various contest to engage the users everyday.
• Managing the large no of users is very difficult by this case study all the things will become easy and sorted and also very effective way to see with the help of Reverse engineering in mysql workbench which is also known as ER diagram.
• At last, all the query will help the management to take best decision.

THANK YOU.