Skip to content
Edition NigeriaGlobal
Country

You are in the Nigeria edition. Type your country to switch.

Set up your profile

Financial Data Lab · Lesson 4 of 8

Lab 4: SQL on a price table

This lab builds a small price table in SQLite and answers real questions with SQL: filtering by date and ticker, averages with GROUP BY, joining a company table, and computing daily returns with the LAG window function. Every query is checked against invented data you can verify by hand.

Advanced10 min read5 steps

This lesson is complete in writing. A video for it is on its way.

What you will learn

  • By the end you can create a price table in SQLite with a primary key on ticker and date, and load sample rows into it.
  • You can filter, sort and summarise prices with SELECT, WHERE, ORDER BY and GROUP BY.
  • You can join a price table to a company table on a shared key.
  • You can compute daily returns and a moving average with window functions, partitioned by ticker.

Key terms in this lesson

SQL
Structured Query Language: the standard language for asking a relational database to select, filter, combine and summarise rows of data.
Window function
An SQL function that calculates across a set of related rows while keeping each row, such as LAG to read the previous day's price for the same ticker.

Step 1 of 5

Build a small price database

Opening this step…

Step 2 of 5

Selecting, sorting and summarising

This step opens when you finish the one above.

Step 3 of 5

Joining prices to companies

This step opens when you finish the one above.

Step 4 of 5

Returns and moving averages with window functions

This step opens when you finish the one above.

Step 5 of 5

SQL and Python together

This step opens when you finish the one above.

Key takeaways

The key takeaways open when you finish every step.

Lesson quiz

6 questions. You need 85% to pass.

The quiz opens when you finish every step.

Read first

Stockertim Learn is education. It explains how investing works and never recommends buying or selling anything.

Up next

Financial data & analyticsLab 5: cleaning corporate action…Read nowLab 5: cleaning corporate actions in codeFinancial data & analytics · Advanced · 15 minFinancial data & analyticsLab 6: a sentiment classifier on…Read nowLab 6: a sentiment classifier on sample headlinesFinancial data & analytics · Advanced · 15 minFinancial data & analyticsLab 7: watching a model overfitRead nowLab 7: watching a model overfitFinancial data & analytics · Advanced · 15 minFinancial data & analyticsLab 8: writing the model card an…Read nowLab 8: writing the model card and a reproducible notebookFinancial data & analytics · Advanced · 15 minHuman resourcesLawful bases and consent at workRead nowLawful bases and consent at workHuman resources · Advanced · 15 minBonds & fixed incomeFGN bonds, treasury bills and Eu…Read nowFGN bonds, treasury bills and Eurobonds comparedBonds & fixed income · Beginner · 15 minProjects, consulting & analysisProject risk and stakeholder man…Read nowProject risk and stakeholder managementProjects, consulting & analysis · Intermediate · 15 minValuation & company analysisThe auditor's report and governa…Read nowThe auditor's report and governanceValuation & company analysis · Advanced · 15 minAI & securityModel risk management and explai…Read nowModel risk management and explainabilityAI & security · Advanced · 15 minRisk, governance & ethicsWhistleblowing and speaking upRead nowWhistleblowing and speaking upRisk, governance & ethics · Beginner · 15 minTrading: risk, costs and processRisk per trade and position sizeRead nowRisk per trade and position sizeTrading: risk, costs and process · Intermediate · 15 minRisk, governance & ethicsProfessional codes and the funda…Read nowProfessional codes and the fundamental principlesRisk, governance & ethics · Beginner · 15 min
English