<filename>sprintchallenge/northwind.py
"""can only import sqlite3 and other modules we write """
import sqlite3 as sql

""" Answering questions about the northwind database through SQL queries """

#connecting to the northwind database file and instantiating a cursor
connect = sql.connect("northwind_small.sqlite3")
cursor = connect.cursor()

# finding the ten most expensive items in the db
def highest_price():
    """ Finding the ten most expensive items per unit price in the northwind DB """
    ten_highest_query = """
        SELECT ProductName
        FROM Product
        GROUP BY UnitPrice
        ORDER BY UnitPrice DESC
        LIMIT 10; """
    ten_highest = cursor.execute(ten_highest_query).fetchall()
    print(f' The ten most expensive items per unit price are: {ten_highest}')

# averrage age of an employee at the time of hire
def average_emp_age():
    """ Finding the average age of an employee at the time of hire """
    avg_emp_age_query = """
        SELECT AVG(HireDate - BirthDate) FROM Employee;
        """
    avg_emp_age = cursor.execute(avg_emp_age_query).fetchone()[0]
    print(f'The average age of an employee at the time of hire is {avg_emp_age:.2f}')

# Finding how the average age of employee at hire vary by city
def emp_hire_age_by_city():
    """ Finding the differences is average hire age by city """
    avg_hire_age_by_city_query = """
        SELECT AVG(HireDate - BirthDate), City
        FROM Employee
        GROUP BY City
        ORDER BY AVG(HireDate - BirthDate) DESC;
        """
    avg_hire_age_by_city = cursor.execute(avg_hire_age_by_city_query).fetchall()
    print(f'The average age of hire by city is: {avg_hire_age_by_city}')


# finding the ten most expensive items and their suppliers
def most_exp_items_suppliers():
    """ Finding the ten most expensive items and their respective suppliers """
    exp_items_supp_query = """
        SELECT ProductName, CompanyName
        FROM Product as prod
        INNER JOIN Supplier as sup
        ON prod.SupplierID = sup.ID
        ORDER BY UnitPrice DESC
        LIMIT 10;
        """
    exp_items_supp = cursor.execute(exp_items_supp_query).fetchall()
    print(f'The ten most expensive items and their respective suppliers are: {exp_items_supp}')

# largest category by number of unique products
def largest_by_products():
    """ Finding the largest category by number of unique products """
    lar_cat_by_unique_prods_query = """
        SELECT CategoryName
        FROM Category as cat
        INNER JOIN Product as prod
        ON cat.ID = prod.CategoryID
        INNER JOIN OrderDetail as or_dt
        ON prod.Id = or_dt.ProductId
        GROUP BY CategoryName
        ORDER BY SUM(DISTINCT Quantity) DESC
        LIMIT 1;
        """
    lar_cat_by_unique_prods = cursor.execute(lar_cat_by_unique_prods_query).fetchone()[0]
    print(f'The largest category by number of unique products is {lar_cat_by_unique_prods}')

#which employee has the most territories
def emp_most_terr():
    """ Finding the employee with the most territories """
    emp_with_most_terr_query = """
    SELECT FirstName, LastName
    FROM Employee as emp
    INNER JOIN EmployeeTerritory as et
    ON emp.Id = et.EmployeeID
    GROUP BY EmployeeId
    ORDER BY COUNT(DISTINCT TerritoryId) DESC
    LIMIT 1;
    """
    emp_with_most_terr_first_name = cursor.execute(emp_with_most_terr_query).fetchone()[0]
    emp_with_most_terr_last_name = cursor.execute(emp_with_most_terr_query).fetchone()[1]
    # name = []
    # for i in emp_with_most_terr:
    #     name.append(str(i[0:] + i[:1]))
    print(f'The employee with the most territories is {emp_with_most_terr_first_name} {emp_with_most_terr_last_name}')


#link to blog: https://medium.com/@esmi2022/example-data-science-interview-questions-and-answers-951d9a8b23a6?sk=69c1a35cd6ef1587eb2461639e77220e

<filename>sprintchallenge/northwind.py
"""can only import sqlite3 and other modules we write """
import sqlite3 as sql

""" Answering questions about the northwind database through SQL queries """

#connecting to the northwind database file and instantiating a cursor
connect = sql.connect("northwind_small.sqlite3")
cursor = connect.cursor()

# finding the ten most expensive items in the db
def highest_price():
    """ Finding the ten most expensive items per unit price in the northwind DB """
    ten_highest_query = """
        SELECT ProductName
        FROM Product
        GROUP BY UnitPrice
        ORDER BY UnitPrice DESC
        LIMIT 10; """
    ten_highest = cursor.execute(ten_highest_query).fetchall()
    print(f' The ten most expensive items per unit price are: {ten_highest}')

# averrage age of an employee at the time of hire
def average_emp_age():
    """ Finding the average age of an employee at the time of hire """
    avg_emp_age_query = """
        SELECT AVG(HireDate - BirthDate) FROM Employee;
        """
    avg_emp_age = cursor.execute(avg_emp_age_query).fetchone()[0]
    print(f'The average age of an employee at the time of hire is {avg_emp_age:.2f}')

