tiktok facebook | tiktok vo | tiktok DS | tiktok acre | Tiktok SDE

Let's take a look at the recent tiktok interviews for the recent tiktok interview Real questions, right? This time, the real question explanation class is here.

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. 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.

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

SAMPLE DATA

The table like this.

Questions and analyses

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)
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 summit 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)
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 spur on the website on a given day

SELECT
advertiser_id, date
TIMESTAMPDIFF()
TIMESTAMPDIFF(
SECOND, MIN(timestamp), TIMESTAMPDIFF(
MIN(timestamp), MAX(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. If you have more questions or need clarification, please feel free to ask!

After availing our interview Assistance serviceWe 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.

After our TiktokInterview support servicesCandidates passed this round of interviews successfully, let's look forward to the next interview questions together!

If you are interested in our services, feel free to leave a message to consult us.

azn7u2@gmail.com

See Full Bio