11 Compass SQL Interview Questions (Updated 2024)

Updated on

August 11, 2024

At Compass, Data Scientists, Analysts, and Data Engineers rely on SQL to extract and analyze real estate data, including property listings and sales history, as well as manage data in the property recommendation system to match buyer preferences with available listings. For this reason, Compass includes SQL interview questionsin its hiring process to assess candidates' skills.

To help you prepare for the Compass SQL interview, here’s 11 Compass SQL interview questions in this blog.

Compass SQL Interview Questions

11 Compass SQL Interview Questions

SQL Question 1: Calculate the Average Listing Price per Month

Imagine you are a data analyst at Compass, a real estate company. Your task is to analyze the change in average listing price by date for the various types of properties, made by different agents, available on the platform.

The platform maintains a property listings database, with a structure as shown below:

Example Input:

listing_idagent_idlisting_dateproperty_typelisting_price
1012012022-01-13Condo250000
1022072022-02-14Single Family350000
1032012022-03-22Townhouse300000
1042082022-01-15Single Family400000
1052102022-02-22Condo200000

Your task is to write a SQL query to identify the average listing price, per month, per property type.

Answer:


In this query, we used the window function , this function traverse through each row of each partition (here partition is made by same month and property type) and computes the average listing price.

As a result, we'll get a list of average listing prices separated by month and property type.

p.s. Window functions show up pretty often during SQL interviews, so practice the 27+ window function questions on DataLemur

SQL Interview Questions on DataLemur

SQL Question 2: Employees Earning More Than Managers

Suppose you had a table of Compass employee salary data. Write a SQL query to find all employees who earn more than their own manager.

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

Solve this interview question 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 solution above is tough, you can find a step-by-step solution here: Well Paid Employees.

SQL Question 3: Can you describe the difference between a correlated and a non-correlated sub-query?

A correlated sub-query is one that is linked to the outer query and cannot be executed on its own. It uses the outer query to filter or transform data by referencing a column from the outer query, while the outer query uses the results of the inner query. On the other hand, a non-correlated sub-query is independent of the outer query and can be executed on its own. It does not reference any columns from the outer query and is used to retrieve additional data for the outer query.

Correlated sub-queries are slower to execute, as they have to be re-evaluated for each row of the outer query, while non-correlated sub-queries are faster, as they only have to be executed once.

Compass SQL Interview Questions

SQL Question 4: Real Estate Listing Database Design

Compass is a real-estate technology company that provides a platform for buying, selling and renting properties. Given this, you are tasked with designing a database to hold listing information. The information required includes listing id, list date, price, address, number of bedrooms, number of bathrooms, and the agent id associated with the property.

In addition, create a table for agents which includes agent id, first name, last name, and total listings managed. Once you have created the database design, answer the following question: Which agents had listed properties with prices above $1 million in the month of June, 2022?

Example Input:
listing_idlist_datepriceaddressbedroomsbathroomsagent_id
106/15/20221250000"123 Main St"321
206/18/2022850000"456 Oak St"212
306/20/20221750000"789 Pine St"431
407/02/2022950000"111 Elm St"323
Example Input:
agent_idfirst_namelast_nametotal_listings
1"John""Doe"50
2"Jane""Smith"45
3"Jim""Brown"60

Answer:


The above query joins the listings and agents tables on the agent_id column. It then filters on the price being above $1 million and the listing date being in June, 2022. The result will be the first and last names of those agents who meet these criteria.

SQL Question 5: How does an inner join differ from a full outer join?

An inner join only includes rows from both tables that satisfy the join condition, whereas a full outer join includes all rows from both tables, regardless of whether they match the join condition or not.

For example, suppose you had a table of Compass orders and Compass customers.

INNER JOIN: When there is a match in the shared key or keys, rows from both tables are retrieved. In this example, an between the Orders and Customers tables would retrieve rows where the in the Orders table matches the in the Customers table.

FULL OUTER JOIN: A retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, NULL values will be returned for the columns of the non-matching table.

SQL Question 6: Filter Customer Records Based on Residential Status and City

At Compass, we often need to filter customer records based on their residential status and their city. Using the customer records database,

  1. Write a SQL query to fetch the details of all customers who live in and are .
  2. Write a SQL query to fetch the details of all customers who do not live in and are not .

For this exercise, consider the table has the following fields: , , , , .

Here's a sample of the type of data in the table:

Example Input:
idnamecitystatuscreated_at
1John DoeNew YorkHomeowner07/01/2022 00:00:00
2Jane SmithSan FranciscoRenter07/01/2022 00:00:00
3Andrew FernandezBostonHomeowner07/02/2022 00:00:00
4Diane WatkinsNew YorkRenter07/02/2022 00:00:00
5George AndersonNew YorkHomeowner07/03/2022 00:00:00

Answer:

  1. To fetch the details of all customers who live in and are we can use the following query:

This query will return all rows from the table where the is and the is .

  1. To fetch the details of all customers who do not live in and are not we can use the following query:

This query will return all rows from the table where the is not and the is not .

SQL Question 7: What is database denormalization, and when is it a good idea to consider it?

Denormalization is the practice of altering a database schema in a way that breaks the normalization rules (1st, 2nd, 3rd normal forms).

A few reasons to denormalize a database:

  • Improved performance: Denormalization often reduces the the number of costly join operations that are needed to retrieve data. This is helpful when the database is being used for OLAP (Online Analytical Processing) use cases at Compass, as joins can be expensive and slow.

  • Scalability: Denormalization can be like a turbo boost for your database's scalability! By requiring less data to be read and processed when executing a query, denormalization can help your database handle a ton of queries without breaking a sweat.

  • Simplification: One way to simplify the design of a database is by using denormalization to reduce the number of tables and relationships that need to be managed. This can make the database easier to understand and maintain.