# Finding how the average age of employee at hire vary by city
def emp_hire_age_by_city():
    """ Finding the differences is average hire age by city """
    avg_hire_age_by_city_query = """
        SELECT AVG(HireDate - BirthDate), City
        FROM Employee
        GROUP BY City
        ORDER BY AVG(HireDate - BirthDate) DESC;
        """
    avg_hire_age_by_city = cursor.execute(avg_hire_age_by_city_query).fetchall()
    print(f'The average age of hire by city is: {avg_hire_age_by_city}')


# finding the ten most expensive items and their suppliers
def most_exp_items_suppliers():
    """ Finding the ten most expensive items and their respective suppliers """
    exp_items_supp_query = """
        SELECT ProductName, CompanyName
        FROM Product as prod
        INNER JOIN Supplier as sup
        ON prod.SupplierID = sup.ID
        ORDER BY UnitPrice DESC
        LIMIT 10;
        """
    exp_items_supp = cursor.execute(exp_items_supp_query).fetchall()
    print(f'The ten most expensive items and their respective suppliers are: {exp_items_supp}')

# largest category by number of unique products
def largest_by_products():
    """ Finding the largest category by number of unique products """
    lar_cat_by_unique_prods_query = """
        SELECT CategoryName
        FROM Category as cat
        INNER JOIN Product as prod
        ON cat.ID = prod.CategoryID
        INNER JOIN OrderDetail as or_dt
        ON prod.Id = or_dt.ProductId
        GROUP BY CategoryName
        ORDER BY SUM(DISTINCT Quantity) DESC
        LIMIT 1;
        """
    lar_cat_by_unique_prods = cursor.execute(lar_cat_by_unique_prods_query).fetchone()[0]
    print(f'The largest category by number of unique products is {lar_cat_by_unique_prods}')

#which employee has the most territories
def emp_most_terr():
    """ Finding the employee with the most territories """
    emp_with_most_terr_query = """
    SELECT FirstName, LastName
    FROM Employee as emp
    INNER JOIN EmployeeTerritory as et
    ON emp.Id = et.EmployeeID
    GROUP BY EmployeeId
    ORDER BY COUNT(DISTINCT TerritoryId) DESC
    LIMIT 1;
    """
    emp_with_most_terr_first_name = cursor.execute(emp_with_most_terr_query).fetchone()[0]
    emp_with_most_terr_last_name = cursor.execute(emp_with_most_terr_query).fetchone()[1]
    # name = []
    # for i in emp_with_most_terr:
    #     name.append(str(i[0:] + i[:1]))
    print(f'The employee with the most territories is {emp_with_most_terr_first_name} {emp_with_most_terr_last_name}')


#link to blog: https://medium.com/@esmi2022/example-data-science-interview-questions-and-answers-951d9a8b23a6?sk=69c1a35cd6ef1587eb2461639e77220e

can only import sqlite3 and other modules we write Answering questions about the northwind database through SQL queries #connecting to the northwind database file and instantiating a cursor # finding the ten most expensive items in the db Finding the ten most expensive items per unit price in the northwind DB SELECT ProductName         FROM Product         GROUP BY UnitPrice         ORDER BY UnitPrice DESC         LIMIT 10; # averrage age of an employee at the time of hire Finding the average age of an employee at the time of hire SELECT AVG(HireDate - BirthDate) FROM Employee; # Finding how the average age of employee at hire vary by city Finding the differences is average hire age by city SELECT AVG(HireDate - BirthDate), City         FROM Employee         GROUP BY City         ORDER BY AVG(HireDate - BirthDate) DESC; # finding the ten most expensive items and their suppliers Finding the ten most expensive items and their respective suppliers SELECT ProductName, CompanyName         FROM Product as prod         INNER JOIN Supplier as sup         ON prod.SupplierID = sup.ID         ORDER BY UnitPrice DESC         LIMIT 10; # largest category by number of unique products Finding the largest category by number of unique products SELECT CategoryName         FROM Category as cat         INNER JOIN Product as prod         ON cat.ID = prod.CategoryID         INNER JOIN OrderDetail as or_dt         ON prod.Id = or_dt.ProductId         GROUP BY CategoryName         ORDER BY SUM(DISTINCT Quantity) DESC         LIMIT 1; #which employee has the most territories Finding the employee with the most territories SELECT FirstName, LastName     FROM Employee as emp     INNER JOIN EmployeeTerritory as et     ON emp.Id = et.EmployeeID     GROUP BY EmployeeId     ORDER BY COUNT(DISTINCT TerritoryId) DESC     LIMIT 1; # name = [] # for i in emp_with_most_terr: #     name.append(str(i[0:] + i[:1])) #link to blog: https://medium.com/@esmi2022/example-data-science-interview-questions-and-answers-951d9a8b23a6?sk=69c1a35cd6ef1587eb2461639e77220e