Tweets posted from which city has a higher number of average likes, Bangkok or Chiang Mai?
"Bangkok" and "Chiang Mai" are both City; average number of like = Divide (Sum(Likes), Count(TweetID))
SELECT SUM(CASE WHEN T2.City = 'Bangkok' THEN Likes ELSE NULL END) / COUNT(CASE WHEN T2.City = 'Bangkok' THEN 1 ELSE 0 END) AS bNum , SUM(CASE WHEN City = 'Chiang Mai' THEN Likes ELSE NULL END) / COUNT(CASE WHEN City = 'Chiang Mai' THEN TweetID ELSE NULL END) AS cNum FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T2.City IN ('Bangkok', 'Chiang Mai')
CREATE TABLE location
(
    LocationID INTEGER
        constraint location_pk
            primary key,
    Country    TEXT,
    State      TEXT,
    StateCode  TEXT,
    City       TEXT
);
CREATE TABLE user
(
    UserID TEXT
        constraint user_pk
            primary key,
    Gender TEXT
);
CREATE TABLE twitter
(
    TweetID         TEXT
            primary key,
    Weekday         TEXT,
    Hour         INTEGER,
    Day          INTEGER,
    Lang         TEXT,
    IsReshare    TEXT,
    Reach        INTEGER,
    RetweetCount INTEGER,
    Likes        INTEGER,
    Klout        INTEGER,
    Sentiment    REAL,
    "text"         TEXT,
    LocationID   INTEGER,
    UserID       TEXT,
    foreign key (LocationID) references location(LocationID),
    foreign key (UserID) references user(UserID)
);