Of course, don't over-do the database denormalization magic – too much redundancy can increase data storage costs, and get complicated to manage if there's frequent commands that need to be run.

SQL Question 8: Finding the Average Price of a Specific Type of Property in Each Borough

As a real estate company, Compass has lots of data about property prices. For this question, we are interested in the average price of different types of properties in each borough.

Given the table that contains detailed information about the properties listed on the platform, write a SQL query to determine the average price for each type of property (e.g., condos, single family homes) for each borough in the city.

Here is example data from the table:

Example Input:
listing_idboroughproperty_typeprice
1Brooklyncondo600000
2Queenssingle family home800000
3Manhattancondo2000000
4Brooklynsingle family home700000
5Queenscondo500000
6Brooklyncondo700000
7Manhattansingle family home2500000
8Queenssingle family home850000

The result should be a table that groups by boroughs and property types and shows their average prices.

Example Output:
boroughproperty_typeavg_price
Brooklyncondo650000
Brooklynsingle family home700000
Queenscondo500000
Queenssingle family home825000
Manhattancondo2000000
Manhattansingle family home2500000

Answer:


This query first groups the listings by both borough and property type, then calculates the average price of the listings in each group. The result is the average price for each type of property in each borough.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for requiring grouping and aggregating items of a certain type or this Facebook Average Post Hiatus (Part 1) Question which is similar for calculating and grouping by different criteria.

SQL Question 9: Click-Through Conversion Rate Analysis for Compass

Compass, a real estate technology company, advertises its various property listings on their site. The marketing team is interested in knowing the click-through-conversion rate, i.e., the proportion of users who viewed an ad and subsequently added the property to their wishlist. Create a SQL statement to calculate the click-through-conversion rates for different properties.

Example Input:
view_iduser_idview_dateproperty_id
10112309/10/2021 00:00:0050001
10223409/10/2021 00:00:0090001
10312309/11/2021 00:00:0050001
10445609/12/2021 00:00:0030001
Example Input:
add_iduser_idadd_dateproperty_id
20112309/10/2021 00:00:0050001
20223409/11/2021 00:00:0090001
20367809/12/2021 00:00:0030001

Answer:


In the above PostgreSQL query, we're calculating the total number of unique views and wishlist adds for each property. The click-through-rate is calculated as the ratio of unique wishlist adds to unique views. The is used to ensure that we consider all views even if there were no corresponding adds to the wishlist.

To solve a related SQL interview question on DataLemur's free interactive SQL code editor, attempt this SQL interview question asked by Facebook:

Facebook Click-through-rate SQL Question

SQL Question 10: Can you describe the difference between a unique and a non-unique index?

Unique indexes help ensure that there are no duplicate key values in a table, maintaining data integrity. They enforce uniqueness whenever keys are added or changed within the index.

Non-unique indexes on the other hand, are used to improve query performance by maintaining a sorted order of frequently used data values, but they do not enforce constraints on the associated table.

Unique Indexes are blazing fast. Non unique indexes can improve query performance, but they are often slower because of their non-unique nature.

SQL Question 11: Grouped Aggregate Analysis of Property Listings

As a real estate brokerage firm, Compass frequently needs to analyze its inventory of property listings. Write a SQL query that calculates the average, minimum, and maximum listing price for each property type (e.g., house, apartment, condo) in each city.

Example Input:
listing_idcityproperty_typelisting_price
101New YorkApartment850000
102New YorkHouse2000000
103San FranciscoCondo1200000
104San FranciscoApartment1100000
105New YorkApartment1200000
106San FranciscoHouse3000000
Example Output:
cityproperty_typeavg_pricemin_pricemax_price
New YorkApartment10250008500001200000
New YorkHouse200000020000002000000
San FranciscoApartment110000011000001100000
San FranciscoCondo120000012000001200000
San FranciscoHouse300000030000003000000

Answer:


The output of this query shows the average, minimum, and maximum listing price for each type of property in each city. For example, in New York, the average price of an apartment is 1,025,000, with a range from 850,000 to 1,200,000.

Preparing For The Compass SQL Interview

The best way to prepare for a Compass SQL interview is to practice, practice, practice. In addition to solving the above Compass SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like Google, Uber, and Microsoft.

DataLemur Question Bank

Each exercise has multiple hints, full answers and most importantly, there is an online SQL code editor so you can right in the browser run your query and have it executed.

To prep for the Compass SQL interview you can also be a great idea to solve SQL problems from other tech companies like:

In case your SQL query skills are weak, forget about jumping right into solving questions – refresh your SQL knowledge with this free SQL for Data Analytics course.

SQL tutorial for Data Analytics

This tutorial covers topics including filtering groups with HAVING and advantages of CTEs vs. subqueries – both of which pop up frequently during Compass interviews.

Compass Data Science Interview Tips

What Do Compass Data Science Interviews Cover?

In addition to SQL interview questions, the other question categories covered in the Compass Data Science Interview include:

  • Probability & Statistics Questions
  • Python or R Programming Questions
  • Analytics and Product-Metrics Questions
  • ML Interview Questions
  • Behavioral Questions centered on Compass company values

Compass Data Scientist

How To Prepare for Compass Data Science Interviews?

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

  • 201 interview questions taken from FAANG, tech startups, and Wall Street
  • a crash course covering Product Analytics, SQL & ML
  • over 1000+ 5-star reviews on Amazon

Acing Data Science Interview

Also focus on the behavioral interview – prep for that using this Behavioral Interview Guide for Data Scientists. You should also look into the latest trends and innovations from Compass.

© 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