10 Newmark Group SQL Interview Questions (Updated 2024)
Newmark Group employees use SQL for analyzing real estate market trends and managing property transaction datasets. Because of this, Newmark Group almost always asks SQL questions in interviews for Data Analyst, Data Science, and BI jobs.
Thus, to help prep you for the Newmark Group SQL interview, here’s 10 Newmark Group SQL interview questions in this article.
10 Newmark Group SQL Interview Questions
SQL Question 1: Identify the Most Active Customers
Newmark Group wants to identify their most active customers to understand their purchasing behavior and preferences. An activity can be considered making a purchase. The company defines their "whale" users as customers who have made more than 100 purchases in a single month. Using this definition, write a SQL query to identify the customers who fit this criterion for the month of June.
Here's the input data representing the table:
Answer:
Here's the query:
Execution of above query on given "purchases" table will give us a list of user_ids along with their purchase counts for the month of June in 2022 who have made more than 100 purchases,i.e. are the "whale" users for the Newmark Group.
To practice a related customer analytics question on DataLemur's free online SQL coding environment, try this Microsoft SQL Interview problem:
SQL Question 2: Employees Earning More Than Managers
Given a table of Newmark Group employee salary information, write a SQL query to find employees who earn more than their direct boss.
Newmark Group 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.
Solve this problem directly within the browser 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 tough, you can find a step-by-step solution here: Highly-Paid Employees.
Read about Newmarks Group's global supply challenges that they're facing in 2024!
SQL Question 3: What's the difference between an inner and a full outer join?
A full outer join returns all rows from both tables, including any unmatched rows, whereas an inner join only returns rows that match the join condition between the two tables.
For a tangible example, suppose you had a table of Newmark Group orders and Newmark Group customers.
Here's a SQL inner join using the orders and customers tables:
This query will return rows from the orders and customers tables that have matching values. Only rows with matching values will be included in the results.
Here is an example of a using the orders and customers tables:
This query will return all rows from both the orders and customers tables, including any rows that do not have matching values. Any rows with null values for either table will be included in the results.
SQL Question 4: Analyzing Sales Revenue with Window Functions
Newmark Group, a real estate advisory company, needs to analyze their sales data to inform their revenue strategies. They would like to calculate the total sales revenue per month from each property type that they deal with, and rank these property types according to the collected revenue every month. Additionally, they would like to find out that cumulative sales revenue of each property type up to the particular month.
The tables and which are provided contains following information:
Example Input:
| sale_id | property_id | sale_date | sale_price |
|---|---|---|---|
| 101 | 1 | 01/02/2022 | 500000 |
| 102 | 2 | 01/22/2022 | 350000 |
| 103 | 3 | 01/30/2022 | 600000 |
| 104 | 2 | 02/01/2022 | 400000 |
| 105 | 1 | 03/01/2022 | 550000 |
Example Input:
| property_id | property_type |
|---|---|
| 1 | Apartment |
| 2 | Semi-detached House |
| 3 | Detached House |
Question is:
Write a SQL query to find out for each month, the total sales revenue for each property type along with their ranking based on the revenue, and total cumulative revenue for each property type until that month.
Answer:
In this query, a common table expression (CTE) is used to calculate the total monthly revenues for each property type. Then, these revenues are ranked per month. The second part of the query computes the cumulative revenue for each property type until that month using a window function. The result should display the total monthly revenue with corresponding ranks and cumulative revenue till date for each property type sorted by month and rank.
Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur
SQL Question 5: When would you use the constraint?
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 Newmark Group'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.
SQL Question 6: Average Property Value by City
As a data analyst at Newmark Group, a full-service real estate company, you are asked to analyze our property data. One key metric to report is the average property value by city.
Please use the provided table and write a SQL query to find the average property value in each city.
Example Input:
| property_id | city | property_value |
|---|---|---|
| 101 | New York | 1200000 |
| 102 | San Francisco | 1500000 |
| 103 | San Francisco | 1800000 |
| 104 | New York | 1300000 |
| 105 | San Francisco | 1400000 |
| 106 | Los Angeles | 1100000 |
| 107 | Los Angeles | 1000000 |
| 108 | New York | 1400000 |
Example Output:
| city | avg_property_value |
|---|---|
| New York | 1300000 |
| San Francisco | 1566666.67 |
| Los Angeles | 1050000 |
Answer:
The above SQL query calculates the average property value for each city from the property table. The GROUP BY statement is used to separate the data into groups, in this case, by city. The AVG function is then used to calculate the average property value in each of these groups.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for needing to group by and aggregate on a field or this Robinhood Cities With Completed Trades Question which is similar for requiring a successful grouping and calculation by city.
SQL Question 7: What distinguishes a left join from a right join?
Both types of joins in SQL help you retrieve data from multiple tables and merge the results into a single table.
To demonstrate the difference between a left join versus a right join, imagine you had two database tables: an table that contains data on Google Ads keywords and their bid amounts, and a table with information on product sales and the Google Ads keywords that drove those sales.
A retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the Sales table). If there is no match in the right table, values will be returned for the right table's columns.
A RIGHT JOIN retrieves all rows from the right table (in this case, the Sales table) and any matching rows from the left table (the table). If there is no match in the left table, values will be returned for the left table's columns.
SQL Question 8: Calculate the Click-Through Rate per Advertisement for Newmark Group
Newmark Group has an online advertisement campaign which directs views to their web pages. Each ad click and view of a product is logged. The company would like to understand the click-through rate (CTR) for each of its advertisements. CTR is calculated as the number of clicks the ad gets divided by the number of times the ad is shown, times 100.
Assume the relevant tables are and .
Example Input:
| ad_id | display_date |
|---|---|
| 101 | 01/15/2023 |
| 102 | 01/15/2023 |
| 103 | 01/16/2023 |
| 101 | 01/16/2023 |
| 102 | 01/17/2023 |
Example Input:
| ad_click_id | click_date | ad_id |
|---|---|---|
| 1 | 01/15/2023 | 101 |
| 2 | 01/15/2023 | 101 |
| 3 | 01/15/2023 | 102 |
| 4 | 01/16/2023 | 101 |
| 5 | 01/17/2023 | 102 |
Answer:
Here's an SQL query that could solve this problem:
This query first makes a left join of the advertisement_logs and click_logs tables on the condition that the ad_id and the date match. By doing so, we obtain all ads and their corresponding clicks if any. We then calculate the CTR as the count of distinct ad clicks divided by the count of distinct ad displays times 100. The result is grouped by ad_id so we get a CTR for each advertisement.
Example Output:
| ad_id | ctr |
|---|---|
| 101 | 50.00 |
| 102 | 33.33 |
| 103 | 0.00 |
This output shows the click-through rate for each advertisement. For example, the CTR for ad_id 101 is 50%, which means half the time it was displayed, it was clicked.
To solve a similar problem about calculating rates, try this SQL interview question from TikTok within DataLemur's online SQL coding environment:
SQL Question 9: Calculate the Average Sale Price of Each Property Type
Newmark Group provides commercial real estate services. As a data analyst, your task is to write a query to find out the average sale price for each type of property (e.g., Office, Retail, Industrial, etc.) that the company has sold in the past year.
We will start with creating a mock database table, .
Example Input:
We're aiming for an output that shows the average sale price for each type of property.
Example Output:
Answer:
The information in the table can be used to solve this problem. The PostgreSQL query is as follows:
In the above query, we are grouping the sales based on the column and calculating the average of the for each group. It will give us the average sale price for each type of property that the company sold in the past year.
SQL Question 10: What is database normalization?
To normalize a database, tables are divided into smaller, more specialized ones and relationships between them are defined via primary and foreign keys. This minimizes redundancy, making the database more flexible, scalable, and easier to maintain. Normalization also helps to ensure the accuracy of the data by reducing the likelihood of inconsistencies and errors.
Newmark Group SQL Interview Tips
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Newmark Group SQL interview is to solve as many practice SQL interview questions as you can!
Beyond just solving the earlier Newmark Group SQL interview questions, you should also solve the 200+ FAANG SQL Questions on DataLemur which come from companies like FAANG and tech startups.
Each problem on DataLemur has multiple hints, full answers and most importantly, there's an online SQL coding environment so you can easily right in the browser your SQL query answer and have it graded.
To prep for the Newmark Group SQL interview you can also be a great idea to practice SQL problems from other real estate and REIT companies like:
But if your SQL coding skills are weak, forget about diving straight into solving questions – strengthen your SQL foundations with this interactive SQL tutorial.
This tutorial covers SQL concepts such as finding NULLs and cleaning text data – both of these pop up frequently in Newmark Group SQL interviews.
Newmark Group Data Science Interview Tips
What Do Newmark Group Data Science Interviews Cover?
In addition to SQL interview questions, the other types of problems to prepare for the Newmark Group Data Science Interview include:
- Probability & Stats Questions
- Python or R Programming Questions
- Analytics and Product-Metrics Questions
- Machine Learning Questions
- Resume-Based Behavioral Questions
How To Prepare for Newmark Group Data Science Interviews?
To prepare for Newmark Group Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from Facebook, Google & startups
- a crash course covering Python, SQL & ML
- over 1000+ 5-star reviews on Amazon