- 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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
For More Enquiry Click On 👉 LinkedIn