Database schema:

CREATE TABLE country (
	country_id INTEGER NOT NULL -- example: ['129', '128'],
	country_iso_code TEXT DEFAULT NULL -- example: ['AE', 'AF'],
	country_name TEXT DEFAULT NULL -- example: ['Afghanistan', 'United Arab Emirates'],
	PRIMARY KEY (country_id)
)


CREATE TABLE department (
	department_id INTEGER NOT NULL -- example: ['2', '1'],
	department_name TEXT DEFAULT NULL -- example: ['Directing', 'Camera'],
	PRIMARY KEY (department_id)
)


CREATE TABLE gender (
	gender_id INTEGER NOT NULL -- example: ['0', '1'],
	gender TEXT DEFAULT NULL -- example: ['Unspecified', 'Female'],
	PRIMARY KEY (gender_id)
)


CREATE TABLE genre (
	genre_id INTEGER NOT NULL -- example: ['14', '12'],
	genre_name TEXT DEFAULT NULL -- example: ['Adventure', 'Fantasy'],
	PRIMARY KEY (genre_id)
)


CREATE TABLE keyword (
	keyword_id INTEGER NOT NULL -- example: ['30', '65'],
	keyword_name TEXT DEFAULT NULL -- example: ['holiday', 'individual'],
	PRIMARY KEY (keyword_id)
)


CREATE TABLE language (
	language_id INTEGER NOT NULL -- example: ['24575', '24574'],
	language_code TEXT DEFAULT NULL -- example: ['sv', 'en'],
	language_name TEXT DEFAULT NULL -- example: ['English', 'svenska'],
	PRIMARY KEY (language_id)
)


CREATE TABLE language_role (
	role_id INTEGER NOT NULL -- example: ['2', '1'],
	language_role TEXT DEFAULT NULL -- example: ['Spoken', 'Original'],
	PRIMARY KEY (role_id)
)


CREATE TABLE movie (
	movie_id INTEGER NOT NULL -- example: ['5', '11'],
	title TEXT DEFAULT NULL -- example: ['Four Rooms', 'Star Wars'],
	budget INTEGER DEFAULT NULL -- example: ['4000000', '11000000'],
	homepage TEXT DEFAULT NULL -- example: ['', 'http://www.starwars.com/films/star-wars-episode-iv'],
	overview TEXT DEFAULT NULL -- example: ["It's Ted the Bellhop's first night on the job...an", 'Princess Leia is captured and held hostage by the '],
	popularity REAL DEFAULT NULL -- example: ['22.87623', '126.393695'],
	release_date DATE DEFAULT NULL -- example: ['1995-12-09', '1977-05-25'],
	revenue INTEGER DEFAULT NULL -- example: ['775398007', '4300000'],
	runtime INTEGER DEFAULT NULL -- example: ['121', '98'],
	movie_status TEXT DEFAULT NULL -- example: ['Rumored', 'Released'],
	tagline TEXT DEFAULT NULL -- example: ['A long time ago in a galaxy far, far away...', 'Twelve outrageous guests. Four scandalous requests'],
	vote_average REAL DEFAULT NULL -- example: ['6.5', '8.1'],
	vote_count INTEGER DEFAULT NULL -- example: ['6624', '530'],
	PRIMARY KEY (movie_id)
)


CREATE TABLE movie_cast (
	movie_id INTEGER DEFAULT NULL -- example: ['559', '285'],
	person_id INTEGER DEFAULT NULL -- example: ['114', '85'],
	character_name TEXT DEFAULT NULL -- example: ['Will Turner', 'Captain Jack Sparrow'],
	gender_id INTEGER DEFAULT NULL -- example: ['2', '1'],
	cast_order INTEGER DEFAULT NULL -- example: ['0', '1'],
	CONSTRAINT fk_movie_cast_gender FOREIGN KEY(gender_id) REFERENCES gender (gender_id),
	CONSTRAINT fk_movie_cast_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id),
	CONSTRAINT fk_movie_cast_person FOREIGN KEY(person_id) REFERENCES person (person_id)
)


