### fill the gaps below and send the script back

import pandas as pd
import sqlite3

DB_NAME = 'inventive_retail_group.db'

def create_sqlite_database(filename: str) -> None:
    """ create a database connection to an SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(filename)
        print(sqlite3.sqlite_version)
    except sqlite3.Error as e:
        print(e)
    finally:
        if conn:
            conn.close()

create_sqlite_database(DB_NAME)
conn = sqlite3.connect(DB_NAME)

df = pd.read_csv('https://gist.githubusercontent.com/kevin336/acbb2271e66c10a5b73aacf82ca82784/raw/e38afe62e088394d61ed30884dd50a6826eee0a8/employees.csv')

employees = df.drop('SALARY', axis=1)
salaries = df[['EMPLOYEE_ID', 'SALARY']]

employees.to_sql('employees', conn, index=False, if_exists='replace')
salaries.to_sql('salaries', conn, index=False, if_exists='replace')

display(employees)
display(salaries)

### find employees which managers work in other department
employees_working_in_other = pd.read_sql('''
SELECT *
FROM employees
WHERE MANAGER_ID IN (SELECT MANAGER_ID
                     FROM employees
                     GROUP BY MANAGER_ID
                     HAVING AVG(DEPARTMENT_ID) NOT IN (20,30,50,60,90,100,110)
                     )
''', conn)
print(employees_working_in_other)

### Calculate average salary for departments using window function
employees_working_in_other = pd.read_sql('''
SELECT DEPARTMENT_ID,
       AVG (SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS avg_salaries
FROM (SELECT *
      FROM employees i
      JOIN salaries AS c ON i.EMPLOYEE_ID = c.EMPLOYEE_ID) AS d

''', conn)
print(employees_working_in_other)