Coding questions · Character.AI

Write SQL for character safety and engagement metrics

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.

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:

daily_unsafe_pct = (# characters created that day that are unsafe) / (total # characters created that day)
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.

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	user_id
2025-05-10	1
2025-05-12	2
2025-05-20	3
2025-05-21	4
2025-05-25	5
2025-05-27	6

Table `characters`

column	type	constraints
character_id	INT	PRIMARY KEY, NOT NULL
created_by_user_id	INT	NOT NULL
created_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	created_at	created_by_user_id	safety_flag
101	2025-05-20	1	safe
102	2025-05-20	2	unsafe
103	2025-05-21	3	safe
104	2025-05-22	4	unsafe
105	2025-05-24	2	unsafe
106	2025-05-25	5	safe
107	2025-05-27	6	safe
108	2025-05-27	1	unsafe
109	2025-05-28	3	safe

Table `conversations`

column	type	constraints
conversation_id	INT	PRIMARY KEY, NOT NULL
character_id	INT	NOT NULL
user_id	INT	NOT NULL
started_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	conversation_id	safety_flag	started_at	user_id
101	1001	safe	2025-05-25	3
101	1002	unsafe	2025-05-25	4
101	1003	safe	2025-05-26	3
101	1004	unsafe	2025-05-27	5
101	1005	safe	2025-05-27	6
102	1006	unsafe	2025-05-26	3
102	1007	unsafe	2025-05-27	4
102	1008	safe	2025-05-28	5
103	1009	safe	2025-05-28	3
103	1010	unsafe	2025-05-28	6
104	1011	unsafe	2025-05-29	1
107	1012	safe	2025-05-30	2

Expected output

One row per character with at least one conversation in May 2025, showing the total number of conversations. Sorted by highest conversation_count.

character_id	conversation_count
101	5
102	3
103	2
104	1
107	1

Hints

Filter conversations by started_at in May 2025.
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)

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	user_id
2025-05-10	1
2025-05-12	2
2025-05-20	3
2025-05-21	4
2025-05-25	5
2025-05-27	6

Table `characters`

column	type	constraints
character_id	INT	PRIMARY KEY, NOT NULL
created_by_user_id	INT	NOT NULL
created_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	created_at	created_by_user_id	safety_flag
101	2025-05-20	1	safe
102	2025-05-20	2	unsafe
103	2025-05-21	3	safe
104	2025-05-22	4	unsafe
105	2025-05-24	2	unsafe
106	2025-05-25	5	safe
107	2025-05-27	6	safe
108	2025-05-27	1	unsafe
109	2025-05-28	3	safe

Table `conversations`

column	type	constraints
conversation_id	INT	PRIMARY KEY, NOT NULL
character_id	INT	NOT NULL
user_id	INT	NOT NULL
started_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	conversation_id	safety_flag	started_at	user_id
101	1001	safe	2025-05-25	3
101	1002	unsafe	2025-05-25	4
101	1003	safe	2025-05-26	3
101	1004	unsafe	2025-05-27	5
101	1005	safe	2025-05-27	6
102	1006	unsafe	2025-05-26	3
102	1007	unsafe	2025-05-27	4
102	1008	safe	2025-05-28	5
103	1009	safe	2025-05-28	3
103	1010	unsafe	2025-05-28	6
104	1011	unsafe	2025-05-29	1
107	1012	safe	2025-05-30	2

Expected output

Single-row aggregate showing counts of unsafe and total characters, plus the unsafe/total ratio.

total_characters	unsafe_character_ratio	unsafe_characters
9	0.4444444444	4

Hints

Use conditional aggregation with SUM(CASE WHEN ...).
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:

daily_unsafe_pct = (# of unsafe characters created that day) / (total characters created that day)
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).

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	user_id
2025-05-10	1
2025-05-12	2
2025-05-20	3
2025-05-21	4
2025-05-25	5
2025-05-27	6

Table `characters`

column	type	constraints
character_id	INT	PRIMARY KEY, NOT NULL
created_by_user_id	INT	NOT NULL
created_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	created_at	created_by_user_id	safety_flag
101	2025-05-20	1	safe
102	2025-05-20	2	unsafe
103	2025-05-21	3	safe
104	2025-05-22	4	unsafe
105	2025-05-24	2	unsafe
106	2025-05-25	5	safe
107	2025-05-27	6	safe
108	2025-05-27	1	unsafe
109	2025-05-28	3	safe

Table `conversations`

column	type	constraints
conversation_id	INT	PRIMARY KEY, NOT NULL
character_id	INT	NOT NULL
user_id	INT	NOT NULL
started_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	conversation_id	safety_flag	started_at	user_id
101	1001	safe	2025-05-25	3
101	1002	unsafe	2025-05-25	4
101	1003	safe	2025-05-26	3
101	1004	unsafe	2025-05-27	5
101	1005	safe	2025-05-27	6
102	1006	unsafe	2025-05-26	3
102	1007	unsafe	2025-05-27	4
102	1008	safe	2025-05-28	5
103	1009	safe	2025-05-28	3
103	1010	unsafe	2025-05-28	6
104	1011	unsafe	2025-05-29	1
107	1012	safe	2025-05-30	2

Expected output

One row per date with the daily unsafe percentage and its 7-day trailing moving average.

