# 1st Place solution of category "BigQuery ML Models built in SQL"

First of all thank you to the organizers for hosting this competition! I was very delighted having the opportunity to compete in a SQL competition.
Congratulations to the LB winners as well as the winners in the prize categories!
My personal objective for the competition was to tryout BigQuery (BQ) including the basic ML features. So I restricted myself to SQL-techniques.
The solution can briefly be summarized as:
-
Features
: turns, intersection clustering, populations per zipcode
-
Model
: Detailed and general (for unknown intersections) LinReg model per target (no CV/KFolding)
Details about the solution which led to my highest scoring submission (and what didn't work) are all documented in
[this kernel](https://www.kaggle.com/joatom/bigquery-geotab-bqml)
. I also published two other kernels during the competition and archived them
[here](https://github.com/joatom/kaggle-2019-BigQuery-Geotab-Intersection-Congestion)
.
Besides the technical part I like to share some thoughts about my ranking and the competition.
As you might have noticed there is a big difference between my 1st place in the
BigQuery ML Models built in SQL
category and my 213th Place on the LB. I can think of some possible reasons for that:
- Few participants have chosen the "SQL-way" in the competition
- I did some beginners mistakes (e.g. not setting up a proper validation set or I didn't go "all-the-way" analyzing the data or doing structured feature elimination)
- Restriction on the available BQML Models (LinReg, Means and LogReg) can't compete against Tree-, Booster- and NN-Models.
- The quota restrictions in BQ slowed down the pace. For instance, I didn't do grid search for parameters or KFolding because I could only train a few models per week. (to be fair, I need to mention that I relied entirely on the sandbox and I didn't activate the gcp credits which were provided at the beginning of the competition).
Anyway, I really love processing data with SQL. For me it feels more consistent than pandas (but that's my biased view). I really enjoyed the easily embedding of the models in SQL. I also liked the easily usable geo spatial functions of BQ. I think with the TensorFlow option there are opportunities to build more complex models.
This competition nicely sums up my first kaggle year and points me where to go next.
- retro 2019 - so much to learn and randomly trying out everything (like a child in a candy store)
- goals 2020 - approaching data science projects in a more structured kind of manner.
Thanks for reading.
Johannes