How many more units of item no.16 were sold on the day with the highest max temperature in 2012 in store no.5 than in store no.10?
store no. 5 refers to store_nbr = 5; store no. 10 refers to store_nbr = 10; item no.16 refers to item_nbr = 16; in 2012 refers to SUBSTR(date, 1, 4) = '2012'; highest max temperature refers to Max(tmax); more units sold refers to Subtract ( Sum(units where store_nbr = 5), Sum(units where store_nbr = 10))
SELECT ( SELECT SUM(units) FROM sales_in_weather AS T1 INNER JOIN relation AS T2 ON T1.store_nbr = T2.store_nbr INNER JOIN weather AS T3 ON T2.station_nbr = T3.station_nbr WHERE T1.item_nbr = 16 AND T1.`date` LIKE '%2012%' AND T1.store_nbr = 5 GROUP BY tmax ORDER BY T3.tmax DESC LIMIT 1 ) - ( SELECT SUM(units) FROM sales_in_weather AS T1 INNER JOIN relation AS T2 ON T1.store_nbr = T2.store_nbr INNER JOIN weather AS T3 ON T2.station_nbr = T3.station_nbr WHERE T1.item_nbr = 16 AND T1.`date` LIKE '%2012%' AND T1.store_nbr = 6 GROUP BY tmax ORDER BY T3.tmax DESC LIMIT 1 )
CREATE TABLE sales_in_weather
(
    date      DATE,
    store_nbr INTEGER,
    item_nbr  INTEGER,
    units     INTEGER,
    primary key (store_nbr, date, item_nbr)
);
CREATE TABLE weather
(
    station_nbr INTEGER,
    date        DATE,
    tmax        INTEGER,
    tmin        INTEGER,
    tavg        INTEGER,
    depart      INTEGER,
    dewpoint    INTEGER,
    wetbulb     INTEGER,
    heat        INTEGER,
    cool        INTEGER,
    sunrise     TEXT,
    sunset      TEXT,
    codesum     TEXT,
    snowfall    REAL,
    preciptotal REAL,
    stnpressure REAL,
    sealevel    REAL,
    resultspeed REAL,
    resultdir   INTEGER,
    avgspeed    REAL,
    primary key (station_nbr, date)
);
CREATE TABLE relation
(
    store_nbr   INTEGER
            primary key,
    station_nbr INTEGER,
    foreign key (store_nbr) references sales_in_weather(store_nbr),
    foreign key (station_nbr) references weather(station_nbr)
);
