An end-to-end relational database architecture and user engagement analytics project for ChatData, evaluating forum interactions across posts, comments, and user activity metrics using SQLite, SQL Magic, and Pandas.
ChatData operates an online community platform where understanding user interaction patterns is vital for product development. This project establishes a normalized relational schema, implements relational constraints (PK/FK), and executes structured SQL analytical workflows to extract engagement metrics.
- Relational Schema Design: Built an Entity-Relationship Diagram (ERD) mapping interactions across
Users,Posts, andComments. - Database Ingestion & Integrity: Constructed normalized tables in SQLite with primary keys and foreign key constraints enforcing referential integrity (
PRAGMA foreign_keys = ON). - Analytical Querying: Formulated complex single and multi-table SQL queries leveraging aggregation, grouping, filtering, subqueries, and window functions.
- Governance & Ethics: Audited data privacy risks and PII (Personally Identifiable Information) handling under GDPR compliance guidelines.
Users(18,412 records): User profiles, reputation scores, view metrics, and up/down voting history (PK: Id).Posts(42,234 records): Questions and answers, view counts, comment counts, score tags, and body character lengths (PK: Id,FK: OwnerUserId -> Users.Id).Comments(50,000 records): Threaded user replies and feedback scores (PK: Id,FK: PostId -> Posts.Id,FK: UserId -> Users.Id).
| Focus Area | Query Highlights | Analytical Insights |
|---|---|---|
| Comment Volume Distribution | COUNT(*) WHERE CommentCount = 0 vs > 1 |
Identified that over 51% of posts had 0 comments, highlighting areas to optimize community engagement mechanisms. |
| Top Engaged Users | GROUP BY Id ORDER BY TotalViews DESC LIMIT 5 |
Isolated super-users driving the highest platform visibility (top user accounting for 85k+ views). |
| Content Performance | LENGTH(Body) and score aggregations |
Correlated question text depth with answer acceptance rates and high community upvotes. |
- Language: Python 3, SQL
- Environment: Jupyter Notebook, Google Colab, IPython-SQL (SQL Magic)
- Database: SQLite3
- Libraries:
sqlite3,pandas,numpy,sqlalchemy
/data: Source interaction datasets (users.csv,posts.csv,comments.csv)./notebooks: Interactive analysis notebook (chatdata_sql_analysis.ipynb) with full SQL query logs./docs: Entity-Relationship Diagram (PDF) and executive lifecycle presentation./img: Visual schema and ERD exports.
# 1. Clone repository
git clone [https://github.com/rfilipeuk/chatdata-sql-analytics.git](https://github.com/rfilipeuk/chatdata-sql-analytics.git)
# 2. Navigate into project
cd chatdata-sql-analytics
# 3. Launch Notebook
jupyter notebook notebooks/chatdata_sql_analysis.ipynb