Analyze Company Financials with SQL

Query real SEC filing data to compare company financials across sectors.

Introduction

30 Second Summary

Every quarter, public companies publish detailed financial reports showing exactly how much money they made, spent, and kept. Financial analysts use SQL to dig through this data, compare companies, and spot trends that drive investment decisions.

In this project, you will build a financial analysis database with real company data and write SQL queries that answer the same questions junior financial analysts face on the job.

What You'll Build

A SQLite database containing revenue, net income, and other key metrics for 30 public companies across 5 industry sectors, queried with SQL to produce analyst-grade financial insights.

By the end of this project, you'll have:

  • A SQLite database with 3 related tables storing real financial data from SEC filings for 30 well-known companies.
  • A collection of 10+ SQL queries covering filtering, sorting, aggregation, joins, and calculated financial metrics like profit margin and return on assets.
  • A sector comparison report that ranks industries by profitability. This is exactly the type of output hiring managers expect from junior analysts.
  • Secret Mission: Write advanced queries using window functions to rank companies by revenue growth and calculate running totals.

Are there any prerequisites?

A basic understanding of financial statements (income statement, balance sheet) is helpful but not strictly required. No programming or SQL experience is needed. This project starts from zero and teaches you everything step by step.

Install DB Browser for SQLite

To analyze financial data with SQL, you need a tool that lets you write queries and see results instantly.

DB Browser for SQLite is a free, visual application that gives you a spreadsheet-like interface on top of a SQL database. In this step, you will install it, create your first database file, and get familiar with the interface where you will write all your queries.

In this step, get ready to:
  • Download and install DB Browser for SQLite.
  • Create a new empty database file for your financial data.
  • Explore the DB Browser interface and find the Execute SQL tab.
Download and install DB Browser for SQLite

DB Browser for SQLite is a free, open-source application that runs on your computer. There is nothing to sign up for and no account to create.

  • Open your web browser and go to the DB Browser for SQLite download page:

sqlitebrowser.org/dl

macOS

  • Under the macOS section, click DB Browser for SQLite (Universal) to download the installer.
  • Open the downloaded .dmg file.
  • Drag the DB Browser for SQLite icon into your Applications folder.

Windows

  • Under the Windows section, click DB Browser for SQLite - Standard installer for 64-bit Windows to download the installer.
  • Open the downloaded installer file.
  • Click through the installer, accepting the default settings at each screen.
  • Click Install and wait for the installation to finish.

Which version am I installing?

You are installing version 3.13.1, which is the latest stable release. This version includes a full SQL query editor with syntax highlighting and command history.

Download not starting?

Some browsers block downloads from unfamiliar sites. If your download does not start, look for a blocked-download notification in your browser's toolbar and allow it.

help me download and install DB Browser for SQLite

Launch DB Browser and create a new database

A database file is where all your financial data will live. You will create one empty file now and fill it with tables and data in the next step.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type DB Browser for SQLite and press Enter to open it.
  • Click New Database in the toolbar at the top of the window.
  • In the save dialog, navigate to a folder you can find easily (like your Desktop or Documents).
  • Name the file company_financials.db and click Save.

What happens after you click Save?

DB Browser immediately opens an Edit table definition dialog. This is a shortcut for creating a table using the GUI. You do not need it because you will create tables by writing SQL in the next step.

  • Click Cancel to dismiss the Edit table definition dialog.

You should now see the main DB Browser window with your company_financials.db file name in the title bar.

Don't see the database name in the title bar?

If you accidentally closed the dialog before saving, go to File in the menu bar, then click New Database to try again.

help me create a new database in DB Browser for SQLite

Explore the DB Browser interface

DB Browser has four main tabs across the top of the window. Each tab serves a different purpose, but you will spend almost all your time in one of them.

  • Look at the four tabs below the toolbar:
  • Database Structure shows your tables and their columns.
  • Browse Data lets you view rows of data in a spreadsheet-like grid.
  • Edit Pragmas controls database-level settings (you can skip this one).
  • Execute SQL is where you write and run SQL queries.
  • Click the Execute SQL tab.

You should see a large text area where you can type SQL queries. This is your workspace for the rest of the project.

Run queries with a keyboard shortcut

You can run SQL statements by pressing Ctrl+Enter (Windows) or Cmd+Enter (macOS) instead of clicking the play button. This makes running queries much faster once you get into a rhythm.

Your environment is ready. Next up, you will create your database tables and load them with real financial data from public company filings.

Create Your Financial Database Tables

You have an empty database open and ready to go. Now you need to give it structure.

Financial data lives in multiple related tables. Companies belong to sectors, and each company has financial results for different years. In this step, you will create three tables that mirror how real financial databases are organized and then load them with data from actual SEC filings.

In this step, get ready to:
  • Create tables for sectors, companies, and financial data using SQL.
  • Load real financial data for 30 public companies across 5 industry sectors.
  • Verify that your database contains 60 rows of financial data spanning fiscal years 2023 and 2024.
Create the sectors and companies tables

Every relational database starts with a schema. Your first two tables define which sectors exist and which companies belong to each sector.

  • In the Execute SQL tab, create the sectors and companies tables by running this SQL:
-- Create the sectors table to categorize companies by industry
CREATE TABLE sectors (
    sector_id INTEGER PRIMARY KEY,
    sector_name TEXT NOT NULL
);

-- Create the companies table with a foreign key linking to sectors
CREATE TABLE companies (
    company_id INTEGER PRIMARY KEY,
    ticker TEXT NOT NULL,
    company_name TEXT NOT NULL,
    sector_id INTEGER,
    FOREIGN KEY (sector_id) REFERENCES sectors(sector_id)
);

What does this code do?

CREATE TABLE defines a new table in your database. Each column has a name and a data type (INTEGER for numbers, TEXT for words).

