logo

10 eXp World Holdings SQL Interview Questions (Updated 2024)

Updated on

June 30, 2024

eXp World Holdings employees write SQL queries all the time for analyzing vast real estate transaction datasets and optimizing data retrieval processes. For this reason eXp World Holdings almost always asks SQL questions during interviews for Data Analyst, Data Science, and BI jobs.

So, to help you prep, here’s 10 eXp World Holdings SQL interview questions – how many can you solve?

eXp World Holdings SQL Interview Questions

10 eXp World Holdings SQL Interview Questions

SQL Question 1: Identify Top Spending Users at eXp World Holdings

For our residential real estate company, eXp World Holdings, it is important to recognize and value the customers that contribute the most to our business. As such, we wish to identify the top spending users within the last month, referred to in our business as VIP users. Given our databases of user transactions (), customer details (), and property details (), kindly develop a SQL query that retrieves the names and total spent of the top 10 users by their spending within the last month.

Example Input:
transaction_idcustomer_idproperty_idtransaction_datetransaction_amount
1500345208106/10/2022450000
2400892330106/13/2022375000
3900234405007/15/2022525000
4200345512007/20/2022610000
5800689208107/25/2022489000
Example Input:
customer_idcustomer_name
345John Doe
892Jane Smith
234Bob Johnson
689Mary Brown
Example Input:
property_idproperty_typeproperty_location
2081CondominiumChicago
3301Single Family HomeLos Angeles
4050DuplexSeattle
5120FourplexAustin

Answer:


This query first selects the customers who have made transactions in the past month. Then it calculates the total amount each customer spent, grouping by their name. It finally sorts the users in descending order of sum spent and limits the output to the top 10 users.

To practice a similar customer analytics SQL question where you can solve it interactively and have your SQL code instantly graded, try this Walmart Labs SQL Interview Question: Walmart Labs SQL Interview Question

Read about eXp Holdings new addition to their Tech tools package.

SQL Question 2: Well Paid Employees

Suppose there was a table of eXp World Holdings employee salary data. Write a SQL query to find the employees who earn more than their direct manager.

eXp World Holdings Example Input:

employee_idnamesalarydepartment_idmanager_id
1Emma Thompson38001
2Daniel Rodriguez2230110
3Olivia Smith800018
4Noah Johnson680028
5Sophia Martinez1750110
8William Davis70002NULL
10James Anderson40001NULL

Example Output:

employee_idemployee_name
3Olivia Smith

This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.

Test your SQL query for this problem interactively on DataLemur:

Employees Earning More Than Their Manager

Answer:

First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.


If the code above is confusing, you can find a detailed solution here: Employee Salaries Higher Than Their Manager.

SQL Question 3: What's the difference between a one-to-one and one-to-many relationship?

In database schema design, a one-to-one relationship between two entities is characterized by each entity being related to a single instance of the other. An example of this is the relationship between a US citizen and their social-security number (SSN) - each citizen has one SSN, and each SSN belongs to one person.

On the other hand, a one-to-many relationship is when one entity can be associated with multiple instances of the other entity. A teacher's relationship with their classes is an example of this - a teacher can teach many classes, but each class is only associated with one teacher.

eXp World Holdings SQL Interview Questions

SQL Question 4: Calculate the Monthly Agent Performance

Assume eXp World Holdings wants to analyze their agent's performance every month. They want to see how many properties their agents sold each month and the average selling price for those properties. They want this insight to see the monthly trend and understand which agents perform well.

Create a SQL query to calculate the total number of properties sold and the average selling price for each agent every month.

Here is the dataset:

Example Input:
sale_idagent_idproperty_idsale_pricesale_date
101A1P50012000002022-06-08
102A2P50021500002022-06-10
103A1P50033000002022-06-18
104A2P50042500002022-07-26
105A1P50053500002022-07-05

We will use function to get the month and years from dates in PostgreSQL:

Answer:


In this query, we first define the partition, which is “agent_id” and month and year of "sale_date". Then, we count the number of property sales by each agent in each month and also calculate the average selling price. Then ordering by year and month in ascending order, and the number of properties in descending order to see who sold the most.

To practice another window function question on DataLemur's free online SQL coding environment, solve this Google SQL Interview Question: Google SQL Interview Question

SQL Question 5: What are some ways you can identify duplicates in a table?

One way to find duplicatesis to use a clause and then use to find groups


You could also use the operator:


SQL Question 6: Filtering Customer Records

