import pandas as pd
import sqlite3
from pandas import DataFrame 

n_conn = sqlite3.connect('northwind_small.sqlite3')
n_curs = n_conn.cursor()


# What are the ten most expensive items (per unit price) in the database?
query = """
SELECT ProductName, UnitPrice
FROM Product
ORDER BY UnitPrice DESC
LIMIT 10
"""
n_curs.execute(query)
print(n_curs.fetchall())


# What is the average age of an employee at the time of their hiring? (Hint: a
# lot of arithmetic works with dates.)

query = """
SELECT AVG(HireDate-BirthDate)
FROM Employee
"""
n_curs.execute(query)
print(n_curs.fetchall())

# answer: 37.22

# (*Stretch*) How does the average age of employee at hire vary by city?
query = """SELECT City, AVG(HireDate-BirthDate)
FROM Employee
GROUP BY City
"""
n_curs.execute(query)
print(n_curs.fetchall())

# What are the ten most expensive items (per unit price) 
# in the database *and* their suppliers?
query = """
SELECT ProductName, UnitPrice, CompanyName
FROM Product as p
JOIN Supplier as s
ON p.SupplierID = s.ID
ORDER BY UnitPrice DESC
LIMIT 10
"""
n_curs.execute(query)
print(n_curs.fetchall())

# What is the largest category (by number of unique products in it)?
query = """
SELECT CategoryName, COUNT(CategoryName)
FROM Category as c
JOIN Product as p
ON c.ID=p.CategoryID
GROUP BY CategoryName
ORDER by COUNT(CategoryName) DESC
"""
n_curs.execute(query)
print(n_curs.fetchall())

# largest category is Confections 13

# (*Stretch*) Who's the employee with the most territories? Use `TerritoryId`
# (not name, region, or other fields) as the unique identifier for territories.
# EMPLOYEE ID 7

query = """
SELECT EmployeeId, TerritoryId, COUNT(DISTINCT TerritoryId)
FROM EmployeeTerritory
GROUP BY EmployeeId
ORDER BY COUNT(DISTINCT TerritoryId) DESC
"""
n_curs.execute(query)
print(n_curs.fetchall())


[((5, 9, 5, 51), 'sqlite3.connect', 'sqlite3.connect', ({(5, 25, 5, 50): '"""northwind_small.sqlite3"""'}, {}), "('northwind_small.sqlite3')", False, 'import sqlite3\n')]