10 eXp World Holdings SQL Interview Questions (Updated 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?
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_id | customer_id | property_id | transaction_date | transaction_amount |
|---|---|---|---|---|
| 1500 | 345 | 2081 | 06/10/2022 | 450000 |
| 2400 | 892 | 3301 | 06/13/2022 | 375000 |
| 3900 | 234 | 4050 | 07/15/2022 | 525000 |
| 4200 | 345 | 5120 | 07/20/2022 | 610000 |
| 5800 | 689 | 2081 | 07/25/2022 | 489000 |
Example Input:
| customer_id | customer_name |
|---|---|
| 345 | John Doe |
| 892 | Jane Smith |
| 234 | Bob Johnson |
| 689 | Mary Brown |
Example Input:
| property_id | property_type | property_location |
|---|---|---|
| 2081 | Condominium | Chicago |
| 3301 | Single Family Home | Los Angeles |
| 4050 | Duplex | Seattle |
| 5120 | Fourplex | Austin |
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:
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_id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 | |
| 2 | Daniel Rodriguez | 2230 | 1 | 10 |
| 3 | Olivia Smith | 8000 | 1 | 8 |
| 4 | Noah Johnson | 6800 | 2 | 8 |
| 5 | Sophia Martinez | 1750 | 1 | 10 |
| 8 | William Davis | 7000 | 2 | NULL |
| 10 | James Anderson | 4000 | 1 | NULL |
Example Output:
| employee_id | employee_name |
|---|---|
| 3 | Olivia 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:
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.
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_id | agent_id | property_id | sale_price | sale_date |
|---|---|---|---|---|
| 101 | A1 | P5001 | 200000 | 2022-06-08 |
| 102 | A2 | P5002 | 150000 | 2022-06-10 |
| 103 | A1 | P5003 | 300000 | 2022-06-18 |
| 104 | A2 | P5004 | 250000 | 2022-07-26 |
| 105 | A1 | P5005 | 350000 | 2022-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:
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_id | signup_date | property_management | real_estate_count |
|---|---|---|---|
| 101 | 2020-01-02 | Yes | 3 |
| 102 | 2020-02-02 | No | 4 |
| 103 | 2019-12-28 | Yes | 2 |
| 104 | 2020-10-01 | Yes | 5 |
| 105 | 2020-11-01 | No | 1 |
Example Output:
| customer_id | signup_date | property_management | real_estate_count |
|---|---|---|---|
| 101 | 2020-01-02 | Yes | 3 |
| 104 | 2020-10-01 | Yes | 5 |
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_id | city | state | listing_price |
|---|---|---|---|
| 101 | Seattle | WA | 800000 |
| 102 | Seattle | WA | 850000 |
| 103 | Los Angeles | CA | 1200000 |
| 104 | Austin | TX | 450000 |
| 105 | Austin | TX | 530000 |
Example Input
| sale_id | property_id | sale_price | sale_date |
|---|---|---|---|
| 1 | 101 | 820000 | 06/08/2022 00:00:00 |
| 2 | 102 | 900000 | 06/10/2022 00:00:00 |
| 3 | 103 | 1300000 | 06/18/2022 00:00:00 |
| 4 | 104 | 460000 | 07/26/2022 00:00:00 |
| 5 | 105 | 550000 | 07/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_id | first_name | country |
|---|---|---|
| 1 | John | USA |
| 2 | Alice | Canada |
| 3 | Robert | USA |
| 4 | Anita | UK |
| 5 | Michael | USA |
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.
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.
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
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)