Quick Overview

This question evaluates proficiency in SQL-based data manipulation and analytics, specifically aggregations, joins, date-based grouping, ratio calculations, and windowed moving averages to compute safety and engagement metrics.

Write SQL for character safety and engagement metrics

Company: Character.AI

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

You are working with a conversational product that has **users**, **AI characters**, and **conversations**. Write SQL queries for each of the tasks below. Assume **UTC** timestamps and that `day` means `DATE(created_at)`. ## Tables ### `users` - `user_id` BIGINT PRIMARY KEY - `created_at` TIMESTAMP ### `characters` - `character_id` BIGINT PRIMARY KEY - `created_by_user_id` BIGINT REFERENCES `users(user_id)` - `created_at` TIMESTAMP - `safety_flag` BOOLEAN - `TRUE` = safe, `FALSE` = unsafe ### `conversations` Each row represents a user engaging in a conversation associated with a character. - `conversation_id` BIGINT - `character_id` BIGINT REFERENCES `characters(character_id)` - `user_id` BIGINT REFERENCES `users(user_id)` - `created_at` TIMESTAMP - `safety_flag` BOOLEAN - `TRUE` = safe, `FALSE` = unsafe Notes: - A single `conversation_id` may appear multiple times if multiple users engaged. --- ## Task 1: Find the “top 100 characters” Define “top” as the characters with the **largest number of conversation engagements** (rows in `conversations`) across all time. Return: - `character_id` - `engagement_count` Order by `engagement_count` DESC and return only the top 100. --- ## Task 2: Compute the unsafe character ratio Compute the overall ratio of **unsafe characters** among all characters. Return: - `unsafe_character_ratio` (a decimal between 0 and 1) Where: - unsafe character = `characters.safety_flag = FALSE` --- ## Task 3: 7-day moving average of daily unsafe-character percentage For each `day` (based on `characters.created_at`), compute: 1) `daily_unsafe_pct` = (# characters created that day that are unsafe) / (total # characters created that day) 2) A **7-day trailing moving average** of `daily_unsafe_pct` (including the current day). Return: - `day` - `daily_unsafe_pct` - `daily_unsafe_pct_ma7` Order by `day` ascending. --- ## Task 4 (open-ended, but answer with SQL outputs) ### 4a) Relationship between unsafe characters and unsafe conversations Create a 2×2 breakdown of conversation engagements by: - character safety (`characters.safety_flag`) - conversation safety (`conversations.safety_flag`) Return: - `character_safety_flag` - `conversation_safety_flag` - `engagement_count` ### 4b) By day, unsafe-user engagement in unsafe conversations For each `day` (based on `conversations.created_at`), compute metrics related to **users who engaged in unsafe conversations** (`conversations.safety_flag = FALSE`). Return: - `day` - `unsafe_conversation_engagements` (count of rows where `conversations.safety_flag = FALSE`) - `unsafe_users` (distinct users engaging in unsafe conversations) - `total_engagements` (all conversation rows that day) - `total_users` (distinct users that day) - `unsafe_engagement_ratio` = `unsafe_conversation_engagements / total_engagements` - `unsafe_user_ratio` = `unsafe_users / total_users` Order by `day` ascending.

Overview: This question evaluates proficiency in SQL-based data manipulation and analytics, specifically aggregations, joins, date-based grouping, ratio calculations, and windowed moving averages to compute safety and engagement metrics.

Top 100 characters by engagement (conversation count)

You are given three tables: users, characters, and conversations. Each row in conversations represents one user engaging in a conversation about a character. For the date range 2025-05-01 to 2025-05-31 (inclusive), return the top 100 characters by total number of conversations. Output: - character_id - conversation_count Order by conversation_count DESC, then character_id ASC. If fewer than 100 characters exist in the range, return all of them.

Tables

users(user_id INT, created_at DATE)

characters(character_id INT, created_by_user_id INT, created_at DATE, safety_flag VARCHAR(10))

conversations(conversation_id INT, character_id INT, user_id INT, started_at DATE, safety_flag VARCHAR(10))

Hints

  1. Filter conversations by started_at in May 2025.
  2. Group by character_id and order by COUNT(*) descending.

Unsafe character ratio

Using the characters table, compute the unsafe character ratio across all characters. Definition: unsafe_character_ratio = (# of characters with safety_flag = 'unsafe') / (total # of characters) Return a single row with: - unsafe_characters - total_characters - unsafe_character_ratio (as a decimal)

Tables

users(user_id INT, created_at DATE)

characters(character_id INT, created_by_user_id INT, created_at DATE, safety_flag VARCHAR(10))

conversations(conversation_id INT, character_id INT, user_id INT, started_at DATE, safety_flag VARCHAR(10))

Hints

  1. Use conditional aggregation with SUM(CASE WHEN ...).
  2. Use NULLIF to avoid divide-by-zero.

7-day moving average of daily unsafe character percentage

For each calendar date from 2025-05-20 to 2025-05-28 (inclusive), compute: 1) daily_unsafe_pct = (# of unsafe characters created that day) / (total characters created that day) 2) daily_unsafe_pct_7d_ma = 7-day moving average of daily_unsafe_pct ending on that date (include the current day and up to 6 prior dates that have rows in the result). Return columns: - dt - daily_unsafe_pct - daily_unsafe_pct_7d_ma Notes: - Use the character created_at as the day. - Only output dates that have at least one character created (based on sample data, that is every date in the range).

Tables

users(user_id INT, created_at DATE)

characters(character_id INT, created_by_user_id INT, created_at DATE, safety_flag VARCHAR(10))

conversations(conversation_id INT, character_id INT, user_id INT, started_at DATE, safety_flag VARCHAR(10))

Hints

  1. Compute a daily ratio first, then apply a window AVG over the daily rows.
  2. Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-row trailing window.

Safety relationship matrix: character safety vs conversation safety (2x2)

For conversations started between 2025-05-01 and 2025-05-31 (inclusive), build a 2x2 matrix showing how conversation safety relates to the safety of the character being discussed. Join conversations to characters on character_id, then return: - character_safety_flag (from characters.safety_flag) - conversation_safety_flag (from conversations.safety_flag) - conversation_count Order by character_safety_flag ASC, conversation_safety_flag ASC.

Tables

users(user_id INT, created_at DATE)

characters(character_id INT, created_by_user_id INT, created_at DATE, safety_flag VARCHAR(10))

conversations(conversation_id INT, character_id INT, user_id INT, started_at DATE, safety_flag VARCHAR(10))

Hints

  1. This is a GROUP BY over two categorical flags.
  2. Make sure to join to characters to get the character safety flag.

Daily ratio of users engaging in unsafe conversations

For each day from 2025-05-25 to 2025-05-30 (inclusive), compute the ratio of users who engaged in at least one unsafe conversation that day. Definitions (per day): - total_unique_users = distinct users with any conversation that day - unsafe_unique_users = distinct users with at least one conversation with conversations.safety_flag = 'unsafe' that day - unsafe_user_ratio = unsafe_unique_users / total_unique_users Return columns: - dt - total_unique_users - unsafe_unique_users - unsafe_user_ratio Order by dt ASC.

Tables

users(user_id INT, created_at DATE)

characters(character_id INT, created_by_user_id INT, created_at DATE, safety_flag VARCHAR(10))

conversations(conversation_id INT, character_id INT, user_id INT, started_at DATE, safety_flag VARCHAR(10))

Hints

  1. Use COUNT(DISTINCT ...) for unique users.
  2. Count unsafe users with COUNT(DISTINCT CASE WHEN safety_flag='unsafe' THEN user_id END).

Loading coding console...