Data science: Join three tables

Sales record only an employee id; employees record a department id. Join all three tables to total sales per department, without losing any sale.

What to build

A program that totals sales per department through two joins.

Requirements

  • For each department print NAME: TOTAL, ordered by total from highest and equal totals alphabetically, including departments with no sales.
  • Sales by an employee whose department id is unknown are added to no department, and sales by an unknown employee to unknown employee; print each of these lines (in that order, after the departments) only if it is not 0.
  • Then print total: X (all sales) and best seller: NAME (X), the known employee with the highest total (the first in employee order if equal), or best seller: none if no known employee sold anything.

Input

Line 1: d. Then d lines ID,NAME. Next line: e. Then e lines ID,NAME,DEPT_ID. Next line: s. Then s lines EMP_ID,AMOUNT (whole numbers).

Output

The department lines, perhaps the two special lines, the total and the best seller.

Constraints

1 ≤ d, e ≤ 1000; 0 ≤ s ≤ 10000; amounts are positive.

Example

Input:

2
D1,Sales
D2,Support
3
E1,Asha,D1
E2,Ben,D1
E3,Chen,D9
5
E1,100
E2,50
E1,30
E3,20
E8,5

Output:

Sales: 180
Support: 0
no department: 20
unknown employee: 5
total: 205
best seller: Asha (130)

Why it works

Two dictionary indexes turn a three-table join into one pass over the sales, and giving unmatched sales their own lines means the department totals plus those lines always add up to the grand total.

Examples

Example 1 · Input
2
D1,Sales
D2,Support
3
E1,Asha,D1
E2,Ben,D1
E3,Chen,D9
5
E1,100
E2,50
E1,30
E3,20
E8,5
Expected output
Sales: 180
Support: 0
no department: 20
unknown employee: 5
total: 205
best seller: Asha (130)
Example 2 · Input
1
A,Alpha
1
e,Eve,A
0
Expected output
Alpha: 0
total: 0
best seller: none

Prompts inside input() are ignored when checking, so input("n: ") is fine.

Hints

Hint 1

Index departments by id and employees by id, then go through the sales once, adding each amount to the right place.

Hint 2

Start every department's total at 0, so departments with no sales still appear.

Solution

Show one correct solution (try it yourself first)
d = int(input())
departments = {}
for _ in range(d):
    did, name = input().split(",")
    departments[did] = name
e = int(input())
employees = {}
for _ in range(e):
    eid, name, did = input().split(",")
    employees[eid] = (name, did)
s = int(input())

totals = {name: 0 for name in departments.values()}
by_employee = {eid: 0 for eid in employees}
no_department = unknown = grand = 0
for _ in range(s):
    eid, amount = input().split(",")
    amount = int(amount)
    grand += amount
    if eid not in employees:
        unknown += amount
        continue
    by_employee[eid] += amount
    did = employees[eid][1]
    if did in departments:
        totals[departments[did]] += amount
    else:
        no_department += amount

for name, total in sorted(totals.items(), key=lambda t: (-t[1], t[0])):
    print(f"{name}: {total}")
if no_department:
    print(f"no department: {no_department}")
if unknown:
    print(f"unknown employee: {unknown}")
print(f"total: {grand}")
best = None
for eid, amount in by_employee.items():
    if amount > 0 and (best is None or amount > best[1]):
        best = (employees[eid][0], amount)
print(f"best seller: {best[0]} ({best[1]})" if best else "best seller: none")

Lesson: Joining tables →