SQLeasyJoins~10m

A real estate agency wants to identify all active property listings along with...

Problem

A real estate agency wants to identify all active property listings along with their assigned agent details. Some properties might not have an assigned agent yet, and some agents might not have any active listings. Write a query to return all properties with their listing price, property type, agent name, and agent phone number. Include properties without agents, but exclude agents without any properties. Order by listing price descending, then by property_id ascending for ties.

Examples

Example 1

Example 2

Constraints

›Return columns: property_id, listing_price, property_type, agent_name, phone_number
›Include only active listings
›Include properties without assigned agents (agent_name and phone_number should be NULL)
›Exclude agents who have no active property listings
›Order by listing_price DESC, then property_id ASC

Reference solution

Reference solution available after you attempt the question.

Ready to solve it?

Start a session on Mockbit #50. Write your SQL, run it against real data, and get graded on correctness, efficiency, query structure, and business understanding.