Data Analyst Interview Questions & Answers: Set 17, Real Estate & Property

SQL

1. 1.Calculate average price per square foot by area and property type.

   Hide answer

   Answer: Divide total price by total square feet per group, not the average of row-level ratios, so large and small flats are weighted correctly. Assume sales(listing_id, area, property_type, sale_price, size_sqft). Filter out zero or null sizes to avoid division errors. NULLIF is a safety net in case a group has zero total size. Round for readability and sort so the most expensive segments appear first.

   SELECT area,
          property_type,
          COUNT(*) AS n_sales,
          ROUND(SUM(sale_price) / NULLIF(SUM(size_sqft), 0), 2)
            AS avg_price_per_sqft
   FROM sales
   WHERE size_sqft > 0
   GROUP BY area, property_type
   ORDER BY avg_price_per_sqft DESC;

2. 2.Find listings that stayed unsold for more than 90 days.

   Hide answer

   Answer: Compare the listing date with the sale date, or with today if the listing is still unsold. Assume listings(listing_id, area, listed_date, sold_date, status). Unsold means sold_date IS NULL, so days on market is CURRENT_DATE minus listed_date. In PostgreSQL, subtracting two DATE values returns an integer number of days. Trap: if you only check sold listings you miss exactly the ones you care about. Exclude withdrawn listings if the business wants only active stock.

   SELECT listing_id,
          area,
          listed_date,
          CURRENT_DATE - listed_date AS days_on_market
   FROM listings
   WHERE sold_date IS NULL
     AND status = 'active'
     AND CURRENT_DATE - listed_date > 90
   ORDER BY days_on_market DESC;