import pandas as pd

import sqlite3

import pandas as pd
import sqlite3
con = sqlite3.connect('../input/database.sqlite')
scripts = pd.read_sql_query('\n\nSELECT s.Id,\n\n       cv.Title,\n\n       COUNT(DISTINCT vo.Id) NumVotes,\n\n       COUNT(DISTINCT CASE WHEN vo.UserId!=s.AuthorUserId THEN vo.Id ELSE NULL END) NumNonSelfVotes,\n\n       CASE WHEN COUNT(DISTINCT CASE WHEN vo.UserId!=s.AuthorUserId THEN vo.Id ELSE NULL END)>0 THEN 1 ELSE 0 END HasNonSelfVotes,\n\n       COUNT(DISTINCT v.Id) NumVersions,\n\n       SUM(CASE WHEN r.WorkerStatus=2 THEN 1 ELSE 0 END) NumSuccessfulRuns,\n\n       SUM(CASE WHEN r.WorkerStatus=3 THEN 1 ELSE 0 END) NumErroredRuns,\n\n       SUM(CASE WHEN v.IsChange=1 THEN 1 ELSE 0 END) NumChangedVersions,\n\n       SUM(v.LinesInsertedFromPrevious-v.LinesDeletedFromPrevious) Lines,\n\n       SUM(v.LinesInsertedFromPrevious+v.LinesChangedFromPrevious) LinesAddedOrChanged,\n\n       l.Name\n\nFROM Scripts s\n\nINNER JOIN ScriptVersions v ON v.ScriptId=s.Id\n\nINNER JOIN ScriptVersions cv ON s.CurrentScriptVersionId=cv.Id\n\nINNER JOIN ScriptRuns r ON r.ScriptVersionId=v.Id\n\nINNER JOIN ScriptLanguages l ON v.ScriptLanguageId=l.Id\n\nLEFT OUTER JOIN ScriptVotes vo ON vo.ScriptVersionId=v.Id\n\nWHERE r.WorkerStatus != 4\n\n  AND r.WorkerStatus != 5\n\nGROUP BY s.Id,\n\n         cv.Title,\n\n         cv.Id,\n\n         l.Name\n\nORDER BY cv.Id DESC\n\n', con)
scripts

pd.read_sql_query('\nSELECT *\nFROM ScriptLanguages\nLIMIT 100\n', con)