Which country produced the highest number of cars? Calculate the annual average number of cars that the said country produced from the very start to the present.
the highest number of cars refers to max(country); annual average number = divide(count(ID), count(model_year))
SELECT T2.country, CAST(COUNT(T1.ID) AS REAL) / COUNT(DISTINCT T1.model_year) FROM production AS T1 INNER JOIN country AS T2 ON T1.country = T2.origin GROUP BY T2.country ORDER BY COUNT(T2.country) DESC LIMIT 1
CREATE TABLE country
(
    origin  INTEGER
            primary key,
    country TEXT
);
CREATE TABLE price
(
    ID    INTEGER
            primary key,
    price REAL
);
CREATE TABLE data
(
    ID           INTEGER
            primary key,
    mpg          REAL,
    cylinders    INTEGER,
    displacement REAL,
    horsepower   INTEGER,
    weight       INTEGER,
    acceleration REAL,
    model        INTEGER,
    car_name     TEXT,
    foreign key (ID) references price(ID)
);
CREATE TABLE production
(
    ID         INTEGER,
    model_year INTEGER,
    country    INTEGER,
    primary key (ID, model_year),
    foreign key (country) references country(origin),
    foreign key (ID) references data(ID),
    foreign key (ID) references price(ID)
);
