8 JOYY SQL Interview Questions (Updated 2024)

Updated on

March 3, 2024

At JOYY, SQL is used all the damn time for analyzing user engagement with the platform and optimizing live-stream video delivery through targeted data queries and manipulation. Because of this, JOYY almost always evaluates jobseekers on SQL problems in interviews for Data Science and Data Engineering positions.

To help you ace the JOYY SQL interview, this blog covers 8 JOYY SQL interview questions – able to solve them?

8 JOYY SQL Interview Questions

SQL Question 1: Analyzing User Engagement

JOYY Inc is a social media platform company. They are interested in understanding the daily active users' engagement with their live streaming function. They define engagement as the total duration that a user spends watching live streams in a day.

They request you to write a SQL query to calculate the daily average engagement duration per user for the last seven days.

In the table, the column represents the time that a user starts watching a live stream, and the column represents the time a user stops watching a live stream.

Example Input:
engagement_iduser_idstart_timeend_time
112306/12/2022 18:30:0006/12/2022 18:45:00
212306/12/2022 19:10:0006/12/2022 20:00:00
345606/12/2022 14:50:0006/12/2022 15:20:00
412306/13/2022 08:00:0006/13/2022 08:30:00
545606/13/2022 16:00:0006/13/2022 16:40:00
678906/14/2022 20:00:0006/14/2022 21:50:00
Example Output:
dateavg_duration
06/12/202265
06/13/202235
06/14/2022110

Answer:


The query calculates the duration of each user engagement by subtracting the start_time from the end_time. To do this, the query uses to get the duration in seconds, then divide it by 60 to convert the duration to minutes. The query averages this duration per day across all user engagements that happened within the last seven days. It does this using .

For more window function practice, solve this Uber SQL Interview Question within DataLemur's interactive coding environment:

Uber Window Function SQL Interview Question

SQL Question 2: Filter Users Activity

As a data analyst at JOYY, you've been asked to analyze the user activities from a large user base data. Write a SQL query that filters the user activity database such that only records of users located in 'USA' and have 'active' status are included. You should further filter these records to those users who have spent more than 100 hours on the platform and have registered in the year 2020 or later.

Example Input:
user_idcountrystatushours_spentregistration_date
101USAactive1502020-11-07
102Canadaactive2002020-02-03
103USAinactive502021-01-25
104USAactive1102021-05-02
105Germanyactive1202020-03-05
106USAactive902021-09-01

Answer:


This query first filters the data based on the 'country' and 'status' column. It then filters the remaining data by comparing the 'hours_spent' column to 100 and the year part of the 'registration_date' to 2020.

The EXTRACT function is used in PostgreSQL to get the year part of the 'registration_date'. It will return records with registration_date in the year 2020 or later.

SQL Question 3: What does the constraint do?

A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.

Say for example you had sales analytics data from JOYY's CRM (customer-relationship management) tool.


