Character·Data Scientist·Take-home Assignment
May 2026
SQL-heavy take-home style assessment for a DS role at Character, the AI character platform. Four tasks, all around the same schema (users, characters, conversations), escalating from basic aggregation to moving averages to open-ended safety analysis. No behavioral stuff, just pure SQL.
- Given a conversations table, write a SQL query to find the top 100 characters by total number of conversation engagements, returning character_id and engagement_count ordered descending.
- Write a SQL query to compute the ratio of unsafe characters (safety_flag = FALSE) to all characters as a single decimal value.
- For each day characters were created, compute the daily percentage of unsafe characters and a 7-day trailing moving average of that percentage. Return day, daily_unsafe_pct, and daily_unsafe_pct_ma7.
- Create a 2x2 breakdown showing engagement counts split by whether the character was safe or unsafe AND whether the conversation was safe or unsafe.
- By day, compute the count of unsafe conversation engagements, distinct unsafe users, total engagements, total distinct users, and derive unsafe engagement ratio and unsafe user ratio.
“Pretty standard GROUP BY with a COUNT and LIMIT 100.”