Advanced SQL Interview Questions for Software Engineer

Advanced-level SQL interview questions for Software Engineer positions.

1. How would you design a database schema for a social media feed?

Answer: Consider: Users table, Posts table with user_id FK, Follows table (follower_id, following_id), Likes/Comments as junction tables. For feed: denormalize into timeline table or use fan-out-on-write pattern. Index on followed_id and created_at.

CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR UNIQUE);

CREATE TABLE follows (
  follower_id INT REFERENCES users(id),
  following_id INT REFERENCES users(id),
  PRIMARY KEY (follower_id, following_id)
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Get feed for user 1
SELECT p.* FROM posts p
JOIN follows f ON p.user_id = f.following_id
WHERE f.follower_id = 1
ORDER BY p.created_at DESC LIMIT 50;

CREATE INDEX idx_posts_user_date ON posts(user_id, created_at DESC);