medium · ~12 min
Finance needs a report of total invoiced spend per CRM account by reconciling accounts against invoices from a legacy billing feed.
accounts
invoices
Because company names were typed manually in both systems, the same company can appear with inconsistent letter casing or stray leading/trailing spaces (for example ' ACME CORP ' and 'Acme Corp').
' ACME CORP '
'Acme Corp'
total_invoiced
account_id
account_name
Input contract: account_id is unique and non-null in accounts, and normalized account_name values are unique across accounts. invoice_id is unique and non-null in invoices. Both account_name and company are non-null strings that may have mixed case and leading/trailing spaces; amount is a non-null float.
invoice_id
company
amount