我们一起来看看近期的tiktok面试真题吧，这次的真题讲解课堂来啦。

Let's take a look at some recent TikTok interview questions. Welcome to this session where we'll explain these real interview questions!

Alright class, today we're going to work through a set of questions based on a table of data related to advertiser events. Let’s start by understanding the table structure and then proceed to the questions.

Table Structure

We have a table named ttam_logger that logs events related to ads creation. Here are the columns and their descriptions:

1. date: The date the event occurred, used for partitioning the data.
2. timestamp: The exact time the event occurred.
3. advertiser_id: A unique identifier for each advertiser.
4. event: The type of event that occurred (e.g., enter, next, back, submit).
5. page: The page where the event occurred (objective, adgroup, creative).

Sample Data

The table might look something like this:

date      | timestamp          | advertiser_id | event  | page
20220101  | 2022-01-01 13:30:07| 1001          | enter  | objective
20220101  | 2022-01-01 13:31:11| 1001          | next   | objective
20220101  | 2022-01-01 13:32:56| 1001          | next   | adgroup
20220102  | 2022-01-02 18:14:07| 1002          | enter  | objective
20220114  | 2022-01-14 03:14:07| 1003          | enter  | objective
20220114  | 2022-01-14 03:16:12| 1003          | next   | objective
20220114  | 2022-01-14 03:22:47| 1003          | next   | adgroup
20220114  | 2022-01-14 03:28:56| 1003          | submit | creative

Questions and Analysis

Q1: What is the total number of submits on 20220101?

• To find this, we filter the data where the date is 20220101 and the event is submit, then count the number of such entries.

Q2: What’s the overall submit rate for the week of 20220108 ~ 20220114?

• First, we find the total number of submit events in the given week.
• Then, we calculate the total number of unique sessions (considering each unique combination of advertiser_id and date).
• The submit rate is the number of submit events divided by the total number of unique sessions.

Q3: How many advertisers who submitted this week (20220108 ~ 20220114) also submitted last week (20220101 ~ 20220107)?

• We extract the list of advertisers who submitted in the first week and the list who submitted in the second week, and then find the intersection of these two lists.

Q4: How much time did an advertiser spend on the website on a given day?

• For each advertiser on each day, we calculate the time difference between their first and last event timestamp.

Let’s break these down step-by-step:

Q1: Total number of submits on 20220101

To solve this, you can use a simple SQL query:

SELECT COUNT(*) 
FROM ttam_logger 
WHERE date = '20220101' AND event = 'submit';

This will give you the count of submit events on the specified date.

Q2: Overall submit rate for the week of 20220108 ~ 20220114

First, find the number of submits in the week:

SELECT COUNT(*) 
FROM ttam_logger 
WHERE date BETWEEN '20220108' AND '20220114' AND event = 'submit';

Next, find the total number of unique sessions (a session is a unique combination of advertiser_id and date):

SELECT COUNT(DISTINCT advertiser_id, date) 
FROM ttam_logger 
WHERE date BETWEEN '20220108' AND '20220114';

Calculate the submit rate by dividing the number of submits by the number of sessions.

Q3: Advertisers who submitted in both weeks

First, find the list of advertisers who submitted in the first week:

SELECT DISTINCT advertiser_id 
FROM ttam_logger 
WHERE date BETWEEN '20220101' AND '20220107' AND event = 'submit';

Next, find the list for the second week:

SELECT DISTINCT advertiser_id 
FROM ttam_logger 
WHERE date BETWEEN '20220108' AND '20220114' AND event = 'submit';

Finally, find the intersection of these two lists:

SELECT COUNT(*)
FROM (
  SELECT advertiser_id 
  FROM ttam_logger 
  WHERE date BETWEEN '20220101' AND '20220107' AND event = 'submit'
) AS first_week
INNER JOIN (
  SELECT advertiser_id 
  FROM ttam_logger 
  WHERE date BETWEEN '20220108' AND '20220114' AND event = 'submit'
) AS second_week
ON first_week.advertiser_id = second_week.advertiser_id;

Q4: Time spent on the website on a given day

For each advertiser on each day, find the first and last timestamp:

SELECT advertiser_id, date, 
       MIN(timestamp) AS first_event, 
       MAX(timestamp) AS last_event 
FROM ttam_logger 
GROUP BY advertiser_id, date;

Then, calculate the time difference between the first and last event for each advertiser on each day. This can be done in a subsequent step depending on your SQL environment.

By following these steps, you will be able to answer each of the questions accurately. If you have any further questions or need additional clarification, feel free to ask!

After utilizing our interview assistance service, the candidate successfully passed this round of interviews. We look forward to the next set of interview questions together!

If you are interested in our services, feel free to leave us a message for consultation at any time.

经过我们的面试辅助服务，候选人顺利通过了本轮面试，我们一起期待下一次的面试题目吧~

如果您对我们的服务感兴趣，随时留言咨询我们。