TikTok interview experience | TikTok OA | Detailed explanation of high-frequency SQL questions + preparation guide

TikTok OA has become more and more demanding recently, especially for data, back-end, and advertising-related positions. SQL questions have almost become a must, and the questions are basically based on real advertising business scenarios. We just recently held a TikTok OA. Based on our experience in many TikTok interviews and student coaching, we systematically dismantled this set of high-frequency SQL questions, covering common test points, complete problem-solving ideas, sample codes, and easy pitfalls to help everyone avoid detours during the OA stage.

First understand the data basics: table structure and sample data

To solve these problems, we must first understand what data we are facing. There is a TikTok here calledTtam_loggerThe table is dedicated to recording various operations when advertisers create ads. The fields are very clear:

• Date: Event date, mainly used for data partitioning, the format is YYYYMMDD (for example, 20220101 is January 1, 2022);
• Timestamp: Event time accurate to seconds, such as 2022-01-01 08:00:00;
• Advertiser_id: Advertiser’s unique ID that can distinguish different users;
• Event: Operation type, common ones include enter (enter page), next (next step), back (return), and submit (submit);
• Page: The page where the operation occurs, such as objective (target setting page), adgroup (ad group configuration page), creative (creative design page).

Let me give you a real sample data so that you can understand it at a glance:

Breaking down the topic step by step: ideas + SQL code, novices can understand it

Question 1: How many submission operations occurred on January 1, 2022?

This question is a scoring question, and the core is "screening + counting". First lock the date 20220101, then filter the records whose event type is submit, and finally count the number of records.

SQL code:

SELECT SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END) AS submit_count
FROM ttam_logger
WHERE date = '20220101';

Question 2: What is the overall submission rate for the week of January 8 – January 14, 2022?

Submission rate is not "submissions / total events"! Note here TikTok’s business definition: submission rate = total number of submission events for the week ÷ number of unique sessions for the week. The "independent session" is calculated based on "advertiser ID + date" - all operations by the same advertiser on the same day are counted as one session, no matter how many times he clicks next or back.

Problem solving steps:

1. Count the total number of submit events this week;
2. Count the number of unique combinations of "advertiser_id+date" in that week (i.e. The number of independent sessions);
3. Divide the two to 4 decimal places (accuracy to the thousandth is often required in business).

SQL code:

SELECT
  SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END) AS week_submit_count,
  COUNT(DISTINCT advertiser_id, date) AS unique_sessions,
  ROUND(SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END) / COUNT(DISTINCT advertiser_id, date), 4) AS submit_rate
FROM ttam_logger
WHERE date BETWEEN '20220108' AND '20220114';

Question 3: Of the advertisers who submitted from January 8th – 14th, how many also submitted last week (January 1st – 7th)?

This question tests "user overlap". The core is to find the intersection of submitted users in two time periods. Use CTE (common table expression) to extract the submitting users of two weeks respectively, then use JOIN to find common users, and finally count, the logic is quite clear.

WITH week1_submitters AS (
  -- Advertisers submitted last week (duplicate removed)
  SELECT DISTINCT advertiser_id
  FROM ttam_logger
  WHERE date BETWEEN '20220101' AND '20220107'
    AND event = 'submit'
),
week2_submitters AS (
  -- Advertisers submitted this week (duplicates removed)
  SELECT DISTINCT advertiser_id
  FROM ttam_logger
  WHERE date BETWEEN '20220108' AND '20220114'
    AND event = 'submit'
)
-- Count the number of advertisers who submitted in both weeks
SELECT COUNT(DISTINCT week1_submitters.advertiser_id) AS repeat_submit_advertisers
FROM week1_submitters
JOIN week2_submitters
  ON week1_submitters.advertiser_id = week2_submitters.advertiser_id;

Question 4: How long did an advertiser spend on the site on a given date?

The core of this problem is "calculating the operation time span of a single user in a single day" - the time difference from his first operation to the last operation of the day. Group by advertiser_id and date, take the minimum timestamp (first operation) and maximum timestamp (last operation) of each group, and then use the TIMESTAMPDIFF function to calculate the seconds.

SELECT
  advertiser_id,
  date,
  TIMESTAMPDIFF(SECOND, MIN(timestamp), MAX(timestamp)) AS seconds_spent
FROM ttam_logger
GROUP BY advertiser_id, date;

Practical tips: avoid these pitfalls

1. Date format: TikTok’s date field is in YYYYMMDD format. Do not write “2022-01-01” when filtering, otherwise the data will not be found;
2. Timing of deduplication: When counting the number of users or sessions, be sure to deduplicate (DISTINCT) first, otherwise the base number will be exaggerated, resulting in erroneous results;
3. Definition of submission rate: Different companies may have different definitions of "submission rate". If you are not sure during the interview, you must first confirm the business logic with the interviewer before writing SQL;
4. Duration unit: TIMESTAMPDIFF. You can choose SECOND (seconds), MINUTE (minutes), or HOUR (hours). Adjust according to needs. It is best to ask clearly about the output unit during the interview.

ProgramHelp 2025 Latest practical cases of landed students

This student is a Data Analyst with 3 years of experience. He has a solid technical foundation but often suffers from nervousness when facing the Online Assessment (OA) of Big Tech (such as TikTok, Netflix).

After finding ProgramHelp, we provided him with traceless assistance throughout the OA process. In this TikTok interview, with the help of our experts, the trainee not only solved all the SQL questions perfectly, but also received high praise from the interviewer for the clear code logic and zero plagiarism rate (Plagiarism Free), and successfully won the offer!

Jack Xu MLE | Microsoft Artificial Engineer

Ph.D. From Princeton University. He lives overseas and has worked in many major companies such as Google and Apple. The deep learning NLP direction has multiple SCI papers, and the machine learning direction has a Github Thousand Star⭐️ project.

See Full Bio