Social Features Analytics (Guilds, Friends) with Streaming SQL

Social features analytics with streaming SQL means your guild activity scores, friend network metrics, and social engagement indicators update in seconds as players interact — not in next-morning reports. RisingWave ingests social events from Kafka and maintains materialized views that let your liveops and design teams observe social health continuously, enabling faster interventions when communities go quiet or toxic.

Social Features Are the Retention Engine

Games with strong social features retain players significantly longer than solo experiences. Guilds, clans, friend lists, in-game chat, and cooperative challenges create obligation networks that keep players logging in even when the core content loop feels routine.

But to maintain and improve social features, you need to measure them. Questions like "which guilds are losing members fastest?", "are new players forming friend connections in their first week?", or "which social interaction types correlate with 30-day retention?" require fresh data, not weekly reports.

RisingWave lets you write these measurements as SQL views that update continuously from your event stream.

Ingesting Social Events

Create a source for the social event stream:

CREATE SOURCE social_events (
    event_id       VARCHAR,
    player_id      BIGINT,
    target_id      BIGINT,         -- friend, guild, or chat target
    guild_id       BIGINT,
    event_type     VARCHAR,        -- 'guild_join', 'guild_leave', 'friend_add',
                                   -- 'friend_remove', 'chat_message', 'coop_complete'
    event_time     TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'social-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Add a reference table for guild metadata:

CREATE TABLE guilds (
    guild_id       BIGINT PRIMARY KEY,
    guild_name     VARCHAR,
    region         VARCHAR,
    created_at     TIMESTAMPTZ
);

Guild Health Score in Real Time

Compute a rolling guild health score every 15 minutes, measuring member activity and social interactions:

CREATE MATERIALIZED VIEW guild_health_15min AS
SELECT
    guild_id,
    window_start,
    window_end,
    COUNT(DISTINCT player_id)                                       AS active_members,
    COUNT(*) FILTER (WHERE event_type = 'coop_complete')           AS coop_completions,
    COUNT(*) FILTER (WHERE event_type = 'chat_message')            AS chat_messages,
    COUNT(*) FILTER (WHERE event_type = 'guild_leave')             AS members_left,
    COUNT(*) FILTER (WHERE event_type = 'guild_join')              AS members_joined,
    -- Health score: activity minus churn signal
    (COUNT(*) FILTER (WHERE event_type = 'coop_complete') * 3
     + COUNT(*) FILTER (WHERE event_type = 'chat_message')
     - COUNT(*) FILTER (WHERE event_type = 'guild_leave') * 5)     AS health_score
FROM TUMBLE(social_events, event_time, INTERVAL '15 minutes')
WHERE guild_id IS NOT NULL
GROUP BY guild_id, window_start, window_end;

Friend Network Formation Analytics

Track whether new players are successfully forming friend connections within their first week — a key early retention indicator:

CREATE MATERIALIZED VIEW friend_formation_cohorts AS
SELECT
    se.player_id,
    window_start::DATE                                              AS cohort_week,
    COUNT(*) FILTER (WHERE se.event_type = 'friend_add')           AS friends_added,
    COUNT(*) FILTER (WHERE se.event_type = 'friend_remove')        AS friends_removed,
    COUNT(*) FILTER (WHERE se.event_type = 'coop_complete')        AS coops_with_friends,
    CASE
        WHEN COUNT(*) FILTER (WHERE se.event_type = 'friend_add') >= 3 THEN 'connected'
        WHEN COUNT(*) FILTER (WHERE se.event_type = 'friend_add') >= 1 THEN 'partially_connected'
        ELSE 'isolated'
    END AS social_status
FROM TUMBLE(social_events, event_time, INTERVAL '7 days') se
WHERE se.event_type IN ('friend_add', 'friend_remove', 'coop_complete')
GROUP BY se.player_id, window_start;

Players classified as isolated who have been active for seven days are prime candidates for matchmaking nudges, guild recommendations, or social onboarding prompts.

Detecting At-Risk Guilds

Join the health view with guild metadata to surface at-risk communities:

CREATE MATERIALIZED VIEW at_risk_guilds AS
SELECT
    h.guild_id,
    g.guild_name,
    g.region,
    h.window_start,
    h.active_members,
    h.members_left,
    h.health_score,
    CASE
        WHEN h.health_score < 0                           THEN 'critical'
        WHEN h.members_left > h.members_joined * 2       THEN 'high_churn'
        WHEN h.active_members < 3                        THEN 'inactive'
        ELSE 'healthy'
    END AS guild_status
FROM guild_health_15min h
JOIN guilds g ON h.guild_id = g.guild_id;

Stream this view to Kafka for a community management dashboard that pings moderators when a guild tips into critical status.

Social Analytics Approach Comparison

Practical Liveops Applications

Guild recruitment prompts: When a guild drops below five active members, trigger an in-game recruitment banner visible to high-activity players in the same region.

Social onboarding: When a new player completes their third session without adding a friend (social_status = 'isolated'), show a personalized friend suggestion using the guild health data.

Community event targeting: Send invitations for a cooperative raid event specifically to guilds flagged as high_churn — a shared challenge can reverse member exodus.

Toxic community detection: A sharp spike in guild_leave events combined with low chat_messages can signal a toxic leadership situation, prompting moderation review.

FAQ

Q: Can RisingWave handle the fan-out of guild events where one event affects thousands of members? A: Guild-level aggregations aggregate by guild_id, so fan-out is handled at the grouping level. Individual player-level views remain efficient because each player's events are independent rows in the stream.

Q: How do I track cross-guild interactions, like players who leave one guild and join another? A: Model each guild_leave and guild_join as separate events with the relevant guild_id. A materialized view can track player guild history by maintaining state per player_id across guild IDs.

Q: Our social events come from multiple game services. Can we merge them in RisingWave? A: Yes. Create multiple CREATE SOURCE statements for each Kafka topic or CDC source, then UNION ALL them in a view or write a combined materialized view that reads from all sources.

Q: What latency should we expect for guild health updates? A: With 15-minute tumbling windows, the maximum latency for a guild health update is 15 minutes (at window close). You can reduce this to 1–5 minutes by using HOP() windows that slide more frequently.

Q: Can we query historical guild health trends for design retrospectives? A: Yes. Add a JDBC or Iceberg sink to persist completed window results to your data warehouse. Real-time serving stays in RisingWave; historical analysis runs in the warehouse.

Measure What Makes Players Stay

Social feature analytics is the compass for community health. Without real-time measurement, guilds decay silently and players leave before you notice the signal.

• Start building with RisingWave now: https://docs.risingwave.com/get-started
• Discuss gaming analytics patterns with the community: https://risingwave.com/slack