📊 Social Media Analytics Backend

A SQL-based backend system to track post engagement on a social media platform. Built with PostgreSQL, it handles user activity such as posts, likes, and comments — and generates engagement reports using views, window functions, and triggers.

---

🎯 Objective

Create a SQL system to:

• Track user interactions
• Analyze post engagement
• Rank top posts by engagement
• Export reports as CSV

---

🛠️ Tech Stack

• Database: PostgreSQL
• Data Generator: Python (Faker, psycopg2)
• Tools: SQL Shell (psql), pgAdmin

---

🗂️ Database Schema

📁 Tables:

• users — stores user info
• posts — stores user posts
• likes — tracks which user liked which post
• comments — stores post comments

⚙️ Example Schema:

users(id, username, email, created_at)
posts(id, user_id, content, created_at, like_count, comment_count)
likes(id, user_id, post_id, created_at)
comments(id, user_id, post_id, content, created_at)

---

📊 Analytics Features

✅ 1. Engagement View

A SQL view post_engagement to calculate:

engagement_score = likes + comments

✅ 2. Ranking Query

RANK() window function used to list top posts.

✅ 3. Triggers

Automatic updates to like_count and comment_count on new likes/comments.

✅ 4. CSV Export

Export engagement data:

\COPY (SELECT * FROM post_engagement) TO 'post_engagement_report.csv' WITH CSV HEADER;

---

🧪 Sample Data Generation

Used Python + Faker to populate realistic:

• Users
• Posts
• Likes (ensuring no duplicates)
• Comments

✅ Python Packages Used:

pip install faker psycopg2-binary

---

📁 Deliverables

• ✅ SQL schema
• ✅ Views & queries
• ✅ Triggers for data updates
• ✅ Engagement reports (CSV)

---

📌 Folder Suggestions (if sharing the project)

postgresql_social_analytics/
├── schema.sql
├── generate.py
├── triggers.sql
├── views.sql
├── post_engagement_report.csv
└── README.md

---

🚀 Future Improvements

• Weekly active users report
• Top commenters
• REST API or dashboard UI

---