Log inSign up
Production Relational SchemaAdvanced

Social Media

Users, posts, comments, likes, follows and engagement.

6 Tables100K+ RecordsCTEWindow FunctionsRanking

Interactive SQL Playground & Query Runner

In-Browser Sandbox

Run realistic queries against the Social Media database. Write custom SQL or solve high-yield interview problems.

Interview Challenge:
easy

Post Engagement & Like Leaderboard

Count total likes received for each published post, including posts with zero likes.

LEFT JOINCOUNTGROUP BY
query.sql
Press Ctrl + Enter to execute

Entity Relationship (ER) & Foreign Key Architecture

Referential integrity mappings and table cardinalities

5 Key Relations
1:NPublishes Posts

users

member_id

posts

member_id

1:NReceives Comments

posts

post_id

comments

post_id

1:NAuthors Comment

users

member_id

comments

member_id

1:NLiked By

posts

post_id

likes

post_id

1:NFollows User

users

member_id

follows

follower_id

Relational Tables & Column Data Types

Field definitions, types, and primary keys for query construction

6 tables total
users
3 cols
PKmember_id
INT
handle
VARCHAR(50)
joined_on
DATE
posts
4 cols
PKpost_id
INT
PKmember_id
INT
posted_at
DATETIME
body
VARCHAR(220)
comments
5 cols
PKreply_id
INT
PKpost_id
INT
PKmember_id
INT
replied_at
DATETIME
body
VARCHAR(220)
likes
5 cols
PKreaction_id
INT
PKpost_id
INT
PKmember_id
INT
reaction_type
VARCHAR(20)
reacted_at
DATETIME
follows
3 cols
PKfollower_id
INT
PKfollowed_id
INT
followed_on
DATE
communities
3 cols
PKcommunity_id
INT
community_name
VARCHAR(90)
topic
VARCHAR(60)

Runnable DDL Setup & Seed Script

Run directly in MySQL Workbench, pgAdmin, DBeaver, or terminal

CREATE DATABASE IF NOT EXISTS studyflow_social;
USE studyflow_social;

CREATE TABLE users (
  member_id INT PRIMARY KEY,
  handle VARCHAR(50) NOT NULL UNIQUE,
  joined_on DATE
);

CREATE TABLE posts (
  post_id INT PRIMARY KEY,
  member_id INT,
  posted_at DATETIME,
  body VARCHAR(220),
  FOREIGN KEY (member_id) REFERENCES users(member_id)
);

CREATE TABLE comments (
  reply_id INT PRIMARY KEY,
  post_id INT,
  member_id INT,
  replied_at DATETIME,
  body VARCHAR(220),
  FOREIGN KEY (post_id) REFERENCES posts(post_id),
  FOREIGN KEY (member_id) REFERENCES users(member_id)
);

CREATE TABLE likes (
  reaction_id INT PRIMARY KEY,
  post_id INT,
  member_id INT,
  reaction_type VARCHAR(20),
  reacted_at DATETIME,
  FOREIGN KEY (post_id) REFERENCES posts(post_id),
  FOREIGN KEY (member_id) REFERENCES users(member_id)
);

CREATE TABLE follows (
  follower_id INT,
  followed_id INT,
  followed_on DATE,
  PRIMARY KEY (follower_id, followed_id),
  FOREIGN KEY (follower_id) REFERENCES users(member_id),
  FOREIGN KEY (followed_id) REFERENCES users(member_id)
);

CREATE TABLE communities (
  community_id INT PRIMARY KEY,
  community_name VARCHAR(90),
  topic VARCHAR(60)
);

INSERT INTO users VALUES
(1,'ira_codes','2024-01-10'),(2,'dev_sql','2024-03-02'),(3,'mira_data','2025-02-11'),
(4,'kabir_dev','2025-06-21'),(5,'nia_ml','2026-01-05');

INSERT INTO posts VALUES
(101,1,'2026-04-01 09:10:00','Learning window functions today.'),
(102,2,'2026-04-01 11:20:00','Joins finally clicked.'),
(103,1,'2026-04-02 08:30:00','Sharing a SQL practice tip.'),
(104,3,'2026-04-03 17:45:00','CTEs make reports easier to read.'),
(105,5,'2026-04-04 14:05:00','Started an analytics project.');

INSERT INTO comments VALUES
(1,101,2,'2026-04-01 10:00:00','Try LAG next.'),(2,101,3,'2026-04-01 12:00:00','Very useful.'),
(3,102,4,'2026-04-01 12:30:00','Same here.'),(4,104,1,'2026-04-03 18:10:00','Agreed.');

INSERT INTO likes VALUES
(1,101,2,'like','2026-04-01 09:40:00'),(2,101,3,'like','2026-04-01 09:50:00'),
(3,102,1,'like','2026-04-01 12:00:00'),(4,103,2,'like','2026-04-02 09:00:00'),
(5,104,5,'like','2026-04-03 18:00:00'),(6,104,1,'like','2026-04-03 18:20:00');

INSERT INTO follows VALUES
(1,2,'2025-01-10'),(1,3,'2025-03-05'),(2,1,'2025-04-01'),(3,1,'2026-01-20'),(5,1,'2026-02-12');

INSERT INTO communities VALUES
(1,'SQL Builders','Databases'),(2,'Analytics Lab','Data'),(3,'Interview Prep','Careers');