Instagram_Clone

Instagram_Clone SQL Analysis

Project Overview

The Instagram Clone project focuses on creating a social media platform where users can share photos, interact through likes and comments, and build connections via a follow system. The database is designed for scalability, efficient data management, and seamless user interaction.

Project Objectives

• User Management: Create and manage unique user profiles.
• Content Sharing: Enable photo uploads with ownership and timestamp tracking.
• User Interactions: Support likes, comments, and content engagement.
• Social Networking: Implement a follow system to build user relationships.
• Data Integrity: Maintain robust relational data structures.
• Scalability: Design for growth and future feature integration.
• Analytics: Facilitate insights into user behavior and platform trends.

Operational Efficiency

Optimized Database Design: Normalized schema with indexing for fast queries. Data Integrity: Use foreign keys to maintain relationships and prevent data inconsistencies. Scalability: Designed to handle growth with options for sharding and replication. Query Performance: Efficient JOINs and optimized SQL queries. Efficient Storage: Minimal data redundancy and archival of old data. Concurrency: Handle simultaneous user actions with transactions and consistency checks. Monitoring: Regular performance checks and data backups. Load Balancing: Distribute traffic to maintain responsiveness during peak loads

Query Task

Q1 We want to reward our users who have been around the longest. Find the 5 oldest users.?

Q2 What day of the week do most users register on? We need to figure out when to schedule an ad campgain?

Q3 We want to target our inactive users with an email campaign. Find the users who have never posted a photo?

Q4 How many times does the average user post?*//*total number of photos/total number of users?

Q5 Find user ranking by postings higher to lower?

Q6 Total Posts by users (SELECT COUNT(*)FROM photos) ?

Q7 Total numbers of users who have posted at least one time ?

Q8 A brand wants to know which hashtags to use in a post What are the top 5 most commonly used hashtags?

Q9 *We have a small problem with bots on our site...Find users who have liked every single photo on the site?

Q10 List all comments on photos uploaded by a specific user?

Q11 Find the most liked photo and its uploader?

Q12 Find all users who haven't uploaded a photo?

Q13 Count the number of tags used on each photo?

Q14 Find photos with more than 5 likes?

Q15 List usernames of people who commented on their own photos?

Q16 Find the user who follows the most other users?

Q17 Get the total number of comments made by each user?

Q18 Find photos that have comments from at least 3 distinct users?

Q19 List users who have never uploaded a photo or made a comment?

Q20 Count the total number of likes each user has received on their photos?

Q21 Find tags that are associated with more than 10 photos?

Q22 Find the top 3 users with the most followers?

Q23 Find users who like every photo uploaded by a specific user?

Q24 List photos with the most comments?

Q25 List users who have liked more than 50 photos?