daily_unsafe_pct	daily_unsafe_pct_7d_ma	dt
0.50000000000000000000	0.50000000000000000000	2025-05-20
0E-20	0.25000000000000000000	2025-05-21
1.00000000000000000000	0.50000000000000000000	2025-05-22
1.00000000000000000000	0.62500000000000000000	2025-05-24
0E-20	0.50000000000000000000	2025-05-25
0.50000000000000000000	0.50000000000000000000	2025-05-27
0E-20	0.42857142857142857143	2025-05-28

Hints

Compute a daily ratio first, then apply a window AVG over the daily rows.
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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	user_id
2025-05-10	1
2025-05-12	2
2025-05-20	3
2025-05-21	4
2025-05-25	5
2025-05-27	6

Table `characters`

column	type	constraints
character_id	INT	PRIMARY KEY, NOT NULL
created_by_user_id	INT	NOT NULL
created_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	created_at	created_by_user_id	safety_flag
101	2025-05-20	1	safe
102	2025-05-20	2	unsafe
103	2025-05-21	3	safe
104	2025-05-22	4	unsafe
105	2025-05-24	2	unsafe
106	2025-05-25	5	safe
107	2025-05-27	6	safe
108	2025-05-27	1	unsafe
109	2025-05-28	3	safe

Table `conversations`

column	type	constraints
conversation_id	INT	PRIMARY KEY, NOT NULL
character_id	INT	NOT NULL
user_id	INT	NOT NULL
started_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	conversation_id	safety_flag	started_at	user_id
101	1001	safe	2025-05-25	3
101	1002	unsafe	2025-05-25	4
101	1003	safe	2025-05-26	3
101	1004	unsafe	2025-05-27	5
101	1005	safe	2025-05-27	6
102	1006	unsafe	2025-05-26	3
102	1007	unsafe	2025-05-27	4
102	1008	safe	2025-05-28	5
103	1009	safe	2025-05-28	3
103	1010	unsafe	2025-05-28	6
104	1011	unsafe	2025-05-29	1
107	1012	safe	2025-05-30	2

Expected output

Four rows (or fewer if some combinations do not occur) counting conversations by (character safety, conversation safety).

character_safety_flag	conversation_count	conversation_safety_flag
safe	5	safe
safe	3	unsafe
unsafe	1	safe
unsafe	3	unsafe

Hints

This is a GROUP BY over two categorical flags.
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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
created_at	DATE	NOT NULL

Sample data:

created_at	user_id
2025-05-10	1
2025-05-12	2
2025-05-20	3
2025-05-21	4
2025-05-25	5
2025-05-27	6

Table `characters`

column	type	constraints
character_id	INT	PRIMARY KEY, NOT NULL
created_by_user_id	INT	NOT NULL
created_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	created_at	created_by_user_id	safety_flag
101	2025-05-20	1	safe
102	2025-05-20	2	unsafe
103	2025-05-21	3	safe
104	2025-05-22	4	unsafe
105	2025-05-24	2	unsafe
106	2025-05-25	5	safe
107	2025-05-27	6	safe
108	2025-05-27	1	unsafe
109	2025-05-28	3	safe

Table `conversations`

column	type	constraints
conversation_id	INT	PRIMARY KEY, NOT NULL
character_id	INT	NOT NULL
user_id	INT	NOT NULL
started_at	DATE	NOT NULL
safety_flag	VARCHAR(10)	NOT NULL

Sample data:

character_id	conversation_id	safety_flag	started_at	user_id
101	1001	safe	2025-05-25	3
101	1002	unsafe	2025-05-25	4
101	1003	safe	2025-05-26	3
101	1004	unsafe	2025-05-27	5
101	1005	safe	2025-05-27	6
102	1006	unsafe	2025-05-26	3
102	1007	unsafe	2025-05-27	4
102	1008	safe	2025-05-28	5
103	1009	safe	2025-05-28	3
103	1010	unsafe	2025-05-28	6
104	1011	unsafe	2025-05-29	1
107	1012	safe	2025-05-30	2

Expected output

One row per day, computing distinct-user counts and the ratio of users exposed to unsafe conversations that day.

dt	total_unique_users	unsafe_unique_users	unsafe_user_ratio
2025-05-25	2	1	0.5
2025-05-26	1	1	1
2025-05-27	3	2	0.6666666667
2025-05-28	3	1	0.3333333333
2025-05-29	1	1	1
2025-05-30	1	0	0

Hints

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

Tables

users

characters

conversations

Task 1: Find the “top 100 characters”

Task 2: Compute the unsafe character ratio

Task 3: 7-day moving average of daily unsafe-character percentage

Task 4 (open-ended, but answer with SQL outputs)

4a) Relationship between unsafe characters and unsafe conversations

4b) By day, unsafe-user engagement in unsafe conversations

Top 100 characters by engagement (conversation count)

Table users

Table characters

Table conversations

Expected output

Hints

Unsafe character ratio

Table users

Table characters

Table conversations

Expected output

Hints

7-day moving average of daily unsafe character percentage

Table users

Table characters

Table conversations

Expected output

Hints

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

Table users

Table characters

Table conversations

Expected output

Hints

Daily ratio of users engaging in unsafe conversations

Table users

Table characters

Table conversations

Expected output

Hints

`users`

`characters`

`conversations`

Table `users`

Table `characters`

Table `conversations`

Table `users`

Table `characters`

Table `conversations`

Table `users`

Table `characters`

Table `conversations`

Table `users`

Table `characters`

Table `conversations`

Table `users`

Table `characters`

Table `conversations`