human
Schema:
film (Film_ID, Title, Studio, Director, Gross_in_dollar)
market (Market_ID, Country, Number_cities)
film_market_estimation (Estimation_ID, Low_Estimate, High_Estimate, Film_ID, Type, Market_ID, Year)
Please answer with SQL.
assistant
Sure, I understand.
human
Show me all different market ids in any film market estimation.
assistant
SELECT DISTINCT market_id FROM film_market_estimation;
human
Show me those with a low estimate above 10000.
assistant
SELECT market_id FROM film_market_estimation GROUP BY market_id HAVING MAX( Low_Estimate ) > 10000;
human
What is the average number of cities of them?
assistant
SELECT AVG ( T2.Number_cities ) FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID WHERE T1.Low_Estimate > 10000 ;