-- Database schema
| location : LocationID [ INTEGER ] primary_key , Country [ TEXT ] , State [ TEXT ] , StateCode [ TEXT ] , City [ TEXT ] | user : UserID [ TEXT ] primary_key , Gender [ TEXT ] | 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 ] twitter.LocationID = location.LocationID , UserID [ TEXT ] twitter.UserID = user.UserID |
-- -- Tweets posted from which city has a higher number of average likes, Bangkok or Chiang Mai?

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');