PRIMARY KEY means that column uniquely identifies each row. FOREIGN KEY creates a relationship between tables. The sector_id in companies points back to the sectors table, linking each company to its industry.

  • Click the Run button (play icon) or press Ctrl+Enter to execute the SQL.
  • Click the Database Structure tab. You should see sectors and companies listed as tables.

Don't see the tables?

Make sure each statement ends with a semicolon. DB Browser requires semicolons to separate multiple statements. Also check for typos in column names or data types.

Help me fix my CREATE TABLE statement

Create the financials table

The financials table stores the actual numbers from company annual reports. Each row represents one company's results for one fiscal year. All monetary values are in millions of USD.

  • Click back to the Execute SQL tab and run this SQL to create the financials table:
-- Create the financials table to store annual financial data
CREATE TABLE financials (
    financial_id INTEGER PRIMARY KEY,
    company_id INTEGER,
    fiscal_year INTEGER NOT NULL,
    revenue REAL,
    net_income REAL,
    total_assets REAL,
    total_equity REAL,
    operating_cash_flow REAL,
    FOREIGN KEY (company_id) REFERENCES companies(company_id)
);

What does this code do?

This table uses REAL for monetary columns because financial values can be large numbers. The company_id foreign key links each financial record back to a specific company. Together with the fiscal_year column, you can track how a company performs over time.

  • Click Run to execute.
  • Check the Database Structure tab again. You should now see three tables: sectors, companies, and financials.

Seeing an error about the table already existing?

If you accidentally run the CREATE TABLE statement twice, you will get an error saying the table already exists. This is normal. You only need to create each table once.

Help me fix a table already exists error in SQLite

Populate the tables with real company data

Now you will load real financial data sourced from SEC EDGAR 10-K filings. This includes 5 sectors, 30 well-known public companies, and 2 years of financial results per company.

  • In the Execute SQL tab, clear any previous SQL and run the following to insert the 5 sectors:
-- Insert the 5 industry sectors
INSERT INTO sectors (sector_id, sector_name) VALUES (1, 'Technology');
INSERT INTO sectors (sector_id, sector_name) VALUES (2, 'Healthcare');
INSERT INTO sectors (sector_id, sector_name) VALUES (3, 'Financial Services');
INSERT INTO sectors (sector_id, sector_name) VALUES (4, 'Consumer Goods');
INSERT INTO sectors (sector_id, sector_name) VALUES (5, 'Energy');

What does INSERT INTO do?

INSERT INTO adds a new row to a table. You specify the table name, the columns you are filling, and the values for each column. The values must match the order of the columns listed.

  • Click Run to insert the sectors.
  • Now insert the 30 companies by running this SQL:
-- Technology companies (sector_id = 1)
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (1, 'AAPL', 'Apple', 1);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (2, 'MSFT', 'Microsoft', 1);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (3, 'GOOGL', 'Alphabet', 1);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (4, 'AMZN', 'Amazon', 1);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (5, 'META', 'Meta Platforms', 1);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (6, 'NVDA', 'NVIDIA', 1);

-- Healthcare companies (sector_id = 2)
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (7, 'UNH', 'UnitedHealth Group', 2);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (8, 'JNJ', 'Johnson & Johnson', 2);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (9, 'PFE', 'Pfizer', 2);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (10, 'ABBV', 'AbbVie', 2);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (11, 'MRK', 'Merck', 2);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (12, 'LLY', 'Eli Lilly', 2);

-- Financial Services companies (sector_id = 3)
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (13, 'JPM', 'JPMorgan Chase', 3);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (14, 'BAC', 'Bank of America', 3);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (15, 'V', 'Visa', 3);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (16, 'MA', 'Mastercard', 3);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (17, 'GS', 'Goldman Sachs', 3);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (18, 'MS', 'Morgan Stanley', 3);

-- Consumer Goods companies (sector_id = 4)
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (19, 'PG', 'Procter & Gamble', 4);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (20, 'KO', 'Coca-Cola', 4);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (21, 'PEP', 'PepsiCo', 4);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (22, 'NKE', 'Nike', 4);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (23, 'MCD', 'McDonalds', 4);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (24, 'WMT', 'Walmart', 4);

-- Energy companies (sector_id = 5)
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (25, 'XOM', 'ExxonMobil', 5);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (26, 'CVX', 'Chevron', 5);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (27, 'COP', 'ConocoPhillips', 5);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (28, 'SLB', 'SLB', 5);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (29, 'EOG', 'EOG Resources', 5);
INSERT INTO companies (company_id, ticker, company_name, sector_id) VALUES (30, 'DVN', 'Devon Energy', 5);
  • Click Run to insert all 30 companies.
  • Finally, insert the financial data for all 30 companies across fiscal years 2023 and 2024 by running this SQL:
-- Financial data sourced from SEC EDGAR 10-K filings
-- All monetary values in millions of USD

-- Apple (AAPL) - FY ends September
INSERT INTO financials VALUES (1, 1, 2023, 383285, 96995, 352583, 62146, 110543);
INSERT INTO financials VALUES (2, 1, 2024, 391035, 93736, 364980, 56950, 118254);

-- Microsoft (MSFT) - FY ends June
INSERT INTO financials VALUES (3, 2, 2023, 211915, 72361, 411976, 206223, 87582);
INSERT INTO financials VALUES (4, 2, 2024, 245122, 88136, 512163, 268477, 118548);

-- Alphabet (GOOGL) - Calendar year
INSERT INTO financials VALUES (5, 3, 2023, 307394, 73795, 402392, 264768, 101746);
INSERT INTO financials VALUES (6, 3, 2024, 350018, 100681, 430266, 292717, 125300);

-- Amazon (AMZN) - Calendar year
INSERT INTO financials VALUES (7, 4, 2023, 574785, 30425, 527854, 201875, 84946);
INSERT INTO financials VALUES (8, 4, 2024, 637997, 59248, 624894, 285972, 115877);

