# 9 CoStar SQL Interview Questions (Updated 2024)

Updated on

February 14, 2024

At CoStar Group, SQL is used quite frequently for analyzing large real estate databases and powering their market analytics tool. Because of this, CoStar almost always evaluates jobseekers on SQL problems in interviews for Data Analyst, Data Science, and BI jobs.

As such, to help you ace the CoStar SQL interview, we've collected 9 CoStar Group SQL interview questions – how many can you solve?

## 9 CoStar Group SQL Interview Questions

### SQL Question 1: Calculate Average Listing Price by City and Month

As a real estate data company, CoStar collects information about property listings across various cities. Given a table 'Listings', write a SQL query to calculate the average listing price by city and month for the year 2020, and rank the cities by their average listing prices within each month.

Assume we are provided with the following 'Listings' table:

##### Example Input:

Note: Price here is in thousands.

listing_idcitylisting_dateprice
101New York2020-01-151500
102Chicago2020-01-20700
103Los Angeles2020-01-251300
104New York2020-02-101800
105Chicago2020-02-20800
106Los Angeles2020-02-251500
107New York2020-03-152100
108Chicago2020-03-20900
109Los Angeles2020-03-251700
##### Example Output:
mthcityavg_pricerank
1New York15002
1Chicago7003
1Los Angeles13001
2New York18002
2Chicago8003
2Los Angeles15001
3New York21001
3Chicago9003
3Los Angeles17002

In this solution, we use the window function to rank the cities according to the average listing price within each month. is used to create a separate ranking for each month, and is used to sort the average prices in descending order. The result set is then ordered by month and ranking order.

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

### SQL Question 2: Filter Customers Based on Subscription Status and Activity Level

CoStar has a database that records customer details, their subscription status and their interaction level with the application. CoStar would like you to write a SQL query to filter out active subscribers who have not interacted with the application in the last 30 days.

The 'customers' table is as follows:

##### Example Input:
customer_idsubscription_statuslast_interaction_date
1123active2022-08-10
2451active2022-09-05
3872inactive2022-08-20
5478active2022-07-10
6349active2022-09-15

Assume today's date is 2022-09-20.

You need to write a SQL query to find all active customers who have not interacted with the application in the last 30 days.

Here is a PostgreSQL query to solve this question:

This PostgreSQL query filters the 'customers' table to show the rows where the 'subscription_status' column is 'active' and 'last_interaction_date' is more than 30 days prior to today's date. The "INTERVAL '30 days'" command in PostgreSQL subtracts 30 days from the current date.

### SQL Question 3: What's the difference between relational and NoSQL databases?

A non-relational (NoSQL) database is any database that does not use the typical tabular format of rows and columns like in relational databases.

While knowing the four different types of NoSQL databases is beyond the scope of most Data Analyst & Data Science interviews, Data Engineers at CoStar should vaguely refresh these concepts:

• Document Databases – this database is designed for storing and querying retrieving document data (where each key is associated with a flexible document)
• Key-Value Stores – these databases uses keys where each key is associated with only one value in a collection (similar to a Python dictionary data structure!)
• Wide-Column Stores – this database uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row within the same table. Remember: "-With great flexibility comes great responsiblity-" – Batman if he was learning about NoSQL databases!
• Graph Stores – represents data in terms of the graph data structure, with nodes and edges between entities

### SQL Question 4: Average Price Per Square Foot for Listed Properties

As an analyst at CoStar, a leading provider of commercial real estate information, analytics and online marketplace, you are tasked with understanding key metrics about the listed properties. Calculate the average price per square foot for the listed properties.

##### Example Input:
property_idlisting_pricesquare_foot
1015000002000
1026000002500
1037500003000
1044000001800
1054500001800
##### Example Output:
avg_price_per_sqft
241.91

The above SQL command first calculates the price per square foot for each property by dividing the listing price by the square footage. The ::decimal is used to cast the results as a decimal type for more accurate division. The AVG function is then used to calculate the average of these values.

To practice a very similar question try this interactive Amazon Maximize Prime Item Inventory Question which is similar for calculating average based on square footage or this Alibaba Compressed Mean Question which is similar for calculating mean on a dataset.

### SQL Question 5: Can you explain the distinction 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.

To define a unique index in PostgreSQL, you can use the following syntax:

To define a non-unique index in PostgreSQL, you can use the following syntax:

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.

### SQL Question 6: Analyzing Click-Through Conversion Rates

CoStar, a commercial real estate information company, is interested in understanding the click-through-to-conversion rates of the products they list on their website. For a given time period, they want to know the proportion of users who viewed a product and subsequently added it to their cart.

Two tables contain relevant information:

1. - whenever a user views a product on the website, an entry is created in this table.
2. - when a user adds a product to their cart after viewing it, it is logged in this table.
##### Example Input:
click_iduser_idclick_timeproduct_id
1000110106/08/2022 00:00:002001
1000210206/10/2022 00:00:002002
1000310106/18/2022 00:00:002001
1000410307/26/2022 00:00:002003
1000510407/05/2022 00:00:002004
##### Example Input:
conversion_iduser_idconversion_timeproduct_id
2100110106/08/2022 00:05:002001
2100210506/10/2022 00:15:002005
2100310106/18/2022 00:07:002001
2100410607/26/2022 00:10:002006
2100510407/05/2022 00:06:002004
##### Example Output:
product_idclick-through-conversion_rate
2001100.00
20020.00
20030.00
2004100.00
20050.00
20060.00

