📊 Social Media SQL Analytics Project
A fully relational SQL project that simulates a real social media platform with users, posts, likes, comments, followers, messages, and hashtags.
Built with PostgreSQL and designed for advanced SQL analytics and portfolio demonstration.
🚀 Project Overview
This project models the core features of platforms like Instagram or Twitter using pure SQL.
You can perform:
User analytics
Engagement analysis
Ranking & leaderboards
Follower network analysis
Post performance insights
Window functions
CTE-based queries
Data relationships analysis
Everything is designed to match real industry database standards.
🧱 Database Schema
This database contains the following tables:
Table Description
users User profiles and account info
posts User posts with optional images
likes Likes on posts
comments User comments on posts
follows Follower–following relationships
hashtags Hashtags used on posts
post_hashtags Mapping posts ↔ hashtags
messages Direct messages between users
The schema includes foreign keys, CASCADE deletes, unique constraints, and primary keys.
📁 All table definitions are in:
schema/create_tables.sql
📥 Sample Data
The project includes fully realistic sample data:
5 users
5 posts
Likes, comments, followers
Non-conflicting inserts
Clean and simple structure
📁 Sample data file:
data/sample_inserts.sql
You can load the data by running this script in pgAdmin or psql.
📊 Analytics & SQL Queries
The project contains 20 advanced SQL queries, including:
✔ Most liked posts
✔ Posts with highest comments
✔ Top influencers
✔ Mutual followers
✔ User engagement rate
✔ Most active users
✔ Window functions: RANK()
✔ Users with no posts
✔ Likes and comments received
✔ Comment ranking
✔ Post/follower ratio
📁 Analytics queries are in:
queries/analytics_queries.sql
These queries demonstrate real-world SQL skills useful for interviews and data roles.
🛠 Tech Stack
PostgreSQL 16+
pgAdmin 4
Pure SQL (no backend or Python required)
🔧 How to Run
Create a new database:
CREATE DATABASE social_media_db;
Run table schema:
schema/create_tables.sql
Insert sample data:
data/sample_inserts.sql
Run analytics:
queries/analytics_queries.sql
📈 Why This Project is Valuable
This project demonstrates:
Advanced SQL skills
Database design knowledge
Real social media relational modeling
Analytical thinking
Clean GitHub project structure
Interview-ready queries
Perfect for showcasing SQL + database skills in your portfolio.
👨💻 Created By
Akshay R
(SQL, Python & Data Analytics Student)