-- Meta Platforms (META) - Calendar year
INSERT INTO financials VALUES (9, 5, 2023, 134902, 39098, 229623, 153168, 71113);
INSERT INTO financials VALUES (10, 5, 2024, 164501, 62360, 276054, 182637, 91648);

-- NVIDIA (NVDA) - FY ends January
INSERT INTO financials VALUES (11, 6, 2023, 60922, 29760, 65728, 42978, 28090);
INSERT INTO financials VALUES (12, 6, 2024, 130497, 72880, 111601, 65899, 64089);

-- UnitedHealth Group (UNH) - Calendar year
INSERT INTO financials VALUES (13, 7, 2023, 371622, 22381, 273721, 86647, 29092);
INSERT INTO financials VALUES (14, 7, 2024, 400278, 14401, 293258, 91738, 24205);

-- Johnson & Johnson (JNJ) - Calendar year
INSERT INTO financials VALUES (15, 8, 2023, 85159, 35153, 167558, 68774, 22774);
INSERT INTO financials VALUES (16, 8, 2024, 88821, 14069, 175389, 71670, 23432);

-- Pfizer (PFE) - Calendar year
INSERT INTO financials VALUES (17, 9, 2023, 58496, 2119, 226501, 89014, 5594);
INSERT INTO financials VALUES (18, 9, 2024, 63627, 8038, 219225, 88354, 12166);

-- AbbVie (ABBV) - Calendar year
INSERT INTO financials VALUES (19, 10, 2023, 54318, 4863, 134711, 3560, 22192);
INSERT INTO financials VALUES (20, 10, 2024, 56334, 4281, 134919, 4400, 24031);

-- Merck (MRK) - Calendar year
INSERT INTO financials VALUES (21, 11, 2023, 60115, 365, 106675, 37581, 13553);
INSERT INTO financials VALUES (22, 11, 2024, 64168, 17117, 113203, 40318, 16216);

-- Eli Lilly (LLY) - Calendar year
INSERT INTO financials VALUES (23, 12, 2023, 34124, 5240, 64006, 8552, 7557);
INSERT INTO financials VALUES (24, 12, 2024, 45040, 8907, 75866, 10773, 9938);

-- JPMorgan Chase (JPM) - Calendar year
INSERT INTO financials VALUES (25, 13, 2023, 158104, 49552, 3875393, 327878, 42410);
INSERT INTO financials VALUES (26, 13, 2024, 177556, 58471, 4002814, 344845, 48617);

-- Bank of America (BAC) - Calendar year
INSERT INTO financials VALUES (27, 14, 2023, 98581, 26515, 3180151, 292529, 36261);
INSERT INTO financials VALUES (28, 14, 2024, 101921, 27132, 3261741, 299845, 37840);

-- Visa (V) - FY ends September
INSERT INTO financials VALUES (29, 15, 2023, 32653, 17273, 90499, 36517, 20793);
INSERT INTO financials VALUES (30, 15, 2024, 35926, 19743, 94511, 38157, 22962);

-- Mastercard (MA) - Calendar year
INSERT INTO financials VALUES (31, 16, 2023, 25098, 11195, 42415, 6369, 12457);
INSERT INTO financials VALUES (32, 16, 2024, 28167, 12873, 44219, 7016, 14185);

-- Goldman Sachs (GS) - Calendar year
INSERT INTO financials VALUES (33, 17, 2023, 46254, 8516, 1641594, 117390, 36777);
INSERT INTO financials VALUES (34, 17, 2024, 53510, 12987, 1676000, 123416, 42231);

-- Morgan Stanley (MS) - Calendar year
INSERT INTO financials VALUES (35, 18, 2023, 54143, 9087, 1193693, 93937, 15960);
INSERT INTO financials VALUES (36, 18, 2024, 61761, 13390, 1215282, 101223, 18450);

-- Procter & Gamble (PG) - FY ends June
INSERT INTO financials VALUES (37, 19, 2023, 82006, 14653, 120829, 47065, 16848);
INSERT INTO financials VALUES (38, 19, 2024, 84039, 14879, 122370, 50559, 19845);

-- Coca-Cola (KO) - Calendar year
INSERT INTO financials VALUES (39, 20, 2023, 45754, 10714, 97703, 25826, 11599);
INSERT INTO financials VALUES (40, 20, 2024, 47061, 10631, 100449, 26370, 12102);

-- PepsiCo (PEP) - Calendar year
INSERT INTO financials VALUES (41, 21, 2023, 91471, 9074, 100495, 17839, 10811);
INSERT INTO financials VALUES (42, 21, 2024, 91854, 9572, 100800, 18231, 11619);

-- Nike (NKE) - FY ends May
INSERT INTO financials VALUES (43, 22, 2023, 51217, 5140, 37531, 13998, 5963);
INSERT INTO financials VALUES (44, 22, 2024, 51362, 5700, 37862, 14430, 7431);

-- McDonalds (MCD) - Calendar year
INSERT INTO financials VALUES (45, 23, 2023, 25494, 8469, 56147, -4707, 9608);
INSERT INTO financials VALUES (46, 23, 2024, 25920, 8223, 56958, -4833, 9667);

-- Walmart (WMT) - FY ends January
INSERT INTO financials VALUES (47, 24, 2023, 611289, 11680, 243197, 83861, 28841);
INSERT INTO financials VALUES (48, 24, 2024, 648125, 15511, 252399, 91395, 36437);

-- ExxonMobil (XOM) - Calendar year
INSERT INTO financials VALUES (49, 25, 2023, 334697, 36010, 376317, 204802, 55369);
INSERT INTO financials VALUES (50, 25, 2024, 339247, 33680, 453475, 263705, 55014);