As an analyst at eXp World Holdings, you've been asked to generate a list of customers who own real estate in more than two states and are also involved in property management (indicated by a 'Yes' in the column). You are only interested in the customers who signed up in 2020.

Example Input:
customer_idsignup_dateproperty_managementreal_estate_count
1012020-01-02Yes3
1022020-02-02No4
1032019-12-28Yes2
1042020-10-01Yes5
1052020-11-01No1
Example Output:
customer_idsignup_dateproperty_managementreal_estate_count
1012020-01-02Yes3
1042020-10-01Yes5

Answer:


This PostgreSQL query selects all columns from the table where the column is 'Yes', the is more than 2, and the year of the is 2020. We use the function to get the year part from the column. The keyword is used to include multiple conditions, and all of them must be true for the row to be included in the output.

SQL Question 7: What does the SQL command do?

If you have two tables and want to retrieve only the rows that are present in both tables, just use the operator!

For example, let's use to find all of eXp World Holdings's Facebook video ads that are also being run on YouTube:


SQL Question 8: Average Property Price per City

eXp World Holdings is a real estate and technology company. You have been given two tables, and . The table contains details of the properties listed on the platform - including a , , , and . The table contains data of the sold properties, including a , , the , and the .

Write a SQL query that returns the average listing and sale price of properties for each city. Exclude cities where less than 5 properties have been sold.

Example Input
property_idcitystatelisting_price
101SeattleWA800000
102SeattleWA850000
103Los AngelesCA1200000
104AustinTX450000
105AustinTX530000
Example Input
sale_idproperty_idsale_pricesale_date
110182000006/08/2022 00:00:00
210290000006/10/2022 00:00:00
3103130000006/18/2022 00:00:00
410446000007/26/2022 00:00:00
510555000007/05/2022 00:00:00

Answer:


This SQL query returns the average listing and sale price of properties for each city, excluding cities with less than 5 sold properties. The query joins the properties and sales tables on the field. It then groups the results by city and gets the average and for each group. Finally, it filters out groups (in our case, cities) that have less than 5 sold properties using the clause.

SQL Question 9: Find Customers Whose First Name Contains the Letter "A"

As a data analyst at eXp World Holdings, you have been tasked to retrieve a list of customers whose first names contain the letter "A". The data is stored in a table in the database which contains three columns: , , and .

Example Input:
customer_idfirst_namecountry
1JohnUSA
2AliceCanada
3RobertUSA
4AnitaUK
5MichaelUSA

Your task is to write a SQL query that returns a list of customers whose first names contain the letter "A". The list should show , and .

Answer:


This PostgreSQL query works by filtering the table for rows where the contains the letter 'A'. The '%' signs are wildcard characters that match any number of characters. So this query will match any that has an 'A' anywhere in the name. This will return the customers Alice and Anita.

SQL Question 10: What sets UNION apart from 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.

eXp World Holdings SQL Interview Tips

The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. Besides solving the above eXp World Holdings SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Google, Uber, and Microsoft. DataLemur SQL and Data Science Interview Questions

Each problem on DataLemur has hints to guide you, fully explained answers along with a discussion board to see how others solved it and crucially, there is an online SQL code editor so you can instantly run your SQL query answer and have it graded.

To prep for the eXp World Holdings SQL interview it is also wise to solve SQL problems from other real estate and REIT companies like:

However, if your SQL coding skills are weak, forget about going right into solving questions – go learn SQL with this DataLemur SQL Tutorial.

SQL interview tutorial

This tutorial covers things like LEAD/LAG and rank window functions – both of these show up frequently in eXp World Holdings SQL interviews.

eXp World Holdings Data Science Interview Tips

What Do eXp World Holdings Data Science Interviews Cover?

In addition to SQL query questions, the other types of questions tested in the eXp World Holdings Data Science Interview are:

  • Probability & Stats Questions
  • Coding Questions in Python or R
  • Business Sense and Product-Sense Questions
  • Machine Learning and Predictive Modeling Questions
  • Resume-Based Behavioral Questions

eXp World Holdings Data Scientist

How To Prepare for eXp World Holdings Data Science Interviews?

The best way to prepare for eXp World Holdings Data Science interviews is by reading Ace the Data Science Interview. The book's got:

  • 201 Interview Questions from Microsoft, Amazon & startups
  • A Crash Course on SQL, AB Testing & ML
  • Great Reviews (1000+ reviews, 4.5-star rating)

Ace the Data Science Interview by Nick Singh Kevin Huo