CREATE TABLE movie_company (
	movie_id INTEGER DEFAULT NULL -- example: ['5', '11'],
	company_id INTEGER DEFAULT NULL -- example: ['59', '14'],
	CONSTRAINT fk_movie_company_production_company FOREIGN KEY(company_id) REFERENCES production_company (company_id),
	CONSTRAINT fk_movie_company_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id)
)


CREATE TABLE movie_crew (
	movie_id INTEGER DEFAULT NULL -- example: ['559', '285'],
	person_id INTEGER DEFAULT NULL -- example: ['1704', '120'],
	department_id INTEGER DEFAULT NULL -- example: ['2', '1'],
	job TEXT DEFAULT NULL -- example: ['Director', 'Director of Photography'],
	CONSTRAINT fk_movie_crew_department FOREIGN KEY(department_id) REFERENCES department (department_id),
	CONSTRAINT fk_movie_crew_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id),
	CONSTRAINT fk_movie_crew_person FOREIGN KEY(person_id) REFERENCES person (person_id)
)


CREATE TABLE movie_genres (
	movie_id INTEGER DEFAULT NULL -- example: ['5', '11'],
	genre_id INTEGER DEFAULT NULL -- example: ['80', '35'],
	CONSTRAINT fk_movie_genres_genre FOREIGN KEY(genre_id) REFERENCES genre (genre_id),
	CONSTRAINT fk_movie_genres_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id)
)


CREATE TABLE movie_keywords (
	movie_id INTEGER DEFAULT NULL -- example: ['5', '11'],
	keyword_id INTEGER DEFAULT NULL -- example: ['613', '612'],
	CONSTRAINT fk_movie_keywords_keyword FOREIGN KEY(keyword_id) REFERENCES keyword (keyword_id),
	CONSTRAINT fk_movie_keywords_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id)
)


CREATE TABLE movie_languages (
	movie_id INTEGER DEFAULT NULL -- example: ['5', '11'],
	language_id INTEGER DEFAULT NULL -- example: ['24575', '24574'],
	language_role_id INTEGER DEFAULT NULL -- example: ['2', '1'],
	CONSTRAINT fk_movie_languages_language FOREIGN KEY(language_id) REFERENCES language (language_id),
	CONSTRAINT fk_movie_languages_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id),
	CONSTRAINT fk_movie_languages_language_role FOREIGN KEY(language_role_id) REFERENCES language_role (role_id)
)


CREATE TABLE person (
	person_id INTEGER NOT NULL -- example: ['2', '1'],
	person_name TEXT DEFAULT NULL -- example: ['George Lucas', 'Mark Hamill'],
	PRIMARY KEY (person_id)
)


CREATE TABLE production_company (
	company_id INTEGER NOT NULL -- example: ['2', '1'],
	company_name TEXT DEFAULT NULL -- example: ['Lucasfilm', 'Walt Disney Pictures'],
	PRIMARY KEY (company_id)
)


CREATE TABLE production_country (
	movie_id INTEGER DEFAULT NULL -- example: ['5', '11'],
	country_id INTEGER DEFAULT NULL -- example: ['131', '214'],
	CONSTRAINT fk_production_country_country FOREIGN KEY(country_id) REFERENCES country (country_id),
	CONSTRAINT fk_production_country_movie FOREIGN KEY(movie_id) REFERENCES movie (movie_id)
)

Question: What is the average revenue of American movies in 2006?
Evidence: American movies refers to country_name = 'United States of America'; in 2006 refers to release_date LIKE '2006%'; average revenue = AVG(revenue)

SQL:
SELECT AVG(T1.revenue) FROM movie AS T1 INNER JOIN production_COUNTry AS T2 ON T1.movie_id = T2.movie_id INNER JOIN COUNTry AS T3 ON T2.COUNTry_id = T3.COUNTry_id WHERE T3.COUNTry_name = 'United States of America' AND CAST(STRFTIME('%Y', T1.release_date) AS INT) = 2006