-- Chevron (CVX) - Calendar year
INSERT INTO financials VALUES (51, 26, 2023, 196913, 21369, 261632, 160957, 35609);
INSERT INTO financials VALUES (52, 26, 2024, 193414, 17661, 256938, 152318, 31492);

-- ConocoPhillips (COP) - Calendar year
INSERT INTO financials VALUES (53, 27, 2023, 58574, 10957, 95924, 48082, 20303);
INSERT INTO financials VALUES (54, 27, 2024, 58662, 9235, 123000, 52000, 20247);

-- SLB (SLB) - Calendar year
INSERT INTO financials VALUES (55, 28, 2023, 33135, 4203, 67842, 26089, 6637);
INSERT INTO financials VALUES (56, 28, 2024, 36289, 4461, 68882, 27311, 6827);

-- EOG Resources (EOG) - Calendar year
INSERT INTO financials VALUES (57, 29, 2023, 23186, 7594, 37957, 25543, 11227);
INSERT INTO financials VALUES (58, 29, 2024, 23668, 7082, 40356, 27010, 11362);

-- Devon Energy (DVN) - Calendar year
INSERT INTO financials VALUES (59, 30, 2023, 14950, 4376, 24293, 11619, 6709);
INSERT INTO financials VALUES (60, 30, 2024, 15072, 3308, 25412, 12058, 6422);

Where does this data come from?

Every number in these INSERT statements comes from real company 10-K annual reports filed with the SEC (Securities and Exchange Commission) through the EDGAR system. These are the same filings that Wall Street analysts use to evaluate companies.

