Scribd
Upload
6 views4 pages

Live Coding Exercise

The document contains a technical assessment focused on SQL queries related to real estate listings and cities. It includes specific questions that require writing SQL queries to find the most expensive listing, calculate average prices in cities, and analyze price differences. Additionally, it addresses obtaining the latest price for each listing and calculating average listing prices per month.

Uploaded by

tuanlee274
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views4 pages

Live Coding Exercise

The document contains a technical assessment focused on SQL queries related to real estate listings and cities. It includes specific questions that require writing SQL queries to find the most expensive listing, calculate average prices in cities, and analyze price differences. Additionally, it addresses obtaining the latest price for each listing and calculating average listing prices per month.

Uploaded by

tuanlee274
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Technical Assessment

Part 1
Using the following samples of tables, write a SQL query to answer the questions
below:

Questions
1. In which FOCUS CITY can you find the most expensive listing?

2. Calculate the average price of listings in each city, showing only cities where
the average price is greater than $1000.

Select c.city_name, avg(s.price) as exp_price

From listings s join cities c on s.city_id = c.city_id

Group by c.city_name

Having avg(s.price) > 1000


3. Calculate the difference between each listings’ price and the average price of
its city as a new column

Select s.listing_id, s.price - lag(s.price) over () as difference, avg(s.price) over


(partition by c.city_name)

From listings s left join cities c on s.city_id = c.city_id


Part 2
Using the following samples of tables, write a query to answer the questions below:

Questions
1. Get the latest price of each listing

2. You want to analyse price development. Calculate the average listing price per
month. Note: You need to take the price of a given listing in a given month.

Hint: The expected output should look like this:

You might also like