Scribd
Upload
89 views2 pages

Instagram User Insights and Analytics

This document contains SQL queries to analyze data from an Instagram database. It identifies the 5 oldest users, users who have never posted photos, the most liked photo to determine a contest winner, the top 5 most commonly used hashtags, what day of the week most users register, the average number of posts per user, and users who have liked every single photo which are likely bots. Insights are provided on when to schedule an ad campaign based on registration patterns.

Uploaded by

Jugal Sunil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
89 views2 pages

Instagram User Insights and Analytics

This document contains SQL queries to analyze data from an Instagram database. It identifies the 5 oldest users, users who have never posted photos, the most liked photo to determine a contest winner, the top 5 most commonly used hashtags, what day of the week most users register, the average number of posts per user, and users who have liked every single photo which are likely bots. Insights are provided on when to schedule an ad campaign based on registration patterns.

Uploaded by

Jugal Sunil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SELECT * FROM users;

SELECT * FROM photos;

--1)Find the 5 oldest users of the Instagram from the database


provided

SELECT * FROM users ORDER BY created_at LIMIT 5;

--2)Find the users who have never posted a single photo on


Instagram

SELECT username
FROM users LEFT JOIN photos
ON [Link] = photos.user_id
WHERE [Link] is NULL;

--3)Identify the winner of the contest and provide their details


to the team

SELECT * FROM photos;


SELECT * FROM likes;
SELECT [Link], photos.image_url, count(*) AS total
FROM photos INNER JOIN likes
ON likes.photo_id = [Link]
GROUP BY [Link]
ORDER BY total DESC
LIMIT 1;

--4)Identify and suggest the top 5 most commonly used hashtags on


the platform

SELECT
tags.tag_name,
COUNT(*) AS total
FROM photo_tags
JOIN tags
ON photo_tags.tag_id = [Link]
GROUP BY [Link]
ORDER BY total DESC
LIMIT 5;

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

SELECT
DAYNAME(created_at) AS day,
COUNT(*) AS total
FROM users
GROUP BY day
ORDER BY total DESC
LIMIT 2;

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

SELECT(SELECT COUNT(*) FROM photos) / (SELECT COUNT(*) FROM users)


AS Average;

--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).

SELECT username,
COUNT(*) AS num_of_likes
FROM users INNER JOIN likes
ON [Link] = likes.user_id
GROUP BY likes.user_id HAVING num_of_likes = (SELECT COUNT(*)FROM
photos);

You might also like