The data covers fiscal years 2023 and 2024 for 30 companies across Technology, Healthcare, Financial Services, Consumer Goods, and Energy. Some companies have fiscal years that end in months other than December (for example, Apple's fiscal year ends in September).

  • Click Run to insert all 60 rows of financial data. DB Browser may show a brief loading indicator.
  • Click the Browse Data tab at the top.
  • Use the table dropdown to select financials. You should see 60 rows of data.
  • Click Write Changes in the toolbar (or press Ctrl+S) to save all changes to your database file.

Seeing fewer than 60 rows?

If you ran the INSERT statements more than once, you may see duplicate data or an error about duplicate primary keys. To fix this, you can drop the financials table and recreate it:

DROP TABLE financials;

Then re-run the CREATE TABLE statement for financials followed by the INSERT statements.

Help me fix duplicate row errors in my SQLite database

✔️ Awesome, I've got everything!

Great. Make sure you clicked Write Changes to save your database.

ⓧ I'd like to double check the full code

Your database should now have 3 tables: sectors (5 rows), companies (30 rows), and financials (60 rows). Use the Browse Data tab and switch between tables using the dropdown to verify the row counts match.

Your database is loaded and ready for analysis. In the next step, you will write your first SELECT queries to explore this data and answer real financial questions.

Query Financial Data with SELECT, WHERE, and ORDER BY

Your database is loaded with real financial data from 30 companies. Now it is time to start asking questions of that data.

In this step, you will write SQL queries that filter, sort, and extract exactly the information you need. These are the same skills a junior financial analyst uses every day to pull reports from company databases.

In this step, get ready to:
  • Write SELECT queries to retrieve company data from your tables.
  • Use WHERE clauses to filter companies by sector, revenue, and profitability.
  • Sort and rank companies using ORDER BY to find top performers.
Write basic SELECT queries

The SELECT statement is how you ask a database for information. Every SQL query you write in this project starts with SELECT.

  • In the Execute SQL tab, clear any existing text and type the following queries:
-- Retrieve all columns and all rows from the companies table
SELECT * FROM companies;

-- Retrieve only the ticker and company name columns
SELECT ticker, company_name FROM companies;

What do these queries do?

SELECT * FROM companies asks for every column in the companies table. The * means "all columns."

SELECT ticker, company_name FROM companies asks for only two specific columns. Financial analysts often work with large datasets, so selecting only the columns you need is a real-world best practice.

  • Click the Run button (the play icon above the SQL editor) or press Ctrl+Return to execute the queries.
  • You should see 30 rows of results in the lower pane. The first query shows all columns (company_id, ticker, company_name, sector_id). The second query shows only ticker and company_name.

Seeing an error instead of results?

  • Make sure each SQL statement ends with a semicolon. DB Browser requires semicolons to separate statements.
  • Check that you typed the table name exactly as companies (lowercase, plural). SQL table names must match what you created in Step 2.

Why is my SELECT query returning an error?

Filter with WHERE clauses

Seeing all 30 companies at once is useful for exploring, but analysts rarely need every row. The WHERE clause lets you filter results to only the rows that match a condition.

  • Clear the SQL editor and type these queries to practice single-condition filters:
-- Find all technology companies (sector_id 1 = Technology)
SELECT * FROM companies
WHERE sector_id = 1;

-- Find all financial records where revenue exceeds $50 billion
-- (revenue is stored in millions, so 50 billion = 50000)
SELECT * FROM financials
WHERE revenue > 50000;

-- Find companies that operated at a loss (negative net income)
SELECT * FROM financials
WHERE net_income < 0;

What do these WHERE clauses do?

  • WHERE sector_id = 1 filters the companies table to show only the 6 technology companies you inserted.
  • WHERE revenue > 50000 finds financial records where a company earned more than $50 billion in revenue. Since all monetary values are stored in millions, 50000 means $50 billion.
  • WHERE net_income < 0 finds any company that reported a loss for that fiscal year. You may see zero rows or a few rows depending on the data.
  • Click Run to execute the queries.
  • The first query should return exactly 6 rows (your 6 technology companies). The second query returns only the companies with revenue above $50 billion.

Now let's combine multiple conditions. The AND keyword lets you require multiple conditions to be true at the same time.

  • Clear the SQL editor and type this query to find technology companies with revenue over $50 billion:
-- Find technology companies with revenue over $50 billion
-- This combines a subquery (to get tech company IDs) with a revenue filter
SELECT * FROM financials
WHERE company_id IN (SELECT company_id FROM companies WHERE sector_id = 1)
AND revenue > 50000;

What is happening in this query?

This query uses a subquery inside the WHERE clause. The inner query (SELECT company_id FROM companies WHERE sector_id = 1) grabs the IDs of all technology companies. The outer query then filters financials to only those companies AND only rows where revenue exceeds 50000.

The AND keyword means both conditions must be true. You can also use OR when you want rows that match either condition.

  • Click Run. You should see only the financial records for technology companies that earned more than $50 billion in a given year.

Getting zero rows or an unexpected result?

  • Double-check that your subquery uses sector_id = 1 (Technology is sector 1 based on the insertion order from Step 2).
  • Make sure you have the closing parenthesis after the subquery and before AND.
  • Remember that revenue is in millions. $50 billion is 50000 in our data, not 50000000000.

Help me debug my WHERE clause with AND

Sort results with ORDER BY

Filtering narrows your results. Sorting organizes them so the most important rows appear first. The ORDER BY clause arranges your results by any column, either ascending or descending.

  • Clear the SQL editor and type these queries to rank companies by financial performance:
-- Rank all financial records by revenue, highest first
SELECT * FROM financials
ORDER BY revenue DESC;

-- Rank by net income, highest first
SELECT * FROM financials
ORDER BY net_income DESC;

What does DESC and ASC mean?

DESC means descending (largest to smallest). ASC means ascending (smallest to largest). If you leave off both, SQL defaults to ASC.

For financial analysis, you almost always want DESC because you are looking for the top performers.

  • Click Run. The results should show 60 rows ordered from the highest revenue (or net income) at the top to the lowest at the bottom.

Now let's combine everything you have learned into one powerful query. A common analyst task is identifying the top performers for a specific time period.

  • Clear the SQL editor and type this query to find the top 10 companies by revenue for fiscal year 2024:
-- Top 10 companies by revenue for fiscal year 2024
-- Combines WHERE (filter to 2024), ORDER BY (rank by revenue), and LIMIT (show only 10)
SELECT * FROM financials
WHERE fiscal_year = 2024
ORDER BY revenue DESC
LIMIT 10;

What does LIMIT do?

LIMIT 10 restricts the output to the first 10 rows after filtering and sorting. Without it, you would see all 30 companies for 2024.

This query pattern (WHERE + ORDER BY + LIMIT) is how analysts build "top N" reports. It mirrors questions like "What were the 10 highest-revenue companies last year?"

  • Click Run. You should see exactly 10 rows, showing only fiscal year 2024 data, ranked from highest revenue to lowest.

Seeing more than 10 rows?

  • Make sure LIMIT 10 appears on its own line after ORDER BY. It must come last in the query.
  • Check that you only have one semicolon at the very end. A semicolon in the middle would split it into two separate queries.

Help me fix my ORDER BY and LIMIT query

You can now pull exactly the data you need from your financial database. In the next step, you will use aggregate functions and GROUP BY to calculate totals, averages, and financial metrics across entire sectors.

Aggregate and Group Financial Data

In the previous step, you filtered and sorted individual rows of financial data. That is useful for finding specific companies, but analysts rarely stop there.

The real power of SQL comes from summarizing data across groups. In this step, you will use aggregate functions to calculate totals, averages, and financial ratios that let you compare entire sectors at a glance.

In this step, get ready to:
  • Use aggregate functions (SUM, AVG, COUNT, MAX, MIN) to summarize financial data.
  • Calculate financial ratios like profit margin, return on assets, and return on equity.
  • Filter grouped results with HAVING to find high-performing sectors.
Use aggregate functions to summarize your data

Instead of looking at one company at a time, aggregate functions collapse many rows into a single summary value. These are the building blocks of every financial report.

  • Clear any previous SQL in the Execute SQL tab, then type the following query:
-- Calculate summary statistics across all financial data
SELECT
  SUM(revenue) AS total_revenue,
  AVG(net_income) AS avg_net_income,
  COUNT(*) AS total_records,
  MAX(revenue) AS highest_revenue,
  MIN(revenue) AS lowest_revenue
FROM financials;
  • Click the Run button (play icon) or press Ctrl+Return (Windows) or Cmd+Return (macOS) to execute the query.

What does this query do?

Each function operates on the entire revenue or net_income column at once:

  • SUM(revenue) adds up every revenue value across all 60 rows.
  • AVG(net_income) calculates the mean net income across all companies and years.
  • COUNT(*) counts the total number of rows (should be 60, since you have 30 companies x 2 years).
  • MAX(revenue) and MIN(revenue) find the single highest and lowest revenue values.

You should see a single row in the results pane with five columns. The total_records column should show 60.

Now let's break these summaries down by sector. Clear the SQL editor and type this query:

-- Calculate average revenue per sector
SELECT
  sector_id,
  AVG(revenue) AS avg_revenue,
  COUNT(*) AS records_per_sector
FROM financials
GROUP BY sector_id;
  • Run the query.

What is GROUP BY?

GROUP BY splits your data into buckets based on a column value. Instead of one grand total, you get one result row per group.

Here, GROUP BY sector_id creates 5 groups (one per sector). The AVG(revenue) then calculates the average revenue within each group separately.

You should see 5 rows in the results pane. Each row shows the average revenue and record count for one sector.

Seeing an error about columns not in GROUP BY?

If you include a column in your SELECT that is not inside an aggregate function and not in the GROUP BY clause, you may get unexpected results. Every non-aggregated column in SELECT must appear in GROUP BY.

help me fix my GROUP BY query

Calculate financial ratios with SQL

Raw revenue numbers only tell part of the story. Analysts calculate ratios to compare companies of different sizes on equal footing. A $10 billion company earning $1 billion has the same profit margin as a $100 million company earning $10 million.

  • Clear the SQL editor and type this query to calculate three key financial ratios:
-- Calculate profit margin, return on assets, and return on equity
SELECT
  company_id,
  fiscal_year,
  revenue,
  net_income,
  ROUND(net_income * 1.0 / revenue * 100, 2) AS profit_margin,
  ROUND(net_income * 1.0 / total_assets * 100, 2) AS return_on_assets,
  ROUND(net_income * 1.0 / total_equity * 100, 2) AS return_on_equity
FROM financials
ORDER BY profit_margin DESC;
  • Run the query.

What do these ratios mean?

  • Profit margin (net_income / revenue * 100): What percentage of every dollar in revenue the company keeps as profit.
  • Return on assets (net_income / total_assets * 100): How efficiently the company uses everything it owns to generate profit.
  • Return on equity (net_income / total_equity * 100): How much profit the company generates for every dollar shareholders have invested.

ROUND(..., 2) rounds each result to 2 decimal places so your output is clean and readable.

You should see 60 rows, each showing a company's financial ratios for one year. The results are sorted by profit margin from highest to lowest.

Why multiply by 1.0?

SQLite stores your monetary values as integers (millions). Dividing two integers in SQLite produces an integer result, which truncates the decimal. Multiplying by 1.0 forces SQLite to treat the division as a decimal calculation.

All your ratios showing 0?

This happens when SQLite performs integer division. Make sure you include * 1.0 in your calculation (e.g., net_income * 1.0 / revenue) to get decimal results.

help me fix my calculated columns showing 0

Filter groups with HAVING

You already know WHERE filters individual rows. But what if you want to filter after grouping? For example, you might want only sectors whose average profit margin exceeds 15%.

  • Clear the SQL editor and type this query to find high-margin sectors:
-- Find sectors with average profit margin above 15%
SELECT
  sector_id,
  ROUND(AVG(net_income * 1.0 / revenue * 100), 2) AS avg_profit_margin
FROM financials
GROUP BY sector_id
HAVING avg_profit_margin > 15
ORDER BY avg_profit_margin DESC;
  • Run the query.

WHERE vs HAVING

  • WHERE filters individual rows before grouping happens. Use it for conditions on raw data (e.g., WHERE fiscal_year = 2024).
  • HAVING filters groups after aggregation. Use it for conditions on calculated values (e.g., HAVING AVG(revenue) > 50000).

Think of it this way: WHERE decides which rows go into the groups. HAVING decides which groups appear in the final results.

You should see only the sectors whose average profit margin is above 15%. Sectors that fall below that threshold are excluded from the results.

Now try a second HAVING query. Clear the editor and type this one to find sectors with high total revenue:

-- Find sectors with total revenue exceeding 500 billion
SELECT
  sector_id,
  SUM(revenue) AS total_sector_revenue,
  COUNT(DISTINCT company_id) AS num_companies
FROM financials
WHERE fiscal_year = 2024
GROUP BY sector_id
HAVING total_sector_revenue > 500000
ORDER BY total_sector_revenue DESC;
  • Run the query.

What does this query do?

This query combines WHERE and HAVING in a single statement. The WHERE fiscal_year = 2024 first narrows the data to only 2024 records. Then GROUP BY sector_id groups what remains by sector.

Finally, HAVING total_sector_revenue > 500000 keeps only sectors whose total revenue exceeds $500 billion (remember, values are stored in millions).

You should see only the sectors that cleared the $500 billion threshold for 2024. The rest are filtered out.

Query returns no rows?

If you see an empty result set, your threshold might be too high for the data. Try lowering 500000 to 100000 and run again. If rows appear, gradually raise the number to find a useful threshold.

Also double-check that you typed HAVING (not WHERE) after the GROUP BY line. WHERE before GROUP BY filters rows. HAVING after GROUP BY filters groups.

help me understand why my HAVING query returns empty results

You can now summarize, calculate ratios, and filter grouped data. Next up, you will join all three tables together to build a complete sector comparison report.

Join Tables and Build a Sector Comparison Report

Your database now holds 60 rows of financial data, and you can filter, sort, and aggregate it with confidence.

There is one problem. Your data lives in three separate tables. To answer a question like "What is Apple's sector?" you would need to manually look up the sector_id in the companies table, then find the matching name in the sectors table. JOIN queries solve this by combining rows from multiple tables in a single query.

In this step, get ready to:
  • Join two and three tables together with INNER JOIN to see complete company data.
  • Build a sector comparison report using JOIN, GROUP BY, and calculated columns.
  • Compare year-over-year performance using a self-join.
Join tables with INNER JOIN

Real financial data always lives across multiple tables. An analyst's first task is usually combining these tables to get a complete picture of each company.

  • Write your first JOIN query by typing the following into the Execute SQL tab:
-- Join companies with their financial data
SELECT companies.ticker,
       companies.company_name,
       financials.fiscal_year,
       financials.revenue,
       financials.net_income
FROM companies
INNER JOIN financials ON companies.company_id = financials.company_id
ORDER BY financials.revenue DESC;

What does this query do?

INNER JOIN connects two tables using a shared column. Here, companies.company_id = financials.company_id tells SQL to match each company with its financial rows.

The result is a single combined table where every row shows a company's name, ticker, AND its financial numbers side by side. Without a JOIN, you would have to run two separate queries and match the rows yourself.

  • Click the Run button (the play icon) or press Cmd+Return (macOS) or Ctrl+Enter (Windows) to execute the query.
  • You should see 60 rows of results showing each company's ticker, name, fiscal year, revenue, and net income sorted by revenue from highest to lowest.

Seeing zero results or an error?

Check that your column names match exactly. The ON clause must reference the correct column names from both tables: companies.company_id and financials.company_id.

If you see a "no such column" error, double-check that you prefixed each column with its table name (e.g. companies.ticker not just ticker).

help me fix my INNER JOIN query

Now let's go bigger. You can chain multiple JOINs to bring in all three tables at once.

  • Clear the previous query and type this three-table JOIN:
-- Join all three tables for a complete view
SELECT sectors.sector_name,
       companies.ticker,
       companies.company_name,
       financials.fiscal_year,
       financials.revenue,
       financials.net_income,
       financials.total_assets
FROM sectors
INNER JOIN companies ON sectors.sector_id = companies.sector_id
INNER JOIN financials ON companies.company_id = financials.company_id
ORDER BY sectors.sector_name, financials.revenue DESC;

What does this query do?

This query chains two INNER JOINs. First it connects sectors to companies using sector_id. Then it connects companies to financials using company_id.

The result shows every company's financial data labeled with its sector name. The ORDER BY sorts by sector first, then by revenue within each sector.

  • Click the Run button to execute the query.
  • You should see 60 rows grouped by sector name, with each company's financials displayed alongside its sector.

Getting duplicate or missing rows?

Make sure both JOIN conditions use the correct linking columns. The first JOIN links on sector_id and the second links on company_id.

If you see only partial data, confirm your sectors table has all 5 rows and your companies table has all 30 rows by running SELECT COUNT(*) FROM sectors; and SELECT COUNT(*) FROM companies;.

help me debug my three-table JOIN

✔️ Awesome, I've got everything!

Great. You can now combine data from multiple tables in a single query. Next you will use this skill to build a real analyst report.

ⓧ I'd like to double check the full code

Here are both JOIN queries from this substep:

-- Two-table JOIN: companies + financials
SELECT companies.ticker,
       companies.company_name,
       financials.fiscal_year,
       financials.revenue,
       financials.net_income
FROM companies
INNER JOIN financials ON companies.company_id = financials.company_id
ORDER BY financials.revenue DESC;

-- Three-table JOIN: sectors + companies + financials
SELECT sectors.sector_name,
       companies.ticker,
       companies.company_name,
       financials.fiscal_year,
       financials.revenue,
       financials.net_income,
       financials.total_assets
FROM sectors
INNER JOIN companies ON sectors.sector_id = companies.sector_id
INNER JOIN financials ON companies.company_id = financials.company_id
ORDER BY sectors.sector_name, financials.revenue DESC;
Build a sector comparison report

A hiring manager reviewing your SQL skills would expect you to produce summary reports that compare business units. This next query combines JOINs, GROUP BY, and calculated columns into a single sector comparison report.

  • Write your sector comparison report by typing this query:
-- Sector comparison report: avg revenue, profit margin, ROA, company count
SELECT sectors.sector_name,
       COUNT(DISTINCT companies.company_id) AS company_count,
       ROUND(AVG(financials.revenue), 0) AS avg_revenue,
       ROUND(AVG(financials.net_income * 100.0 / financials.revenue), 2) AS avg_profit_margin,
       ROUND(AVG(financials.net_income * 100.0 / financials.total_assets), 2) AS avg_return_on_assets
FROM sectors
INNER JOIN companies ON sectors.sector_id = companies.sector_id
INNER JOIN financials ON companies.company_id = financials.company_id
GROUP BY sectors.sector_name
ORDER BY avg_profit_margin DESC;

What does this query do?

This query produces a one-row-per-sector summary with four metrics:

  • company_count counts unique companies per sector using COUNT(DISTINCT).
  • avg_revenue calculates the average revenue across all company-year rows in that sector.
  • avg_profit_margin divides net income by revenue and multiplies by 100 to get a percentage.
  • avg_return_on_assets divides net income by total assets and multiplies by 100 to measure how efficiently each sector uses its assets.

The * 100.0 ensures SQLite performs decimal division instead of integer division. ROUND(..., 2) formats the result to two decimal places.

  • Click the Run button to execute the query.
  • You should see 5 rows (one per sector) with columns for sector name, company count, average revenue, average profit margin, and average return on assets. The rows are sorted by profit margin from highest to lowest.

Seeing NULL values in your results?

NULL appears when a division results in zero or missing data. Check that none of your revenue or total_assets values are zero in the financials table by running SELECT * FROM financials WHERE revenue = 0;.

If you see only 1 row instead of 5, make sure you are grouping by sectors.sector_name (with the table prefix), not just sector_name.

help me fix my sector comparison query

✔️ Awesome, I've got everything!

Your sector comparison report is exactly the type of output a hiring manager would expect from a junior analyst. One more query to go.

ⓧ I'd like to double check the full code

Here is the full sector comparison report query:

-- Sector comparison report: avg revenue, profit margin, ROA, company count
SELECT sectors.sector_name,
       COUNT(DISTINCT companies.company_id) AS company_count,
       ROUND(AVG(financials.revenue), 0) AS avg_revenue,
       ROUND(AVG(financials.net_income * 100.0 / financials.revenue), 2) AS avg_profit_margin,
       ROUND(AVG(financials.net_income * 100.0 / financials.total_assets), 2) AS avg_return_on_assets
FROM sectors
INNER JOIN companies ON sectors.sector_id = companies.sector_id
INNER JOIN financials ON companies.company_id = financials.company_id
GROUP BY sectors.sector_name
ORDER BY avg_profit_margin DESC;
Compare year-over-year performance

Analysts rarely look at a single year in isolation. Comparing this year to last year reveals which companies are growing and which are shrinking.

A self-join connects a table to itself. You will join the financials table to itself to place 2023 and 2024 data on the same row for each company.

  • Write your year-over-year comparison query:
-- Self-join: compare 2023 vs 2024 revenue for each company
SELECT companies.ticker,
       companies.company_name,
       f2023.revenue AS revenue_2023,
       f2024.revenue AS revenue_2024,
       ROUND((f2024.revenue - f2023.revenue) * 100.0 / f2023.revenue, 2) AS revenue_change_pct
FROM financials f2023
INNER JOIN financials f2024
  ON f2023.company_id = f2024.company_id
INNER JOIN companies
  ON f2023.company_id = companies.company_id
WHERE f2023.fiscal_year = 2023
  AND f2024.fiscal_year = 2024
ORDER BY revenue_change_pct DESC;

What does this query do?

This query creates two aliases for the same table: f2023 and f2024. Each alias represents one year of data for the same company.

The ON clause matches rows where company_id is the same in both aliases. The WHERE clause then filters so f2023 only contains 2023 rows and f2024 only contains 2024 rows.

The revenue change percentage formula subtracts last year's revenue from this year's, divides by last year's revenue, and multiplies by 100. Positive values mean growth. Negative values mean the company's revenue shrank.

  • Click the Run button to execute the query.
  • You should see 30 rows (one per company) showing both years of revenue side by side and a percentage change column. Companies at the top grew the most. Companies at the bottom shrank.

Getting more than 30 rows or duplicate companies?

This usually means the WHERE clause is missing or incomplete. You need both conditions: f2023.fiscal_year = 2023 AND f2024.fiscal_year = 2024. Without these filters, every combination of years gets matched.

If you see an error about ambiguous column names, make sure every column reference includes its table alias (f2023.revenue, not just revenue).

help me debug my self-join query

✔️ Awesome, I've got everything!

You have now written every type of JOIN a junior analyst needs. Your queries combine tables, calculate financial metrics, and compare performance across time.

ⓧ I'd like to double check the full code

Here are all the queries from this step:

-- Two-table JOIN: companies + financials
SELECT companies.ticker,
       companies.company_name,
       financials.fiscal_year,
       financials.revenue,
       financials.net_income
FROM companies
INNER JOIN financials ON companies.company_id = financials.company_id
ORDER BY financials.revenue DESC;

-- Three-table JOIN: sectors + companies + financials
SELECT sectors.sector_name,
       companies.ticker,
       companies.company_name,
       financials.fiscal_year,
       financials.revenue,
       financials.net_income,
       financials.total_assets
FROM sectors
INNER JOIN companies ON sectors.sector_id = companies.sector_id
INNER JOIN financials ON companies.company_id = financials.company_id
ORDER BY sectors.sector_name, financials.revenue DESC;

-- Sector comparison report: avg revenue, profit margin, ROA, company count
SELECT sectors.sector_name,
       COUNT(DISTINCT companies.company_id) AS company_count,
       ROUND(AVG(financials.revenue), 0) AS avg_revenue,
       ROUND(AVG(financials.net_income * 100.0 / financials.revenue), 2) AS avg_profit_margin,
       ROUND(AVG(financials.net_income * 100.0 / financials.total_assets), 2) AS avg_return_on_assets
FROM sectors
INNER JOIN companies ON sectors.sector_id = companies.sector_id
INNER JOIN financials ON companies.company_id = financials.company_id
GROUP BY sectors.sector_name
ORDER BY avg_profit_margin DESC;

-- Self-join: compare 2023 vs 2024 revenue for each company
SELECT companies.ticker,
       companies.company_name,
       f2023.revenue AS revenue_2023,
       f2024.revenue AS revenue_2024,
       ROUND((f2024.revenue - f2023.revenue) * 100.0 / f2023.revenue, 2) AS revenue_change_pct
FROM financials f2023
INNER JOIN financials f2024
  ON f2023.company_id = f2024.company_id
INNER JOIN companies
  ON f2023.company_id = companies.company_id
WHERE f2023.fiscal_year = 2023
  AND f2024.fiscal_year = 2024
ORDER BY revenue_change_pct DESC;

You now have a complete analyst toolkit: filtering, sorting, aggregation, calculated metrics, multi-table joins, and year-over-year comparisons. Next up, the Secret Mission challenges you to go beyond the basics with window functions that rank and compare companies within their sectors.

Secret mission

Window Functions for Advanced Financial Analysis

You have already written queries that any junior analyst needs. Now push into intermediate territory with window functions. These let you rank, compare, and accumulate data across rows without collapsing your results into groups. Interviewers love asking about them because they separate candidates who memorize syntax from those who truly understand data.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • DB Browser for SQLite (installed application).
  • company_financials.db (your local database file).

Keep everything running

No action needed. Choose this if you are still actively building or plan to continue practicing SQL queries with your database.

  • Your company_financials.db file stays where you saved it.
  • DB Browser for SQLite remains installed and ready to use.
  • You can open the database anytime to run more queries or add new data.

Pause - I'll come back to this later

Since everything is local, there is nothing to pause. Your database file and DB Browser will be ready whenever you return.

  • Close DB Browser for SQLite if it is still open.
  • Your company_financials.db file remains on disk, taking up minimal space.

Delete - I don't want to use this again

Remove all project resources and the application from your machine.

  • Delete the database file:
  • Uninstall DB Browser for SQLite:

Nice Work!

Nice Work!

You just built a financial analysis database from scratch and wrote SQL queries that mirror the work junior analysts do on the job every day.

Here's what you accomplished:

  • Built a 3-table relational database with real company financial data sourced from SEC filings.
  • Wrote 10+ SQL queries covering SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOINs, and calculated financial metrics like profit margin, ROA, and ROE.
  • Created a sector comparison report combining JOINs, GROUP BY, and calculated columns. This is the type of output a hiring manager expects from a junior analyst.
  • Used window functions (RANK, LAG, running totals) and CTEs to perform advanced financial analysis that sets you apart in interviews.

Ready to quiz yourself?