The FOREIGN KEY constraint ensures that the data in the field of the "opportunities" table is valid, and prevents the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and can be used to ensure that data is not deleted from the accounts table if there are still references to it in the opportunities` table.

JOYY SQL Interview Questions

SQL Question 4: Analysis of Click-Through-Rate in JOYY Digital Ads Campaign

JOYY, a global video-based social media platform, has recently run a global ad campaign with a goal to increase user engagement on their platform. As a Data Analyst, your task is to analyze the click-through-rate (CTR) of these ads for a specified period. JOYY's ad data is split into two tables, and .

The table contains a record for each time an ad was displayed to a user. It has the following columns:

  • (unique identifier for each ad display instance)
  • (unique identifier for each user)
  • (unique identifier for each ad)
  • (date when the ad was displayed)

The table contains a record for each time a displayed ad was clicked by the user. It has the following columns:

  • (unique identifier for each ad click instance)
  • (unique identifier for the ad display instance that the click belongs to)
  • (date when the ad was clicked)

Example Input:

display_iduser_idad_iddisplay_date
9812748600106/01/2022 00:00:00
3765365442206/01/2022 00:00:00
6129245600106/02/2022 00:00:00
9257907442206/02/2022 00:00:00
4217689600106/02/2022 00:00:00

Example Input:

click_iddisplay_idclick_date
1123376506/01/2022 00:00:00
2342925706/02/2022 00:00:00
3452421706/03/2022 00:00:00

Answer:


This query first joins the and tables on the column, then groups the results by the column. The result is a table that shows the number of times each ad was displayed, the number of clicks each ad received, and the calculated click-through-rate for each ad.

To solve a similar problem about calculating rates, solve this TikTok SQL Interview Question within DataLemur's interactive coding environment: SQL interview question from TikTok

SQL Question 5: Describe the difference between UNION and UNION ALL.

Both and are used to combine the results of two or more SELECT statements into a single result set.

However, only includes one instance of a duplicate, whereas includes duplicates.

SQL Question 6: Filter Customer Records using LIKE

JOYY is a global social media platform that provides live streaming services. Bearing this in mind, write an SQL query that fetches the customer records whose names end with 'SONG'. As a Data Analyst at JOYY, you are required to dig into customer data and provide useful insights.

Sample Input:
customer_idfirst_namelast_nameemailsign_up_datecountry
1JohnSongjohnsong@joyy.com01/05/2022USA
2DaveLeedavelee@joyy.com03/10/2022Canada
3TimSongtimsong@joyy.com08/07/2022UK
4EmmaWoodemmawood@joyy.com05/02/2022Australia
5PeterJohnsonpeterjohnson@joyy.com16/09/2022USA
Example Output:
customer_idfirst_namelast_name
1JohnSong
3TimSong
5PeterJohnson

Answer:

You can use the SQL keyword for this problem to find records that match the string ending with 'SONG'. The keyword allows you to use wildcards as part of your search string. In SQL, the percent sign can be used as a wildcard representing zero, one, or multiple characters. The SQL query should look like this:


In this SQL statement, matches any string ending with 'SONG'. Thus, it fetches all customers whose last names end with 'SONG'.

SQL Question 7: Could you describe the function of UNION in SQL?

{#Question-7}

is used to combine the output of multiple statements into one big result!

For a concrete example, say you were a Data Analyst supporting the Sales Analytics team at JOYY, and data on potential sales leads lived in both Salesforce CRM and Hubspot. To write a query to analyze leads created after 2023 started, across both CRMs, you could use in the following way:


"

SQL Question 8: Find Monthly Active Users

JOYY, being a global video-based social media platform, focuses heavily on user engagement. As a Data Analyst at JOYY, you are tasked with finding the number of unique active users each month. Active users are considered as those who have posted any content (videos, comments etc.) during the month.

We will be using two tables: , and .

Example Input:
user_idsignup_datecountry
1102019-01-15Canada
2152019-06-27USA
3652019-09-10Australia
5272020-03-04Canada
6422021-01-20USA
Example Input:
post_iduser_idpost_date
41511102022-06-03
98232152022-06-15
62963652022-07-11
81535272022-07-23
71926422022-07-31

You need to write the query in PostgreSQL.

Answer:


In the answer, we are using PostgreSQL's function to extract year and month from column of table. Then, we are simply grouping by year and month and counting distinct - this will give the number of unique active users in each month. The clause is used to keep the result set ordered by Year and Month.

JOYY SQL Interview Tips

The best way to prepare for a JOYY SQL interview is to practice, practice, practice. In addition to solving the earlier JOYY SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Facebook, Google, and VC-backed startups. DataLemur SQL Interview Questions

Each problem on DataLemur has hints to guide you, full answers and crucially, there is an online SQL coding environment so you can easily right in the browser your SQL query and have it graded.

To prep for the JOYY SQL interview you can also be helpful to solve SQL problems from other tech companies like:

In case your SQL query skills are weak, don't worry about jumping right into solving questions – refresh your SQL knowledge with this DataLemur SQL Tutorial.

SQL tutorial for Data Scientists & Analysts

This tutorial covers SQL concepts such as filtering on multiple conditions using AND/OR/NOT and rank window functions – both of these show up often in JOYY SQL interviews.

JOYY Data Science Interview Tips

What Do JOYY Data Science Interviews Cover?

In addition to SQL query questions, the other types of questions covered in the JOYY Data Science Interview are:

JOYY Data Scientist

How To Prepare for JOYY Data Science Interviews?

To prepare for JOYY Data Science interviews read the book Ace the Data Science Interview because it's got:

  • 201 interview questions sourced from FAANG tech companies
  • a refresher covering Product Analytics, SQL & ML
  • over 900+ reviews on Amazon & 4.5-star rating

Ace the DS Interview

© 2024 DataLemur, Inc

Career Resources

Free 9-Day Data Interview Crash CourseFree SQL Tutorial for Data AnalyticsUltimate SQL Interview GuideAce the Data Job Hunt Video CourseAce the Data Science InterviewBest Books for Data Analysts