toyeiei

-- count only distinct country in table customers
SELECT COUNT(DISTINCT country)
FROM customers;

toyeiei

-- Find key statistics for column total in table invoices
SELECT
  COUNT(total),
  AVG(total),
  SUM(total),
  MIN(total),
  MAX(total)
FROM invoices;

-- use AS to rename columns

toyeiei

-- count number of customers by country
SELECT
  country,
  COUNT(*) AS n
FROM customers
GROUP BY country;

toyeiei

-- QUIZ 1
-- count number of songs by genre
SELECT 
  genres.name,
  COUNT(*) AS n
FROM genres
JOIN tracks
  ON genres.genreid = tracks.genreid
GROUP BY genres.name;

toyeiei

# input
animals = ['dog', 'cat', 'cat', 'dog', 'dog', 'dog', 'cat', 'dog', 'hippo']

# expected output
# result = {'dog':5, 'cat':3, 'hippo':1}

# create empty dict to save our output
result = {}

# we write for loop and if-else

toyeiei

# write a reusable function
def count_item(input_list):
  """count item in a list, return a dict"""
  result = {}
  for item in input_list:
    if item in result:
      result[item] += 1
    else:
      result[item] = 1
  return result

toyeiei

# input
animals = ['dog', 'cat', 'cat', 'dog', 'dog', 'dog', 'cat', 'dog', 'hippo']
genders = ['M', 'F', 'F', 'F', 'M']
balls = ['red', 'red', 'blue', 'blue', 'blue', 'black']

# test function
print(count_item(animals))
print(count_item(genders))
print(count_item(balls))

toyeiei

# write function for this analysis
def calculate_stat(data, k, v):
  """
  input: data, idx key, idx value
  output: dictionary with key: value pair
  """
  result = {}
  for row in data:
    key = row.split(",")[k] 
    value = int(row.split(",")[v])

toyeiei

# cleaing data
data = data.split("\n")
data = data[1:]

# see what data looks like
for row in data[:10]:
  print(row)
  
# preliminary analysis
result = {}

toyeiei

# test function
calculate_stat(data, 0, 4) # sum births by year
calculate_stat(data, 1, 4) # sum births by month
calculate_stat(data, 2, 4) # sum births by dom
calculate_stat(data, 3, 4) # sum births by dow