Published on

Solving REAL TikTok SQL Interview Questions

Introduction

If you're aiming to get hired at TikTok or looking to practice SQL interview queries, you're in the right place. In this article, we will explore two types of SQL queries that are often asked during interviews at TikTok, specifically tailored for data analytics and data science positions. Let's jump right in and work through these queries together!

Question 1: User Confirmation on Sign-Up

For our first question, we assume you have a table of information about TikTok users who sign up for accounts and need to confirm their sign-up via email and text message. Each new user receives a text message to confirm their account upon sign-up.

Task: Display the user IDs of those who did not confirm their sign-up on the first day but confirmed on the second day.

Steps to Solve

  1. Understand the Tables:

    • Emails Table: Contains user ID and associated email ID.
    • Text Table: Contains email ID (which is a primary key) and action dates related to account confirmation via text.

  2. Join the Tables: Start by joining the emails and text tables on the email ID.

SELECT *
FROM emails AS E1
JOIN texts AS T1 ON T1.email_ID = E1.email_ID;
  1. Write the WITH Statement: Use a Common Table Expression (CTE) to make the query cleaner.
WITH signups AS (
  SELECT E1.user_ID, E1.sign_up_date, T1.action_date
  FROM emails AS E1
  JOIN texts AS T1 ON T1.email_ID = E1.email_ID
  WHERE sign_up_action = 'confirmed'
)
  1. Select the Desired Output: Now write the final SELECT statement.
SELECT user_ID
FROM signups
WHERE sign_up_action = 'confirmed'
AND action_date = sign_up_date + INTERVAL '1 day';

After running this SQL code, you should obtain the user IDs of those who confirmed their sign-ups on the second day.

Question 2: Activation Rate Calculation

For the second query, we look to find the activation rate of users in the emails table.

Task: Write a query to find the activation rate rounded to two decimal places.

Steps to Solve

  1. Count Total Emails: Use a COUNT to get the total number of users from the emails table.

  2. Count Confirmed Activations: Use SUM with a CASE statement to count how many users confirmed their account.

WITH total_signs AS (
  SELECT COUNT(email_ID) AS total_emails,
         SUM(CASE WHEN sign_up_action = 'confirmed' THEN 1 ELSE 0 END) AS confirmed_sign_ups
  FROM emails
)
  1. Calculate the Activation Rate: Finally, divide the confirmed sign-ups by the total emails, rounding the result.
SELECT ROUND(1.00 * confirmed_sign_ups / total_emails, 2) AS activation_rate
FROM total_signs;

Executing this query will provide you with the activation rate formatted to two decimal places.

Conclusion

These two SQL queries serve as excellent practice for candidates seeking roles in data analytics and data science at TikTok. By following the structured approach of using CTEs and applying aggregation functions, you can craft effective SQL statements that meet these challenges.

Keywords

  • TikTok
  • SQL Interview Questions
  • Data Analytics
  • Data Science
  • User Confirmation
  • Activation Rate
  • Common Table Expression (CTE)

FAQ

1. What is a Common Table Expression (CTE)?
A CTE provides a way to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and structure of SQL queries.

2. Why should I use a CASE statement in SQL?
CASE statements are useful for conditional logic within a SQL query, allowing you to perform different actions based on differing conditions.

3. How do I calculate the activation rate in SQL?
The activation rate can be calculated by dividing the number of confirmed activations by the total number of sign-ups, often representing this in a specific format (like rounding to two decimal places).

4. What is the significance of joining tables in SQL?
Joining tables allows you to combine rows from two or more tables based on related columns, enabling more complex queries and data analysis.

Read More