Recap - Real Estate Contractor

Part of the Fundamentals section of Coddy's SQL journey. Lesson 51 of 72.

Challenge

Medium

Available tables and columns:
real_estate: asset_id, value, date
potential: asset_id, forecast

Some analysts forecast what is going to be the growth for real estate assets. We want to check if their forecast is true.

For each asset in the real_estate table, calculate the ratio between the value of a newer date to the value of an older date.

For example, if there is an asset whose value in 2022-01-01 was 5 and in 2022-05-01 was 10 then that ratio is 10/5 = 2.

Ignore all of the comparisons that the day difference between the two dates is smaller than 101.

There are many dates for each asset so there are many ratios to compare. keep only the biggest ratio for each asset.

Finally, divide this ratio with the matching forecast from the potential table (call this field ratio_forecast) and sort the results in descending order.

The final result should have the asset_id and ratio_forecast.

Keep only the assets where the ratio_forecast is bigger than 1.

Note: To get a decimal solution from divide operation (/) you sometimes need to multiply the numerator by 1.0.

For example, instead of a / b use a * 1.0 / b to get the decimal result (when a and b are variables)

Try it yourself

-- Outer query: divide the number computed below by the matching one from the other table
SELECT potential.asset_id, ____ AS ratio_forecast
FROM (
    -- Inner query: pair the table with itself so an older row can be compared to a newer one
    SELECT r1.asset_id, ____ AS ratio
    FROM real_estate AS r1
    JOIN real_estate AS r2 ON ____
    -- Keep only the pairs that are far enough apart in time
    WHERE JULIANDAY(r2.date) - JULIANDAY(r1.date) > ____
    -- One result row per group, keeping only the single largest comparison
    GROUP BY r1.asset_id
) AS actual
JOIN potential ON ____
WHERE ratio_forecast > 1
ORDER BY ____

All lessons in Fundamentals

3Specific Return Format

Null values Sort Results Part 1 Sort Results Part 2 Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join Union Simplify queries, WITH keyword Recap - With QueriesRecap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE Function Aggregation Functions ROWS & RANGE Criterion

Practice on your own: SQL playground