• (BRILLIA INC)
• (PHARMACIELO LTD)
• How to compare firms to the industry “norm”?

• Myriad data sources
• External data (marketing data, economic data, etc.)
• Various internal systems (invoices, production, etc.)
• Aggregation
• Aggregation can require combining data with itself
• Analysis, cleaning, and verification
• Comparing observations, interpolating, etc.

• Database

• Table 1: GL_Detail

• Table 2: Chart_of_Accounts

• Relations often through “keys” that link tables
• Primary key is used to define an observation (row) in a table
• Foreign key is included in a table to link to the primary key of another table (link the rows)
• E.g. account_id is a foreign key in GL_Detail, and the primary key in Chart_of_Accounts

• Relationships can be singular or multiple
• 1:1 – one row in table 1 matches one row in table 2
• 1:m – one row in T1 matches multiple rows in T2
• E.g. 1 employee matches with multiple salary payments
• m:1 – multiple rows in T1 match with one row in T2
• E.g. multiple employees will match with 1 manager
• m:m – multiple rows in T1 match multiple rows in T2
• E.g. employees could be in multiple committees, and the committees have multiple employees in them

• Base standard addressing users, business units, segments, and tax
• General Ledger (chart of accounts, GL, trial balance)
• Order to Cash subledger
• Procure to Pay subledger
• Inventory subledger
• Fixed Asset subledger

• SQL is the language used to interact with RDBs
• SQL playground: sqlbolt.com
• Do you need to know SQL?
• You should understand conceptually the moving parts
• You should understand what it can and can’t do
• Which you can ask LLMs
• Learn it if you need it, are interested, or taking the ISC section of the CPA

sqlbolt.com

• Inner: just keep rows that match
• Outer: keep all rows
• Left/right: keep all rows from one table
• Cross: every row in one table for every row in the other
• Self: joining a table with itself (could be inner, outer, or left/right)
• Union: concatenating datasets, e.g. adding rows together, not columns together

• Just keep rows that match (join keys are in both tables)
• Most common join (JOIN keyword in SQL is inner)

• Keeps all rows in one table
• Used to add columns from another table without altering original rows
• Left and right join are the same with swapped table orders
• A left join B = B right join A

• Creates all combinations of rows
• Every row in T1 will be merged with every row in T2
• Used to expand datasets
• Example: going from yearly to monthly
• Used to get all combinations of values
• Example: match every buyer to every seller to find best trades

• Join tables based on data from one being within a range based on columns from the other
• E.g. Get last year of transactions (with fiscal year end)
• SQL: b.date BETWEEN a.last_fye AND a.fye
• Used for time-series, binning, finding overlaps, etc.
• Considering overlaps is key to avoid duplication

• Most accounting/financial data is time-based, range merges very common
• Returns Dataset

• Financials Dataset

• Connecting merges are a common use of multiple merges
• Often built one merge at a time
• Transaction Dataset
• Customer Dataset
• Financials Dataset

• Keys work for perfect matches (equality)
• What about when there is no key?
• Data from external source
• Data with noise or errors
• Data with non-perfect overlap (e.g. spatial data)
• Statistical / fuzzy matching used in these cases

• Calculations across multiple rows
• Counting, summing, averaging, regressions, etc.
• Aggregations collapse multiple rows into one
• Often combined with merges
• E.g. total expenses by vendor within date range
• Often done in “subquery” and merged

• Adding yearly returns to financial dataset
• Returns Dataset
• Financials Dataset
• sub-query

• The same data can be aggregated many different ways
• How we aggregate changes what information we get

• Group by Year
• Average across location, within year

• Group by Year and Month
• Average across location, within year and month

• Group by Year and State
• Average across City and month/day, within year and state

• Group by City
• Average across City, within year/month/day

• Lead/Lag variables can also be merges
• JOIN ON ticker
• AND fyear = fyear + 1