Skip to content

This project involves designing and implementing a database system to handle various aspects of a social media platform. In this project i have used various advanced functions like cte,views,index and windows functions.

Notifications You must be signed in to change notification settings

AAYUSMANN/social_media_analysis

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

5 Commits
 
 
 
 
 
 
 
 

Repository files navigation

Social Media Database Project

About

  • Through this project I am creating a basic structure of social media database which could easily be connected to frontend interface.
  • Here I am managing the data of multiple users, their followers, interests and public activity on the social media platform which includes postlikes ,comments , commentlikes , hashtag followed ,bookmarks and many more...
  • Through my SQL query I have shown a clear cut description of connection and inter relation between different activities on social media.
  • With my true efforts and research I have tried to give a simple and more exact view of a basic social interconnecting site.

Benefits for the Stakeholder

  1. Query 1: Find the user with the most followers

    • This query identifies the user who has the largest number of followers. It returns the user's ID, username, and the count of their followers.
  2. Query 2: Calculate the average post size by post type (photo or video)

    • This query calculates the average size of posts based on their type, which can be either "Photo" or "Video." It provides insights into the average size for each post type.
  3. Query 3: List the top 5 posts with the most comments and their user owners

    • This query lists the top 5 posts with the highest number of comments and their respective user owners. It's useful for identifying popular posts and their creators.
  4. Query 4: Calculate the total number of likes on posts by each user

    • This query calculates the total number of likes received by each user on their posts. It helps in understanding which users receive the most likes.
  5. Query 5: Create a view to display the total number of comments and likes for each post

    • This query creates a view that displays the combined number of comments and likes for each post. It's useful for easily accessing this information in the future.
  6. Query 6: List the 10 most popular hashtags along with the number of posts they are used in

    • This query identifies and lists the top 10 most popular hashtags and the number of posts they are associated with. It's helpful for tracking trending topics.
  7. Query 7: List the users who have liked their own posts

    • This query identifies users who have liked their own posts. It can be useful for monitoring self-liking behavior.
  8. Query 8: Find users who have not posted any photos or videos

    • This query finds users who have not uploaded any photos or videos. It's a way to identify users who haven't shared multimedia content.
  9. Query 9: Find users who have bookmarked the same post that they liked

    • This query identifies users who have both liked and bookmarked the same posts. It's helpful for understanding user engagement with posts.
  10. Query 10: Find the users who have commented on the most posts

    • This query identifies the top 10 users who have commented on the highest number of posts. It's useful for recognizing highly engaged users in terms of comments.
  11. Query 11: Any specific word in comment

    • This query searches for comments containing specific words like "good" or "beautiful." It's useful for finding and categorizing comments with particular keywords.

About

This project involves designing and implementing a database system to handle various aspects of a social media platform. In this project i have used various advanced functions like cte,views,index and windows functions.

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published