Model a Social Network

Prompt

“Design tables to analyse content creation, engagement and the friend graph on our social app.”

Business questions

1. Daily posts, reactions, comments and shares by content type and country.
2. Engagement rate of posts = engagements / impressions, by content type and creator size.
3. Friend requests sent/accepted per day; acceptance rate; time to accept.
4. Number of friends per user (distribution); % of users with ≥ 10 friends (“activation”).
5. Feed: impressions per user per day, click-through by ranking position.
6. Mutual friends between two users; friend-of-friend recommendations input.

Processes and grains

ERD

erDiagram
    FCT_POSTS }o--|| DIM_USER : creator_key
    FCT_ENGAGEMENTS }o--|| FCT_POSTS : post_id
    FCT_ENGAGEMENTS }o--|| DIM_USER : actor_key
    FCT_FEED_IMPRESSIONS }o--|| FCT_POSTS : post_id
    FCT_FEED_IMPRESSIONS }o--|| DIM_USER : viewer_key
    FCT_FRIEND_REQUESTS }o--|| DIM_USER : sender_key
    FCT_FRIEND_REQUESTS }o--|| DIM_USER : receiver_key
    FRIENDSHIPS }o--|| DIM_USER : user_key
    FRIENDSHIPS }o--|| DIM_USER : friend_key
    FCT_POSTS {
        string post_id PK
        bigint creator_key FK
        timestamp created_at
        string content_type "text, photo, video, link"
        string visibility
        boolean has_link
    }
    FCT_ENGAGEMENTS {
        string engagement_id PK
        string post_id FK
        bigint actor_key FK
        string engagement_type "like, love, comment, share"
        timestamp created_at
        boolean is_removed "unlike"
    }
    FCT_FEED_IMPRESSIONS {
        string feed_request_id
        string post_id FK
        bigint viewer_key FK
        int position
        timestamp shown_at
        int dwell_ms
        boolean clicked
        string ranking_model_version
    }
    FRIENDSHIPS {
        bigint user_key FK
        bigint friend_key FK
        timestamp since
    }
    FCT_FRIEND_REQUESTS {
        string request_id PK
        bigint sender_key FK
        bigint receiver_key FK
        timestamp sent_at
        timestamp responded_at
        string outcome "accepted, declined, pending"
    }
    DIM_USER {
        bigint user_key PK
        string user_id
        string country
        string age_band "mini-dimension candidate"
        date signup_date
        string creator_tier "SCD2"
    }

Key design decisions

1. Symmetric relationships stored as two directed edges. (A, B) and (B, A) make “friends of user X” a simple WHERE user_key = X and friend counts a GROUP BY user_key, with no OR conditions. Cost: 2× rows. The alternative (one row with user_low < user_high) halves storage but complicates every query.
2. Friend requests as an accumulating snapshot: sent → responded, with time-to-accept as a derived fact. The friendship edge table is the current state; keep history via friendship_events (created/removed) if unfriending analysis matters.
3. Engagements are events that can be undone (unlike). Keep is_removed or a separate removal event; metrics must define whether “likes” means net or gross.
4. Engagement rate needs impressions: store numerator and denominator facts, compute the ratio after aggregation. Never average per-post rates without weighting.
5. Feed impressions are the largest table (tens of billions/day). Partition by date, cluster by viewer; consider sampling or daily aggregates (fct_post_daily with impressions/engagements per post per day) for most analysis.
6. Creator size for Q2 = follower/friend count bucket at post time: SCD2 creator_tier or snapshot the count on the post row.

Sample queries

-- Q4 friend count distribution (directed edges make this a single GROUP BY)
SELECT friend_bucket, COUNT(*) AS users FROM (
  SELECT u.user_key,
         CASE WHEN COUNT(f.friend_key) = 0 THEN '0' WHEN COUNT(f.friend_key) < 10 THEN '1-9'
              WHEN COUNT(f.friend_key) < 100 THEN '10-99' ELSE '100+' END AS friend_bucket
  FROM dim_user u LEFT JOIN friendships f ON f.user_key = u.user_key
  GROUP BY u.user_key
) GROUP BY friend_bucket;

-- Q6 mutual friends of users 1 and 2
SELECT a.friend_key FROM friendships a JOIN friendships b ON a.friend_key = b.friend_key
WHERE a.user_key = 1 AND b.user_key = 2;

Follow-up questions

Rubric

• Separate facts for posts, engagements, impressions with clear grains
• Symmetric friendship modelling choice explained
• Friend request lifecycle (accumulating snapshot)
• Engagement rate computed from additive components
• Handling of undo events (unlike, unfriend)
• Scale considerations for impressions and graph queries