SQL for Data Analysis: Queries You Will Use Every Week

McTaba runs coding and data bootcamps in Kenya. This guide is written to be useful whether or not you ever join one.

Picture a Safaricom statement PDF, turned into rows. Every payment in, every payment out, one line each. That is a transactions table, and it is the table most Kenyan analysts meet first. Our walk-through of the data science career in Kenya explains where SQL fits in the job.

All data here is made up. Imagine two tables in PostgreSQL:

CREATE TABLE customers (
  customer_id  integer PRIMARY KEY,
  name         text,
  town         text
);

CREATE TABLE transactions (
  txn_id       integer PRIMARY KEY,
  customer_id  integer REFERENCES customers,
  txn_time     timestamp,
  kind         text,       -- 'receive', 'send', 'paybill', 'airtime'
  amount       numeric     -- shillings, always positive
);

Five sample rows of transactions, also made up:

txn_id	customer_id	txn_time	kind	amount
1	10	2026-10-01 08:12	receive	5,000
2	10	2026-10-01 12:40	paybill	1,200
3	11	2026-10-02 09:05	receive	800
4	10	2026-10-03 18:22	send	1,500
5	11	2026-10-03 19:01	airtime	100

Learning SQL on a table you can picture is faster than learning it on abstract ones. If you want a class around it, the Data Science to AI Engineering programme teaches SQL and PostgreSQL in its data handling phase.

Every analysis starts by narrowing. How much money came in during October?

SELECT SUM(amount) AS received_october
FROM transactions
WHERE kind = 'receive'
  AND txn_time >= '2026-10-01'
  AND txn_time <  '2026-11-01';

Use "greater than or equal" for the start and "less than" for the next month's first day. It avoids losing the last day or double counting midnight.

Now the pattern you will type most: GROUP BY. Total sent and received per kind of transaction.

SELECT kind,
       COUNT(*)    AS n,
       SUM(amount) AS total,
       AVG(amount) AS average
FROM transactions
GROUP BY kind
ORDER BY total DESC;

Add HAVING when you want to filter on the total itself, such as only kinds with more than 100 rows. It is WHERE for groups.

You can do the same grouping in Python. Our first 30 days of Python shows the loop version.

Customer ids are useless in a report. Managers want names and towns. A JOIN pulls them from the other table.

SELECT c.town,
       COUNT(*)      AS payments,
       SUM(t.amount) AS total
FROM transactions t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.kind = 'paybill'
GROUP BY c.town
ORDER BY total DESC;

Two habits save hours. Alias tables with short letters. And know the difference between JOIN and LEFT JOIN. A plain JOIN drops customers with no rows. LEFT JOIN keeps them, with empty cells. To list customers who never transacted:

SELECT c.name
FROM customers c
LEFT JOIN transactions t ON t.customer_id = c.customer_id
WHERE t.txn_id IS NULL;

Always count rows before and after a JOIN. If the count grows, one side has duplicate keys, and your totals are wrong.

A statement shows a balance after every line. You can rebuild it. Treat receives as plus and everything else as minus, then add up as you go.

SELECT txn_id,
       txn_time,
       kind,
       amount,
       SUM(CASE WHEN kind = 'receive' THEN amount ELSE -amount END)
         OVER (PARTITION BY customer_id ORDER BY txn_time) AS running_balance
FROM transactions
WHERE customer_id = 10
ORDER BY txn_time;

For customer 10, the made-up rows give 5,000, then 3,800 after the paybill, then 2,300 after the send. PARTITION BY restarts the sum for each customer. ORDER BY decides the order of adding.

Two more window tools worth knowing:

• LAG(amount) OVER (...) reads the previous row, so you can find the gap between two payments.
• ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY txn_time DESC) numbers each customer's rows from newest, so filtering to 1 gives the latest one.

Window functions are where many learners stall. If it feels strange, that is normal. Read it as "for each row, look at a group of rows and calculate something, without collapsing them".

Managers ask "is it going up?" You answer with one row per day, week or month. In PostgreSQL, date_trunc does the bucketing.

SELECT date_trunc('week', txn_time)::date AS week_start,
       COUNT(*)    AS payments,
       SUM(amount) AS total
FROM transactions
WHERE kind = 'paybill'
GROUP BY week_start
ORDER BY week_start;

Two traps. A week with no payments simply does not appear, so a chart may skip it. And time zones: a timestamp stored in UTC puts a 1 AM Nairobi payment on the previous day. Check how your database stores time before you trust a daily count.

Once you can write these five patterns, the next skill is deciding which numbers deserve trust. That is the subject of the statistics you actually need for data science. Turning the output into a decision is covered in data storytelling.

1. Install PostgreSQL, or use a free browser tool such as SQLBolt for the basics.
2. Create the two tables above and add about fifty made-up rows.
3. Write each of the five queries from memory, then compare.
4. Invent three questions a shop or chama treasurer would ask, and answer each in SQL.

No school can promise you a job, and SQL alone does not make a data career. It is the skill nearly every analyst role asks you to show. The Data Science to AI Engineering programme starts on 11 January 2027 and costs KES 140,000; the SQL you practise here is the base it builds on.