{"cells":[{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"import pandas as pd\nfrom pathlib import Path\nimport subprocess\nimport sqlite3\nfrom tqdm.notebook import trange\n\nimport riiideducation","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PATH = Path('../input/riiid-test-answer-prediction')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ndf_train = pd.read_csv(PATH/'train.csv', usecols=['row_id', 'user_id', 'task_container_id', 'timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"conn = sqlite3.connect(':memory:')\nc = conn.cursor()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nchunk_size = 20000\ntotal = len(df_train)\nn_chunks = (total // chunk_size + 1)\n\nfor i in trange(n_chunks):\n    df_train.iloc[i * chunk_size:(i + 1) * chunk_size].to_sql('train', conn, method='multi', if_exists='append', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nc.executescript(\"\"\"\n    DROP TABLE IF EXISTS user_tids_all;\n    \n    CREATE TABLE user_tids_all AS\n        SELECT user_id, task_container_id, MAX(timestamp) timestamp\n        FROM train\n        GROUP BY user_id, task_container_id\n        ORDER BY user_id;\n        \n    CREATE UNIQUE INDEX user_id_tid_idx ON user_tids_all (user_id, task_container_id);\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c.execute('select count(*) from user_tids_all').fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"delta_count_train = c.execute(\"\"\"\n    WITH numbered_records AS (\n        SELECT user_id, CAST(task_container_id - LAG(task_container_id) OVER(\n            PARTITION BY user_id ORDER BY timestamp\n        ) > 1 AS INTEGER) delta_count\n        FROM user_tids_all\n    )\n    SELECT SUM(delta_count)\n    FROM numbered_records\n\"\"\").fetchone()[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f'{delta_count_train:0,d} deltas greater than one in the full training set.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nc.executescript(\"\"\"\n    DROP TABLE IF EXISTS user_tids;\n    \n    CREATE TABLE user_tids AS\n        SELECT user_id, MAX(task_container_id) tid_current, 0 tid_delta_count\n        FROM train\n        GROUP BY user_id\n        ORDER BY user_id;\n        \n    CREATE UNIQUE INDEX user_id_idx ON user_tids (user_id);\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"riiideducation.competition.make_env\nenv = riiideducation.make_env()\niter_test = env.iter_test()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for i, test_batch in enumerate(iter_test):\n    records = test_batch[0][['user_id', 'task_container_id']].to_records(index=False)\n\n    c.executescript(f\"\"\"\n        INSERT INTO user_tids (user_id, tid_current)\n            VALUES {(',').join(map(str, records))}\n        ON CONFLICT (user_id) DO UPDATE SET\n            tid_delta_count = CAST(excluded.tid_current - tid_current > 1 AS INTEGER) + tid_delta_count,\n            tid_current = excluded.tid_current\n    \"\"\").fetchone()\n\n    delta_count = c.execute(\"\"\"\n        SELECT SUM(tid_delta_count)\n        FROM user_tids\n        WHERE tid_delta_count > 1\n        \"\"\").fetchone()[0]\n    \n    delta_count = 0 if delta_count is None else delta_count\n    \n    if delta_count > int(1e5):\n        raise\n    \n    if not i % 1000 or i < 5:\n        print(f'{delta_count:0,d} deltas greater than one after {i+1:0,d} batches.')\n    \n    env.predict(test_batch[1][test_batch[0].content_type_id == 0])","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}