The SQL query will first count the number of clicks on each product_id and the number of conversions, if any. Then it will calculate the click-through-conversion_rate by dividing the conversions by the clicks and multiplying by 100 to express it as a percentage.

The COALESCE function is used to handle cases where there are zero conversions for a product_id, in which case the click-through-conversion_rate would be NULL. This function changes those NULL values to 0.

In the sample output, 2001 and 2004 have a 100% click through conversion rate because every click led to a conversion. The remaining product_ids have a 0% rate because no corresponding conversions were found for those product clicks.

To practice a similar problem on DataLemur's free interactive coding environment, attempt this Meta SQL interview question:

### SQL Question 7: When would you use the constraint?

A is a column or set of columns in a table that references the primary key of another table. It is used to link the two tables together, and to ensure that the data in the foreign key column is valid.

The constraint helps to ensure the integrity of the data in the database by preventing the insertion of rows in the foreign key table that do not have corresponding entries in the primary key 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 primary key table if there are still references to it in the foreign key table.

For example, if you have a table of CoStar customers and an orders table, the customer_id column in the orders table could be a that references the id column (which is the primary key) in the CoStar customers table.

### SQL Question 8: Customer and Transaction Analysis

Given two tables from a database, and , write a SQL query to analyze customer metrics.

You need to find out the total number of transactions, total amount spent, and average amount spent per transaction for each customer. Join these tables on the 'customer_id'. Sort the result by total amount spent in descending order.

##### Example Input:
customer_idfirst_namelast_nameemailSignUpDate
001JohnDoejohn_doe@example.com2020-06-02
002JaneDoejane_doe@example.com2021-07-15
003BobSmithbob_smith@example.com2018-01-20
004AliceJohnsonalice_johnson@example.com2019-11-11
005CharlieBrowncharlie_brown@example.com2020-04-07
##### Example Input:
transaction_idcustomer_iddateamountproduct_id
10010012022-04-02200.50ABC123
10020022022-06-10150.75XYZ456
10030032022-07-2050.00ABC123
10040042022-08-3075.00XYZ456
10050012022-09-15250.00ABC123

This query first joins the and tables on the 'customer_id' field. Then, for each customer it counts the total number of transactions (total_transactions), sums the total amount spent (total_amount), and calculates the average amount per transaction (avg_amount). The results are grouped by each unique customer and sorted in descending order by the total amount spent.

Because joins come up frequently during SQL interviews, try an interactive Spotify JOIN SQL question:

### SQL Question 9: Rent Report Analysis

CoStar is a large company that provides data on the real estate market. You are a data analyst for CoStar and have been given a task involving their Rent Report data. Each monthly report provides information about the average rent and total units available for rental properties in a given city for a specific month and year. The reports cover the past 3 years.

##### rent_reports Example Input:
report_idcity_idreport_dateavg_rentunits_available
2091504508/01/202012502050
2387504509/01/202013251900
2753504508/01/202113902150
3059504509/01/202114502000
3402627308/01/202010001850
3719627309/01/202010251750
4282627308/01/202111001900
4657627309/01/202111501850

Your task is to write a SQL query to calculate the change in average rent and number of units available for each city from August 2020 to August 2021.

##### Example Output:
city_idrent_changeunit_change
5045140100
627310050

To answer this question, we would need to run a query where we calculate the difference between the average rent and total units available for each city for the months of August in 2020 and 2021.

Here is the PostgreSQL query that would accomplish that:

In this example, the query first selects the "city_id", then calculates the changes in "rent" and "units_available" between August 2020 and August 2021 for each city. The subqueries help retrieve the specific data for the calculation. We finally group by "city_id" and filter using a WHERE clause to support cities that existed in our timeframe. This results in a new table showing the change in rent and total units available for each city.

### Preparing For The CoStar SQL Interview

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the CoStar SQL interview is to solve as many practice SQL interview questions as you can! Beyond just solving the earlier CoStar SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Microsoft, Google, Amazon, and tech startups.

Each DataLemur SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there is an interactive coding environment so you can instantly run your query and have it checked.

To prep for the CoStar SQL interview it is also helpful to practice SQL questions from other tech companies like:

But if your SQL query skills are weak, don't worry about diving straight into solving questions – refresh your SQL knowledge with this SQL tutorial for Data Scientists & Analysts.

This tutorial covers SQL topics like different types of joins and Self-Joins – both of these show up routinely in SQL job interviews at CoStar.

### CoStar Group Data Science Interview Tips

#### What Do CoStar Data Science Interviews Cover?

Beyond writing SQL queries, the other topics to practice for the CoStar Data Science Interview are:

• Probability & Statistics Questions
• Python Pandas or R Coding Questions
• Product Data Science Interview Questions
• ML Interview Questions
• Behavioral Based Interview Questions

#### How To Prepare for CoStar Data Science Interviews?

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