{"cells": [{"source": ["I referenced these kernel and discussion:\n", "* https://www.kaggle.com/paulorzp/log-ma-and-days-of-week-means-lb-0-532 by Paul Pinto\n", "* https://www.kaggle.com/c/favorita-grocery-sales-forecasting/discussion/42948 by tarobxl"], "metadata": {"_uuid": "2319acf412ed0ee95ccda1e2a2d78fdce09f2552", "_cell_guid": "f588c22d-8667-492d-9dae-fc35a4fffc12"}, "cell_type": "markdown"}, {"source": ["```SQL\n", "-- dest. table: ma8dw.mst_data\n", "SELECT\n", "  *\n", "FROM\n", "(\n", "SELECT\n", "  distinct(date)\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  date >= '2016-08-01'\n", "),\n", "(\n", "SELECT\n", "  distinct(store_nbr)\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  date >= '2016-08-01'\n", "),\n", "(\n", "SELECT\n", "  distinct(item_nbr)\n", "FROM\n", "  corpora2.train\n", "WHERE\n", "  date >= '2016-08-01'\n", ")\n", ";\n", "\n", "-- dest. table: ma8dw.train_2017_ext\n", "SELECT\n", "  m.date as date,\n", "  FORMAT_DATE('%a', m.date) as dow,\n", "  m.store_nbr as store_nbr,\n", "  m.item_nbr as item_nbr,\n", "  CASE WHEN t.unit_sales > 0 THEN LN(t.unit_sales + 1) ELSE 0 END unit_sales\n", "FROM\n", "  ma8dw.mst_data as m\n", "LEFT OUTER JOIN\n", "  corpora2.train as t\n", "ON\n", "  m.date = t.date AND m.store_nbr = t.store_nbr AND m.item_nbr = t.item_nbr\n", ";\n", "\n", "-- dest. table: ma8dw.mst_2017_mean_dow\n", "SELECT\n", "  store_nbr, item_nbr, dow,\n", "  AVG(unit_sales) as unit_sales\n", "FROM\n", "(\n", "  SELECT\n", "    store_nbr, item_nbr, FORMAT_DATE('%a', date) as dow,\n", "    CASE WHEN unit_sales > 0 THEN LN(unit_sales + 1) ELSE 0 END unit_sales\n", "  FROM\n", "    corpora2.train\n", "  WHERE\n", "    date >= '2016-08-01'\n", ")\n", "GROUP BY\n", "  store_nbr, item_nbr, dow\n", "\n", "-- dest. table: ma8dw.mst_2017_mean_week\n", "SELECT\n", "  store_nbr, item_nbr,\n", "  AVG(unit_sales) as unit_sales\n", "FROM\n", "  ma8dw.mst_2017_mean_dow\n", "GROUP BY\n", "  store_nbr, item_nbr\n", ";\n", "\n", "-- dest. table: ma8dw.train_2017_ext_ma_last\n", "SELECT\n", "  *\n", "FROM\n", "(\n", "SELECT\n", "  ROW_NUMBER() OVER(partition by store_nbr, item_nbr order by date desc) row_num,\n", "  date,\n", "  store_nbr,\n", "  item_nbr,\n", "  unit_sales as ma001,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ma003,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as ma007,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as ma014,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) as ma028,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 55 PRECEDING AND CURRENT ROW) as ma056,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN 111 PRECEDING AND CURRENT ROW) as ma112,\n", "  AVG(unit_sales) OVER(partition by store_nbr, item_nbr order by date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)as ma226\n", "FROM\n", "  ma8dw.train_2017_ext\n", ")\n", "WHERE\n", "  row_num = 1\n", ";\n", "\n", "\n", "-- dest. table: ma8dw.mst_2017_ma8\n", "CREATE TEMPORARY FUNCTION median(x ARRAY<FLOAT64>)\n", "RETURNS FLOAT64\n", "LANGUAGE js AS \"\"\"\n", "  var center = (x.length / 2) | 0;\n", "  var x_sorted = x.sort();\n", "  if (x_sorted.length % 2) {\n", "    return x_sorted[center];\n", "  } else {\n", "    return (x_sorted[center - 1] + x_sorted[center]) / 2;\n", "  }\n", "\"\"\";\n", "SELECT\n", "  store_nbr,\n", "  item_nbr,\n", "  median([ma001, ma003, ma007, ma014, ma028, ma056, ma112, ma226]) as unit_sales\n", "FROM\n", "  `ma8dw.train_2017_ext_ma_last`\n", ";\n", "\n", "-- dest. table: ma8dw.submit_ma8dwph_1121\n", "SELECT\n", "  t.id as id,\n", "  CASE\n", "   WHEN a.unit_sales is not null and d.unit_sales is not null and w.unit_sales > 0 THEN\n", "     (EXP(a.unit_sales * d.unit_sales / w.unit_sales) - 1) * onpromotion \n", "     * CASE WHEN h.transferred = false THEN 1.2 ELSE 1 END\n", "   WHEN  a.unit_sales is not null THEN\n", "     (EXP(a.unit_sales) - 1) * onpromotion\n", "     * CASE WHEN h.transferred = false THEN 1.2 ELSE 1 END\n", "   ELSE\n", "   0\n", "  END as unit_sales\n", "FROM\n", "  (\n", "  SELECT\n", "  id,\n", "  date,\n", "  FORMAT_DATE('%a', date) as dow,\n", "  store_nbr,\n", "  item_nbr,\n", "  CASE WHEN onpromotion = true THEN 1.5 ELSE 1. END onpromotion\n", "  FROM\n", "    corpora2.test\n", "  ) as t\n", "LEFT OUTER JOIN\n", "  ma8dw.mst_2017_ma8 as a\n", "ON\n", "  t.item_nbr = a.item_nbr AND t.store_nbr = a.store_nbr\n", "LEFT OUTER JOIN\n", "  ma8dw.train_2017_mean_dow as d\n", "ON\n", "  t.item_nbr = d.item_nbr AND t.store_nbr = d.store_nbr AND t.dow = d.dow\n", "LEFT OUTER JOIN\n", "  ma8dw.train_2017_mean_week as w\n", "ON\n", "  t.item_nbr = w.item_nbr AND t.store_nbr = w.store_nbr\n", "LEFT OUTER JOIN\n", "  (SELECT\n", "    date,\n", "    transferred\n", "  FROM\n", "    corpora2.holidays_events\n", "  WHERE\n", "    transferred = false\n", "  )as h\n", "ON\n", "  t.date = h.date\n", ";\n", "```"], "metadata": {"_uuid": "a13e99c82e323e102b02e6f43d0873e23b9b3853", "_cell_guid": "3534d1bc-fc59-4d69-aa08-b8822a6cbd5b"}, "cell_type": "markdown"}], "nbformat": 4, "metadata": {"language_info": {"nbconvert_exporter": "python", "codemirror_mode": {"version": 3, "name": "ipython"}, "pygments_lexer": "ipython3", "mimetype": "text/x-python", "file_extension": ".py", "name": "python", "version": "3.6.3"}, "kernelspec": {"language": "python", "name": "python3", "display_name": "Python 3"}}, "nbformat_minor": 1}