#!/usr/bin/env python
# coding: utf-8

# In[4]:


import sqlite3


# In[2]:


import requests
r = requests.get("http://math-info.hse.ru/f/2018-19/nes-ds/gradebook.sqlite")
with open("gradebook.sqlite", "wb") as f:
    f.write(r.content)


# In[5]:


conn = sqlite3.connect("db/gradebook.sqlite")


# In[6]:


c = conn.cursor()


# In[7]:


c.execute(
"""
SELECT users.first_name, avg(grade) 
FROM users LEFT JOIN submissions ON users.id = submissions.user_id
GROUP BY users.id
"""
).fetchall()


# In[8]:


c.execute(
"""
SELECT u_id, fn, ln, sum(gr)
FROM (
  SELECT users.id as u_id, users.first_name as fn, users.last_name as ln, submissions.grade, assignments.max_grade, min(submissions.grade, assignments.max_grade) as gr
  FROM users LEFT JOIN submissions ON users.id = submissions.user_id
  JOIN assignments ON submissions.assignment_id == assignments.id
)
GROUP BY u_id
"""
).fetchall()


# In[9]:


get_ipython().run_cell_magic('timeit', '', 'c.execute(\n"""\nSELECT users.first_name, users.last_name, (\nSELECT sum(grade) FROM submissions WHERE submissions.user_id == users.id\n) sum_grade\nFROM users\n"""\n).fetchall()\n')


# In[10]:


get_ipython().run_cell_magic('timeit', '', 'c.execute(\n"""\nSELECT users.first_name, users.last_name, sum(grade)\nFROM users LEFT JOIN submissions ON users.id == submissions.user_id\nGROUP BY users.id\n"""\n).fetchall()\n')


# In[11]:


c.execute("""
PRAGMA table_info(courses)
""").fetchall()


# In[12]:


get_ipython().run_cell_magic('timeit', '', 'c.execute("""\nSELECT users.first_name, users.last_name FROM users\nWHERE users.id IN (\n  SELECT course_user.user_id FROM course_user WHERE course_user.course_id IN\n  (\n    SELECT courses.id FROM courses WHERE code == "nes-ds"\n  )\n)\n"""\n).fetchall()\n')


# In[57]:


get_ipython().run_cell_magic('timeit', '', 'c.execute("""\nSELECT DISTINCT users.first_name, users.last_name FROM users JOIN course_user \nON users.id == course_user.user_id JOIN courses ON course_user.course_id == courses.id\nWHERE courses.code == "nes-ds"\n"""\n).fetchall()\n')


# In[ ]: