Project Description:-

Imagine you're a data analyst working with the product team at Instagram. Your role involves analyzing user interactions and engagement with the Instagram app to provide valuable insights that can help the business grow. User analysis involves tracking how users engage with a digital product, such as a software application or a mobile app. The insights derived from this analysis can be used by various teams within the business. For example, the marketing team might use these insights to launch a new campaign, the product team might use them to decide on new features to build, and the development team might use them to improve the overall user experience. In this project, you'll be using SQL and MySQL Workbench as your tool to analyze Instagram user data and answer questions posed by the management team. Your insights will help the product manager and the rest of the team make informed decisions about the future direction of the Instagram app. Remember, the goal of this project is to use your SQL skills to extract meaningful insights from the data. Your findings could potentially influence the future development of one of the world's most popular social media platforms.

SQL Tasks:-

A) Marketing Analysis:

1. Loyal User Reward: The marketing team wants to reward the most loyal users, i.e., those who have been using the platform for the longest time. Your Task: Identify the five oldest users on Instagram from the provided database.
2. Inactive User Engagement: The team wants to encourage inactive users to start posting by sending them promotional emails. Your Task: Identify users who have never posted a single photo on Instagram.
3. Contest Winner Declaration: The team has organized a contest where the user with the most likes on a single photo wins. Your Task: Determine the winner of the contest and provide their details to the team.
4. Hashtag Research: A partner brand wants to know the most popular hashtags to use in their posts to reach the most people. Your Task: Identify and suggest the top five most commonly used hashtags on the platform.
5. Ad Campaign Launch: The team wants to know the best day of the week to launch ads. Your Task: Determine the day of the week when most users register on Instagram. Provide insights on when to schedule an ad campaign.

B) Investor Metrics:

1. User Engagement: Investors want to know if users are still active and posting on Instagram or if they are making fewer posts. Your Task: Calculate the average number of posts per user on Instagram. Also, provide the total number of photos on Instagram divided by the total number of users.
2. Bots & Fake Accounts: Investors want to know if the platform is crowded with fake and dummy accounts. Your Task: Identify users (potential bots) who have liked every single photo on the site, as this is not typically possible for a normal user.

Approach:-

For this project, I have used My SQL to extract the required data from the given database using the Join function, subqueries, Aggregation, where condition, Group by, Distinct and other functions required. keeping the Primary key and foreign key in consideration provided all the reports asked by the marketing department and Investor metrics department. I have used Canva for making this presentation as it contains the required Elements, Graphs, and Images which made this project more attractive.

1. Find the 5 oldest users of the Instagram from the database provided. 
Query:-

SELECT 
    username, created_at
FROM
    users
ORDER BY created_at
LIMIT 5;

2. Find the users who have never posted a single photo on Instagram.
Query:-

SELECT 
    u.username
FROM
    users u
        LEFT JOIN
    photos p ON u.id = p.user_id
WHERE
    p.user_id IS NULL
ORDER BY u.username;

3. Identify the winner of the contest and provide their details to the team.
Query:-

WITH base AS
(
SELECT 
    likes.photo_id,
    users.username,
    COUNT(likes.user_id) AS Likes,
    users.id AS user_id,
    photos.image_url AS Image_url
FROM
    likes
        INNER JOIN
    photos ON likes.photo_id = photos.id
        INNER JOIN
    users ON photos.user_id = users.id
GROUP BY likes.photo_id , users.username
ORDER BY Likes DESC
LIMIT 1
)
SELECT 
     user_id, username, Image_url, Likes 
FROM
    base;

4. Identify and suggest the top 5 most commonly used hashtags on the platform.
Query:-

SELECT 
    t.tag_name, COUNT(p.photo_id) AS num_tags
FROM
    photo_tags p
        INNER JOIN
    tags t ON p.tag_id = t.id
GROUP BY tag_name
ORDER BY num_tags DESC
LIMIT 5; 

5. What day of the week do most users register on? Provide insights on when to schedule an ad campaign.
Query:-

SELECT 
    DAYNAME(created_at) AS day_of_week,
    COUNT(id) AS User_Registered
FROM
    users
GROUP BY day_of_week
ORDER BY User_Registered DESC
LIMIT 5;

1. Provide how many times does average user posts on Instagram. Also, provide the total number of photos on Instagram/total number of users.
Query:-

WITH cte AS
(
SELECT 
    u.id AS userid, COUNT(p.id) AS photoid
FROM
    users u
        LEFT JOIN
    photos p ON u.id = p.user_id
GROUP BY u.id
)
SELECT 
    SUM(photoid) AS total_photo, COUNT(userid) AS total_users
FROM
    cte;


SELECT 
    AVG(Number_of_Post) AS average_post_per_user
FROM
    (SELECT 
        user_id, COUNT(*) AS Number_of_Post
    FROM
        photos
    GROUP BY user_id) AS average_post_count;

2. 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).
Query:-

SELECT 
    user_id, username
FROM
    likes
        INNER JOIN
    users ON likes.user_id = users.id
GROUP BY user_id
HAVING COUNT(DISTINCT photo_id) = (SELECT 
        COUNT(*)
    FROM
        photos);