
最近的tiktok面经,我们一起来看看近期的 tiktok面试 真题吧,这次的真题讲解课堂来啦。
Welcome, everyone. Today’s agenda involves exploring a comprehensive dataset related to advertiser activities. We will begin by dissecting its composition before discussing each question that arises from it in detail
TABLE STRUCTURE
At TTAMWAM we maintain a table named ttam_logger for recording events associated with ad creation. Below are the columns with their descriptions:
- date: The date of the event, used for data partitioning.
- timestamp: The precise time when the event occurred.
- advertiser_id: A unique identifier assigned to each advertiser.
- event: Describes the type of event (e.g., enter, next, back, submit).
- page: Indicates the specific page where the event occurred (e.g., objective, adgroup, creative).
SAMPLE DATA
The table like this:
date | timestamp | advertiser_id | event | page |
---|---|---|---|---|
20220101 | 2022-01-01 08:00:00 | 1001 | enter | objective |
20220101 | 2022-01-01 08:01:30 | 1001 | next | adgroup |
20220101 | 2022-01-01 08:02:45 | 1001 | next | creative |
20220101 | 2022-01-01 08:05:00 | 1001 | submit | creative |
20220101 | 2022-01-01 09:10:00 | 1002 | enter | objective |
20220101 | 2022-01-01 09:12:30 | 1002 | back | objective |
20220101 | 2022-01-01 09:15:00 | 1002 | submit | adgroup |
20220102 | 2022-01-02 10:00:00 | 1003 | enter | objective |
20220102 | 2022-01-02 10:20:00 | 1003 | submit | summary |
QUESTIONS AND ANALYSIS
Q1: How many submissions were made on January 1st, 2022?
- To determine this, we’ll filter the data for entries dated 20220101 with the event labeled ‘submit,’ and then tally the number of such occurrences.
Q2: What is the overall submit rate for the week of 20220108 ~ 20220114?
- First, determine the total number of submit events recorded during the week.
- Next, calculate the total number of unique sessions, considering each unique combination of advertiser_id and date.
- The submit rate is then computed as the ratio of submit events to the total number of unique sessions.
Q3: How many advertisers who submitted this week (20220108 ~ 20220114) also submitted last week (20220101 ~ 20220107)?
- We compile lists of advertisers who submitted during the first and second weeks respectively, then identify the common entries between these two lists.
Q4: How much time did an advertiser spend on the website on a given day?
- We calculate the time difference between the first and last event timestamps for each advertiser daily.
Let’s break these down step-by-step:
Q1: TOTAL NUMBER OF SUBMITS ON 20220101
To address this, a straightforward SQL query can be employed:
SELECT
SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END) AS week_submits,
COUNT(DISTINCT advertiser_id, date) AS sessions,
SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END)
/ COUNT(DISTINCT advertiser_id, date) AS submit_rate
FROM ttam_logger
WHERE date BETWEEN '20220108' AND '20220114';
This will provide you with the number of submit events occurring on the specified date.
Q2: OVERALL SUBMIT RATE FOR THE WEEK OF 20220108 ~ 20220114
First, find the count of submits in the week:
SELECT
COUNT(*) AS week_submits
FROM ttam_logger
WHERE date BETWEEN '20220108' AND '20220114'
AND event = 'submit';
Then, determine the count of distinct sessions:
SELECT
SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END) AS week_submits,
COUNT(DISTINCT advertiser_id, date) AS sessions,
SUM(CASE WHEN event = 'submit' THEN 1 ELSE 0 END)
/ COUNT(DISTINCT advertiser_id, date) AS submit_rate
FROM ttam_logger
WHERE date BETWEEN '20220108' AND '20220114';
Q3: ADVERTISERS WHO SUBMITTED IN BOTH WEEKS
WITH week1 AS (
SELECT DISTINCT advertiser_id
FROM ttam_logger
WHERE date BETWEEN '20220101' AND '20220107'
AND event = 'submit'
),
week2 AS (
SELECT DISTINCT advertiser_id
FROM ttam_logger
WHERE date BETWEEN '20220108' AND '20220114'
AND event = 'submit'
)
SELECT
COUNT(*) AS repeat_advertisers
FROM week1
JOIN week2 USING (advertiser_id);
Q4: TIME SPENT ON THE WEBSITE ON A GIVEN DAY
SELECT
advertiser_id,
date,
TIMESTAMPDIFF(
SECOND,
MIN(timestamp),
MAX(timestamp)
) AS seconds_spent
FROM ttam_logger
GROUP BY advertiser_id, date;
For every advertiser per day, determine the earliest and latest timestamps:
Afterwards, calculate the time interval between the first and last event for each advertiser daily.
By following these procedures, you’ll accurately address each question. If you have more questions or need clarification, please feel free to ask!
After availing our interview assistance service, the candidate cleared this round of interviews successfully. We eagerly anticipate tackling the next set of interview questions with you!
If you’re interested in our services, please feel free to reach out to us anytime for a consultation.
If you need our help with TikTok’s OA, please feel free to contact us.
经过我们的Tiktok面试辅助服务, 候选人顺利通过了本轮面试,我们一起期待下一次的面试题目吧~
如果您对我们的服务感兴趣,随时留言咨询我们。