lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
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)
)

### Question
Tweet with ID tw-682723090279841798 was posted from which country?

### Hint
"tw-682723090279841798" is the TweetID

### SQL
SELECT T2.Country FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T1.TweetID = 'tw-682723090279841798'
### Database Schema
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)
)

### Question
List down all the tweet text posted from Australia.

### Hint
"Australia" is the Country

### SQL
SELECT T1.text FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T2.Country = 'Australia'
### Database Schema
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)
)

### Question
Write down the tweet text posted from Rawang, Selangor, Malaysia.

### Hint
"Rawang" is the City; "Selangor" is the State; "Malaysia" is the Country

### SQL
SELECT T1.text FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T2.City = 'Rawang' AND T2.State = 'Selangor' AND T2.Country = 'Malaysia'
### Database Schema
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)
)

### Question
Tweets that were posted from Brazil are in what languague?

### Hint
"Brazil" is the Country; language refers to Lang

### SQL
SELECT DISTINCT T1.Lang FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T2.Country = 'Brazil'
### Database Schema
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)
)

### Question
State the country where the most positive sentiment tweets were posted.

### Hint
country with the most positive sentiment tweet refers to Country where Max(Count(Sentiment > 0))

### SQL
SELECT T.Country FROM ( SELECT T2.Country, SUM(T1.Sentiment) AS num FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T1.Sentiment > 0 GROUP BY T2.Country ) T ORDER BY T.num DESC LIMIT 1
### Database Schema
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)
)

### Question
Calculate the total likes collected by tweets in `ru` posted by male users.

### Hint
'ru' refers to Lang = 'ru'; male user refers to Gender = 'Male'

### SQL
SELECT SUM(T1.Likes) FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID WHERE T1.Lang = 'ru' AND T2.Gender = 'Male'
### Database Schema
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)
)

### Question
Calculate the average number of male users who posted tweets in a week.

### Hint
male user refers to Gender = 'Male'; average tweet in a week per user refers to Divide ( Divide(Count(TweetID), Count (UserID)), Divide(31, 7))

### SQL
SELECT COUNT(DISTINCT T1.TweetID) / COUNT(DISTINCT T1.UserID) / 7 AS avg FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID WHERE T2.Gender = 'Male' AND T1.Day BETWEEN 1 AND 31
### Database Schema
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)
)

### Question
How many tweets have a klout of over 50?

### Hint
klout of over 50 refers to Klout > 50

### SQL
SELECT COUNT(DISTINCT TweetID) FROM twitter WHERE Klout > 50
### Database Schema
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)
)

### Question
Please list the texts of all the tweets that are not in English.

### Hint
not in English refers to Lang <> en'

### SQL
SELECT text FROM twitter WHERE Lang != 'en'
### Database Schema
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)
)

### Question
Please give the user ID of the user who has posted the most tweets.

### Hint
users with the most tweet refers to UserID where Max(Count (TweetID))

### SQL
SELECT UserID FROM twitter GROUP BY UserID ORDER BY COUNT(DISTINCT TweetID) DESC LIMIT 1
### Database Schema
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)
)

### Question
Among all the tweets posted on Mondays, how many of them are reshared?

### Hint
"Monday" is the Weekday; reshare refers to IsReshare = 'TRUE'

### SQL
SELECT COUNT(DISTINCT TweetID) FROM twitter WHERE Weekday = 'Monday' AND IsReshare = 'TRUE'
### Database Schema
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)
)

### Question
Please list the texts of the top 3 tweets with the most number of unique users seeing the tweet.

### Hint
the most number of unique users seeing refers to Max(Reach)

### SQL
SELECT text FROM twitter ORDER BY Reach DESC LIMIT 3
### Database Schema
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)
)

### Question
How many reshared tweets have over 100 likes?

### Hint
over 100 likes refers to Likes > 100; reshare tweet refers to IsReshare = 'TRUE'

### SQL
SELECT COUNT(DISTINCT TweetID) FROM twitter WHERE IsReshare = 'TRUE' AND Likes > 100
### Database Schema
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)
)

### Question
What is the total number of tweets sent by male users on Mondays?

### Hint
male user refers to Gender = 'Male; 'Monday' is the Weekday; total number of tweet refers to Count (TweetID)

### SQL
SELECT COUNT(DISTINCT T1.TweetID) FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID WHERE T2.Gender = 'Male' AND T1.Weekday = 'Monday'
### Database Schema
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)
)

### Question
What is the gender of the user who has posted the tweet that got the most likes?

### Hint
tweet got the most likes refers to Max(Likes)

### SQL
SELECT T2.Gender FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID ORDER BY T1.Likes DESC LIMIT 1
### Database Schema
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)
)

### Question
Please list the texts of all the tweets in French posted by male users.

### Hint
"French" is the language and refers to Lang = 'fr'; male user refers to Gender = 'Male'

### SQL
SELECT T1.text FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID WHERE T2.Gender = 'Male' AND T1.Lang = 'fr'
### Database Schema
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)
)

### Question
How many tweets in French were posted from Australia?

### Hint
"French" is the language and refers to Lang = 'fr'; 'Australia' is the Country

### SQL
SELECT COUNT(DISTINCT T1.TweetID) FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T1.Lang = 'fr' AND T2.Country = 'Australia'
### Database Schema
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)
)

### Question
Among all the tweets with a positive sentiment, how many of them were posted by male users in Australia?

### Hint
tweet with positive sentiment refers to Sentiment > 0; male user refers to Gender = 'Male'; 'Australia' is the Country

### SQL
SELECT COUNT(T1.TweetID) FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID INNER JOIN user AS T3 ON T3.UserID = T1.UserID WHERE T2.Country = 'Australia' AND T3.Gender = 'Male' AND T1.Sentiment > 0
### Database Schema
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)
)

### Question
How many more tweets with a positive sentiment than the tweets with a neutral sentiment were posted by male users?

### Hint
positive sentiment tweet refers to Sentiment > 0; neutral sentiment refers to Sentiment = 0; male user refers to Gender = 'Male'; difference = Subtract (Count (TweetID where Sentiment > 0), Count (TweetID where Sentiment = 0))

### SQL
SELECT SUM(CASE WHEN T1.Sentiment > 0 THEN 1 ELSE 0 END) - SUM(CASE WHEN T1.Sentiment = 0 THEN 1 ELSE 0 END) AS diff FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID WHERE T2.Gender = 'Male'
### Database Schema
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)
)

### Question
From which city was the tweet with the most number of retweets posted?

### Hint
tweet with most number of retweet post refers to Max(RetweetCount)

### SQL
SELECT T2.City FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID ORDER BY T1.RetweetCount DESC LIMIT 1
### Database Schema
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)
)

### Question
From which city were more tweets posted, Bangkok or Chiang Mai?

### Hint
"Bangkok" and "Chiang Mai" are both City

### SQL
SELECT SUM(CASE WHEN T2.City = 'Bangkok' THEN 1 ELSE 0 END) AS bNum , SUM(CASE WHEN T2.City = 'Chiang Mai' THEN 1 ELSE 0 END) AS cNum FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T2.City IN ('Bangkok', 'Chiang Mai')