{"cells":[{"metadata":{},"cell_type":"markdown","source":"# cumulative answered_correctly with question tags"},{"metadata":{"trusted":true},"cell_type":"code","source":"lst = []\nfor t in range(188):\n    s = f\"\"\"\n  CASE WHEN q_tag_{t}=1 AND content_type_id=0\n    THEN ROW_NUMBER() OVER (PARTITION BY user_id, q_tag_{t}, content_type_id ORDER BY row_id) END\n    AS work_q_tag_{t}_v1,\"\"\"\n    lst.append(s)\nwork_q_tag_v1 = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  SUM(\n    CASE WHEN q_tag_{t}=1 AND content_type_id=0\n    THEN answered_correctly END\n    ) OVER(PARTITION BY user_id, q_tag_{t}, content_type_id ORDER BY row_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)\n    AS cumsum_q_tag_{t}_v1,\"\"\"\n    lst.append(s)\ncumsum_q_tag_v1 = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(LAST_VALUE(work_q_tag_{t}_v1 IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY row_id), 0) AS work_q_tag_{t}_v2,\"\"\"\n    lst.append(s)\nwork_q_tag_v2 = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(LAST_VALUE(cumsum_q_tag_{t}_v1 IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY row_id), 0) AS cumsum_q_tag_{t}_v2,\"\"\"\n    lst.append(s)\ncumsum_q_tag_v2 = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(LAG (work_q_tag_{t}_v2, 1) OVER (PARTITION BY user_id ORDER BY row_id), 0) AS work_q_tag_{t}_v3,\"\"\"\n    lst.append(s)\nwork_q_tag_v3 = \"\".join(lst)\n\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(LAG (cumsum_q_tag_{t}_v2, 1) OVER (PARTITION BY user_id ORDER BY row_id), 0) AS cumsum_q_tag_{t}_v3,\"\"\"\n    lst.append(s)\ncumsum_q_tag_v3 = \"\".join(lst)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"query = f\"\"\"\nCREATE TABLE Kaggle_Riiid.create_feat_cumsum_q_tag AS\nSELECT\n  row_id,\n  -- work_q_tag{work_q_tag_v3}\n  -- correct_q_tag{cumsum_q_tag_v3}\nFROM (\nSELECT\n  row_id, user_id,\n  -- work_q_tag{work_q_tag_v2}\n  -- correct_q_tag{cumsum_q_tag_v2}\nFROM (\nSELECT\n  row_id, user_id,\n  -- work_q_tag{work_q_tag_v1}\n  -- correct_q_tag{cumsum_q_tag_v1}\nFROM Kaggle_Riiid.train\nLEFT JOIN Kaggle_Riiid.question_tags_ohe ON question_id = content_id\n))\n\"\"\"\nprint(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"lst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(SAFE_DIVIDE(cumsum_q_tag_{t}_v3, work_q_tag_{t}_v3), 0) AS correct_rate_q_tag_{t},\"\"\"\n    lst.append(s)\ncorrect_rate_q_tag = \"\".join(lst)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"query = f\"\"\"\nCREATE TABLE Kaggle_Riiid.create_feat_rate_q_tag AS\nSELECT\n  row_id, {correct_rate_q_tag}\nFROM Kaggle_Riiid.create_feat_cumsum_q_tag\n\"\"\"\nprint(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# lecture PCA features"},{"metadata":{"trusted":true},"cell_type":"code","source":"lst = []\nfor t in range(188):\n    s = f\"\"\"\n  SUM(\n    CASE WHEN l_tag_{t}=1 AND content_type_id=1 THEN 1 END\n    ) OVER(PARTITION BY user_id, l_tag_{t} ORDER BY row_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) \n    AS work_l_tag_{t}_v1,\"\"\"\n    lst.append(s)\nwork_l_tag_v1 = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(LAST_VALUE(work_l_tag_{t}_v1 IGNORE NULLS) OVER(PARTITION BY user_id ORDER BY row_id), 0) AS work_l_tag_{t}_v2,\"\"\"\n    lst.append(s)\nwork_l_tag_v2 = \"\".join(lst)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"query = f\"\"\"\nCREATE TABLE Kaggle_Riiid.create_feat_cumsum_l_tag AS\nSELECT\n  row_id, user_id,{work_l_tag_v2}\nFROM(\nSELECT\n  row_id, user_id,{work_l_tag_v1}\nFROM Kaggle_Riiid.train \nLEFT JOIN Kaggle_Riiid.lecture_tags_ohe ON lecture_id = content_id\n)\n\"\"\"\nprint(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# for infer infomation"},{"metadata":{"trusted":true},"cell_type":"code","source":"lst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(SUM(CASE WHEN content_type_id=0 AND q_tag_{t}=1 THEN 1 END), 0) AS q_tag_{t}_work,\"\"\"\n    lst.append(s)\nq_tag_work_agg_user = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(SUM(CASE WHEN content_type_id=0 AND q_tag_{t}=1 AND answered_correctly=1 THEN 1 END), 0) AS q_tag_{t}_correct,\"\"\"\n    lst.append(s)\nq_tag_correct_agg_user = \"\".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"\"\"\n  IFNULL(SUM(CASE WHEN content_type_id=1 AND l_tag_{t}=1 THEN 1 END), 0) AS l_tag_{t}_work,\"\"\"\n    lst.append(s)\nl_tag_work_agg_user = \"\".join(lst)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"query = f\"\"\"\nCREATE TABLE Kaggle_Riiid.user_agg_info AS\nSELECT\n  user_id, \n  -- q_tag work{q_tag_work_agg_user}\n  -- q_tag correct{q_tag_correct_agg_user}\n  -- l_tag wok{l_tag_work_agg_user}\nFROM Kaggle_Riiid.train\nLEFT JOIN Kaggle_Riiid.question_tags_ohe ON question_id = content_id\nLEFT JOIN Kaggle_Riiid.lecture_tags_ohe ON lecture_id = content_id\nGROUP BY user_id\"\"\"\nprint(query)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# for train ignore"},{"metadata":{"trusted":true},"cell_type":"code","source":"lst = []\nfor i in range(188):\n    s = f\"\"\"\n                 cumsum_q_tag_{i}_v3,\"\"\"\n    lst.append(s)\ncumsum_q_tag = \"\".join(lst)\nprint(cumsum_q_tag)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# avairable tag"},{"metadata":{"trusted":true},"cell_type":"code","source":"lst = []\nfor t in range(188):\n    s = f\"correct_rate_q_tag_{t} * q_tag_{t}\"\n    lst.append(s)\nrate_x_tag = \" +\\n    \".join(lst)\n\nlst = []\nfor t in range(188):\n    s = f\"q_tag_{t}\"\n    lst.append(s)\nn_tag = \" +\\n    \".join(lst)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"query = f\"\"\"\nCREATE TABLE Kaggle_Riiid.create_feat_available_tag_rate AS\nSELECT\n  row_id,\n  IFNULL(SAFE_DIVIDE(\n    {rate_x_tag}\n  ,\n    {n_tag}\n  ), 0) AS q_tag_rate_this_avg\nFROM  Kaggle_Riiid.train\nLEFT JOIN  Kaggle_Riiid.create_feat_rate_q_tag USING (row_id)\nLEFT JOIN  Kaggle_Riiid.question_tags_ohe ON train.content_id = question_tags_ohe.question_id\n\"\"\"\nprint(query)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}