DEV Community: David Mwandairo

Building the SafariConnect Dashboard

David Mwandairo — Thu, 24 Sep 2026 14:22:14 +0000

Wednesday's job was simple to state and hard to do: turn a PostgreSQL schema full of correct answers into something a CEO could read in ten seconds. By Monday we had clean bookings. By Tuesday we had answers to six business questions, buried in SQL output. By Thursday morning, someone with no interest in GROUP BY clauses needed to walk out of a meeting knowing which routes made money and which drivers to promote.

That gap between a correct query and a convincing dashboard is where this part of the project actually lived.

Getting the Data Ready for Power BI

Power BI doesn't want to see your cleaning process. It wants a table it can trust, so the first job was making sure v_clean_trips and clean_bookings deserved that trust. The reason we use both sources for the presentation is that v_clean_trips only contains the completed trips and clean_bookings contains both the completed and cancelled trips, which is a major presentation point. We'd already fixed the 23 data problems in the raw CSV such as the shouting names, the phone numbers with dashes and stray +254 prefixes, the seat classes spelled four different ways and the fares stored as text. Wednesday's task was different. It was about shape, not spelling.

2 quick reminders before getting into the dashboard building process:

To go through the step-by-step analysis that led to this Power BI report, go through this article.
For a refresher on how to connect Power BI to a local database, check out this article

A few things mattered more than I expected going in:

Grain. Power BI aggregates whatever you drag onto a visual, and it will do it happily even when the grain is wrong. Our view sits at one row per booking, so SUM(seats_booked) gives real passenger counts and COUNT(booking_id) gives real booking counts. Mixing those up produces numbers that look plausible and are wrong, which is worse than numbers that look obviously broken.
Date and time columns split out early. We pulled day_name and hour-of-day fields out of departure_date and departure_time in SQL rather than in Power Query, because a CTE with EXTRACT() is easier to check with a SELECT than a DAX measure buried in a report. Anything I can verify with a query, I verify with a query.
Indexes, because refresh time is part of the user experience. Seven indexes went onto the clean table before we connected Power BI, on the columns we knew we'd filter and join on constantly: route_code, driver_name, booking_status, departure_date. A dashboard that takes fifteen seconds to slice by route stops feeling like a dashboard and starts feeling like a problem.
One view, not five imports. Everything Power BI touches comes from v_clean_trips, clean_bookings or a small set of purpose-built views. That's a deliberate choice. When a number on a slide looks wrong, there's exactly one place to go check it, not five copies of the logic drifting apart across different report pages.

The Dashboard, Page by Page

We built five pages, ending in one summary view stitched together from the other four. Here's what each one carries.

Page 1 sets the scale of the business in six cards.

Total revenue of KSh 259.96K across 288 bookings, an average fare of KSh 903.10, an average driver rating of 4.27 against an average trip rating of only 3.53, and 35 cancelled bookings. Nothing fancy here on purpose. A board member glancing at a laptop from across a table should get the shape of the business before anyone says a word.

Page 2 is where the money and the people live.

A route table anchors the page on the left, followed by a revenue-by-route bar chart, a monthly revenue line, a passenger-city chart, a gender donut, and a driver rating bar chart with a trend line. RT001, Nairobi to Mombasa, sits clearly on top of the revenue bars, matching what the SQL had already told us on Tuesday. Good sign. If the dashboard had disagreed with the query, one of them would have been lying.

Page 3 covers what's costing us and when people actually travel.

A seat-class donut, a cancellation-rate card, a lost-revenue figure, a route-by-route lost revenue bar chart, and a month-by-day-of-week matrix of bookings. The cancellation rate reads as 0.12, which is 12%, and lost revenue lands at KSh 32.15K. That's real money walking out the door on bookings that never happened.

Page 4 is one focused chart.

Busiest hours by departure time, sorted from most booked to least. 6am leads. This page could have lived inside page 3, but a scheduling question this operational deserved its own screen rather than fighting for space with six other visuals.

The final page compresses all of it onto one screen for anyone who wants the whole story without clicking through tabs.

The DAX Behind Each Metric

Power BI doesn't inherit anything from the SQL side beyond the columns themselves, so every card and chart above needed its own measure. Below is the DAX that reproduces each one, written against clean_bookings as the table name, swap in whatever your model actually calls it.

KPI cards (Page 1 and the dashboard header)

Total Revenue = SUM(v_clean_trips[total_fare])

Total Bookings = DISTINCTCOUNT(v_clean_trips[booking_id])

Avg Fare = DIVIDE([Total Revenue], [Total Bookings])

Avg Driver Rating = AVERAGE(v_clean_trips[driver_rating])

Avg Trip Rating = AVERAGE(v_clean_trips[trip_rating])

Cancelled Bookings =
CALCULATE(
    DISTINCTCOUNT(clean_bookings[booking_id]),
    clean_bookings[booking_status] = "Cancelled"
)

Lost Revenue =
CALCULATE(
    SUM(clean_bookings[total_fare]),
    clean_bookings[booking_status] IN { "Cancelled", "No Show" }
)

Cancellation Rate % =
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(clean_bookings[booking_id]),
        clean_bookings[booking_status] IN { "Cancelled", "No Show" }
    ),
    [Total Bookings]
)

Revenue by Route and Revenue by Month (Page 2)

Revenue by Route =
CALCULATE(
    SUM(v_clean_trips[total_fare]),
    ALLEXCEPT(v_clean_trips, v_clean_trips[route_code])
)

Revenue by Month = SUM(v_clean_trips[total_fare])
-- placed on a matrix/line chart with departure_date grouped to Month,
-- or against a proper Date table:
Monthly Revenue = CALCULATE(SUM(v_clean_trips[total_fare]), DATESMTD('Date'[Date]))

Revenue by Route is really just Total Revenue dropped onto a bar chart with route_code on the axis; the ALLEXCEPT version is only needed if you want the route total to appear as a static comparison column alongside row-level detail.

Passenger Population and Gender split (Page 2)

Total Seats Booked = SUM(v_clean_trips[seats_booked])

Female Passengers =
CALCULATE([Total Seats Booked], v_clean_trips[passenger_gender] = "Female")

Male Passengers =
CALCULATE([Total Seats Booked], v_clean_trips[passenger_gender] = "Male")

Both donut and area chart use [Total Seats Booked], split by passenger_city for the area chart and by passenger_gender for the donut.

Driver Rating per Revenue (Page 2)

Driver Avg Rating = AVERAGE(v_clean_trips[driver_rating])

Driver Total Revenue = SUM(v_clean_trips[total_fare])

Both measures sit on the same combo chart with driver_name on the axis, one as columns, one as the line. The duplicate "Sum of driver_rating" legend entry flagged in the critique below is what happens when the second measure gets built as a copy of the first instead of a trip_rating average.

Seat Class Booked and Cancellation Rate (Page 3)

Bookings by Seat Class = COUNTROWS(clean_bookings)
-- on the donut, sliced by seat_class

Lost Revenue by Route =
CALCULATE(
    SUM(clean_bookings[total_fare]),
    clean_bookings[booking_status] IN { "Cancelled", "No Show" },
    ALLEXCEPT(clean_bookings, clean_bookings[route_code])
)

Busiest Days Booked by Seats and Busiest Days per Bookings (Page 3)

Seats Booked by Day =
SUM(v_clean_trips[seats_booked])
-- matrix: day_name on rows, Month(departure_date) on columns

Bookings by Day of Week =
COUNTROWS(v_clean_trips)
-- bar chart: WEEKDAY(v_clean_trips[departure_date]) on the axis

day_name itself is worth calculating once as a calculated column rather than re-deriving it in every visual:

day_name = FORMAT(v_clean_trips[departure_date], "dddd")
day_of_week = WEEKDAY(v_clean_trips[departure_date], 2)

Busiest Hours per Departure Time (Page 4)

Bookings by Departure Hour = COUNTROWS(v_clean_trips)
-- horizontal bar, departure_time on the axis, sorted by this measure descending

If departure_time came in as text (as it did through most of the SQL cleaning stage), Power Query needs to convert it to a proper time value before this sort works correctly, otherwise "06:00" and "16:00" sort as text rather than as clock time.

What the Numbers Actually Said

Strip away the chart types and a few things stood out clearly enough that we could say them out loud without hedging.

Nairobi-to-Mombasa (RT001) is the business's best route by revenue, and it's also the route bleeding the most money to cancellations, at roughly KSh 10K lost against a lost-revenue total of KSh 32.15K. That's not a contradiction. It's the same thing: your busiest, most valuable route is also your biggest exposure when a booking falls through.

Driver ratings sit meaningfully higher than trip ratings across the board, 4.27 against 3.53. Passengers aren't rating the drivers badly, but something about the overall trip experience, the vehicle, the timing, the route itself, isn't landing as well as the person behind the wheel. That gap is worth a follow-up question the dashboard alone can't answer.

Economy dominates seat class choice by a wide margin, which says more about price sensitivity in this market than anything else in the data. Monday is the single busiest day of the week, ahead of Wednesday and Tuesday, and Sunday is dramatically quiet. 6am is the most requested departure slot, which tells operations exactly where to put the extra vehicle if they only have one to spare.

Telling the Story to the Board

The brief was blunt about Thursday: ten minutes, live dashboard, every group member speaks, and the CEO chooses who answers each question. That last part changes how you prepare more than anything else. You can't assign yourself the easy slide and hope nobody asks about the hard one.

We walked the room through the dashboard in the same order it's built here: scale first, then routes and people, then costs and timing. Each number on screen had to trace back to a specific query from Tuesday, because "the dashboard says so" isn't an answer a CEO in that kind of room will accept. When the cancellation-rate card came up, the follow-up was immediate: which route, how much money, and what would fixing it require. Having the lost-revenue-by-route chart sitting right there, rather than a number we'd have to go calculate live, was the difference between a confident answer and an awkward pause.

What This Taught Me

A live filter is a live risk. Click the wrong slicer in front of an audience and your carefully rehearsed narrative shows a different number than the one you said thirty seconds ago. We tested every click path the night before, not just the happy one.
A chart is not an insight. "Revenue by route" is a chart. "RT001 makes the most money and loses the most to cancellations" is an insight, and it only exists because someone said it out loud while pointing at two different visuals on two different pages. The dashboard did the arithmetic. The presentation did the thinking.
Numbers need to match everywhere or they undermine themselves. The moment two pages showed a slightly different split for the same field, the room noticed before we did. A dashboard is a set of promises that every number agrees with every other number, and it only takes one mismatch to make people doubt all of them.

What I'd Build Differently Next Time

A few things would change if I rebuilt this dashboard today:

The seat-class donut on page 3 carries a (Blank) slice worth 10-13% of bookings, a leftover null that should have been resolved to a real value or an explicit "Unknown" label back in the SQL cleaning stage, not left to surface as an unlabelled wedge on a board slide. The same donut also disagrees slightly on the combined summary page versus its own dedicated page, a small inconsistency that's exactly the kind of thing point three above warned about.
The driver rating chart on page 2 shows two identical legend entries, both labelled "Sum of driver_rating." One of those measures was clearly meant to be something else, likely a trip-rating comparison, and it slipped through review. Labelling errors like that are easy to miss when you're staring at your own report and easy to spot when a CEO is staring at it fresh.
Several labels get cut off: the route table's route_from column, the passenger-city axis, a driver's name mid-word on the ratings chart. On a laptop screen that's a minor irritation. Projected on a boardroom wall, it reads as sloppiness that has nothing to do with the analysis underneath it.
Everything on this dashboard is the same shade of blue. That's a deliberate, calm choice, and it also means nothing visually flags the routes losing money versus the routes making it. A single accent color on the lost-revenue chart, red or amber against the house blue, would let a CEO's eye find the problem before anyone finished the sentence.
Given that the entire business is about routes between named cities, there's no map anywhere in the report. A route table with codes and city names asks the reader to do geography homework. A map does that work for them, and it's the one visual type this dataset was practically begging for.

None of these are failures of the analysis. The numbers underneath the dashboard held up under direct CEO questioning, which was the actual test. But a dashboard has two audiences, the analyst who trusts the query behind it, and the executive who only ever sees the screen, and building for the second audience is a distinct skill from the SQL that got us there.

The SafariConnect Data Story

David Mwandairo — Wed, 23 Sep 2026 12:43:56 +0000

How 290 rows of tangled booking data became six clear answers for a CEO who doesn't have time for excuses.

The Message That Started It All

Every data project has an origin story. This one starts with a WhatsApp-length message from an Operations Director who has run out of patience with a shared Excel file.

Safari Connect is a Nairobi-based bus and matatu booking platform. Think of it as the Bolt of long-distance travel. Passengers pick a route, choose a seat class, pay by M-Pesa, cash, or card, and turn up (mostly). Since 2024 the company has been growing fast, and its booking history has been growing right along with it, quietly, in a spreadsheet nobody has cleaned since the day it was created.

The brief was blunt: "I need you to clean this data, load it into our database, analyse it, and present your findings to the board... Make it look professional. Present on Friday. The CEO will be in the room."

No pressure.

What follows is the full journey of that data. From a 290-row CSV export riddled with 23 categories of mess, through a PostgreSQL cleaning pipeline, into a set of views, and finally into six business answers with real Kenyan-shilling figures behind them. Every query below is genuine SQL written for this project, and every result table underneath it is the actual output that query produces against the cleaned data, shown the way it would appear in a database GUI like DBeaver, row for row.

Chapter 1: Landing the Raw Data

Before any cleaning can happen, the raw export has to land somewhere safe. The first move is to build a staging table, with every column typed as TEXT, so that a stray letter in a numeric column doesn't blow up the import.

-- Create safari_connect schema
CREATE SCHEMA IF NOT EXISTS safari_connect;

-- Set search_path to safari_connect schema
SET search_path TO safari_connect;

-- Set up staging table to match csv structure
-- Drop existing table if any
DROP TABLE IF EXISTS safari_connect.staging_bookings;

-- Create staging table with all columns as text to avoid type mismatches
CREATE TABLE safari_connect.staging_bookings (
    booking_id TEXT,
    passenger_name TEXT,
    passenger_phone TEXT,
    passenger_gender TEXT,
    passenger_city TEXT,
    route_code TEXT,
    route_from TEXT,
    route_to TEXT,
    vehicle_plate TEXT,
    vehicle_type TEXT,
    driver_name TEXT,
    driver_rating TEXT,
    departure_date TEXT,
    departure_time TEXT,
    seat_class TEXT,
    seats_booked TEXT,
    fare_per_seat TEXT,
    total_fare TEXT,
    payment_method TEXT,
    booking_status TEXT,
    trip_rating TEXT
);
-- Sanity check for staging_bookings table
SELECT * FROM safari_connect.staging_bookings;

That "everything is text" decision looks lazy, but it's the opposite. It's the one rule that keeps a messy import from crashing on line one. 290 rows load without a single error. The mess gets dealt with afterwards, on the analyst's terms, not the file's.

With the raw data safely staged, a second table (clean_bookings), is created to receive the fixed version, with booking_id as a VARCHAR(10) PRIMARY KEY and every other column given a real type: NUMERIC for money and ratings, DATE for departure dates, INTEGER for seats and trip ratings. This table stays empty until the cleaning below earns its data.

--  Drop clean table if exists
DROP TABLE IF EXISTS safari_connect.clean_bookings;

-- Create clean table with appropriate data types
CREATE TABLE safari_connect.clean_bookings (
    booking_id VARCHAR(10) PRIMARY KEY,
    passenger_name VARCHAR(100),
    passenger_phone VARCHAR(10),
    passenger_gender VARCHAR(10),
    passenger_city VARCHAR(60),
    route_code VARCHAR(10),
    route_from VARCHAR(60),
    route_to VARCHAR(60),
    vehicle_plate VARCHAR(15),
    vehicle_type VARCHAR(20),
    driver_name VARCHAR(100),
    driver_rating NUMERIC(2,1),
    departure_date DATE,
    departure_time VARCHAR(10),
    seat_class VARCHAR(20),
    seats_booked INTEGER,
    fare_per_seat NUMERIC(10,2),
    total_fare NUMERIC(10,2),
    payment_method VARCHAR(20),
    booking_status VARCHAR(20),
    trip_rating INTEGER
);
-- Santy check for clean table
SELECT * FROM safari_connect.clean_bookings;

Chapter 2: Twenty-Three Ways a Spreadsheet Can Lie to You

Upon further analysis, I found 23 categories of dirty data hiding in the export. This section shows how SQL was used to hunt each one down.

Names: shouting, whispering, and stray spaces

Passenger names arrive in three unhelpful states i.e. ALICE MWANGI, brian otieno, and Carol Wanjiku with leading and trailing whitespace baked in.

SELECT booking_id, passenger_name
FROM safari_connect.staging_bookings
WHERE passenger_name != INITCAP(TRIM(passenger_name));

Output:

booking_id	passenger_name
BK0001	ALICE MWANGI
BK0002	brian otieno
BK0003	Carol Wanjiku
BK0023	CAROL WANJIKU
BK0024	david kamau

One UPDATE fixes all three problems at once, because INITCAP(TRIM(...)) trims whitespace and title-cases in a single pass:

UPDATE safari_connect.staging_bookings
SET passenger_name = INITCAP(TRIM(passenger_name))
WHERE passenger_name != INITCAP(TRIM(passenger_name));

booking_id	passenger_name
BK0001	Alice Mwangi
BK0002	Brian Otieno
BK0003	Carol Wanjiku
BK0023	Carol Wanjiku
BK0024	David Kamau

Note: BK0003 and BK0023 converge on the same clean value from two different kinds of mess. This proves that one well-chosen function beats three separate patches.

Phone numbers: dashes, country codes, and blanks

Phone numbers show up with dashes (0745-678-901), a +254 international prefix (+254756789012), and in 13 other cases, nothing at all.

SELECT booking_id, passenger_phone
FROM safari_connect.staging_bookings
WHERE passenger_phone LIKE '%-%';

Output:
| booking_id | passenger_phone |
|---|---|
| BK0004 | 0745-678-901 |
| BK0005 | +254756789012 |
| BK0006 | |
| BK0026 | 0767-890-123 |
| BK0027 | +254778901234 |
| BK0028 | |

Three targeted updates handle each variant:

-- Strip dashes and any non-digits
UPDATE safari_connect.staging_bookings
SET passenger_phone = REGEXP_REPLACE(passenger_phone,'[^0-9]', '', 'g')
WHERE passenger_phone LIKE '%-%';

-- Swap the +254 country code for a local leading zero
UPDATE safari_connect.staging_bookings
SET passenger_phone = '0' || SUBSTRING(REGEXP_REPLACE(passenger_phone, '[^0-9]', '', 'g'), 4)
WHERE passenger_phone LIKE '%+254%';

-- Empty strings become real NULLs, not invisible blanks
UPDATE safari_connect.staging_bookings
SET passenger_phone = NULL
WHERE TRIM(passenger_phone) = '';

booking_id	passenger_phone
BK0004	0745678901
BK0005	0756789012
BK0006	NULL
BK0026	0767890123
BK0027	0778901234
BK0028	NULL

That third update matters more than it looks. An empty string and a NULL behave identically to the human eye but very differently to a database. COUNT(), joins, and WHERE clauses all treat them apart. Making the blank explicit is what lets later analysis say "13 bookings have no phone on file" instead of silently miscounting.

Gender: seven ways to say two things

SELECT DISTINCT(passenger_gender) FROM safari_connect.staging_bookings;

passenger_gender	row_count
Male	143
Female	134
FEMALE	4
F	3
MALE	2
female	2
M	1
male	1

Seven labels, two real categories. A CASE statement collapses them:

UPDATE safari_connect.staging_bookings
SET passenger_gender = CASE
    WHEN UPPER(TRIM(passenger_gender)) IN ('MALE', 'M') THEN 'Male'
    WHEN UPPER(TRIM(passenger_gender)) IN ('FEMALE', 'F') THEN 'Female'
    ELSE passenger_gender
END;

Output:
| passenger_gender | row_count |
|---|---|
| Male | 147 |
| Female | 141 |

Dates: three formats fighting for the same column

This is the one that separates a careful analyst from a hopeful one. The same column holds 2024-01-08, 17/01/2024, 01-18-2024, and 19-01-24 — four different shapes, and none of them announce which is which.

booking_id	departure_date
BK0001	2024-01-08
BK0007	17/01/2024
BK0008	01-18-2024
BK0009	19-01-24

The fix has to be sequenced carefully. Slash-separated dates first, then short year-dashes, then anything left over gets checked digit-by-digit (a month value over 12 can only mean the format was MM-DD-YYYY, not DD-MM-YYYY):

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'DD/MM/YYYY')::TEXT
WHERE departure_date LIKE '%/%';

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'DD-MM-YYYY')::TEXT
WHERE departure_date LIKE '%-%' AND LENGTH(departure_date) = 8;

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'MM-DD-YYYY')::TEXT
WHERE departure_date LIKE '%-%'
    AND LENGTH(departure_date) = 10
    AND SPLIT_PART(departure_date, '-', 2)::INTEGER > 12;

booking_id	departure_date
BK0001	2024-01-08
BK0007	2024-01-17
BK0008	2024-01-18
BK0009	2024-01-19

Every one of those four rows now speaks the same language: YYYY-MM-DD, ready to be cast to a real DATE type.

Money hiding behind the word "KES"

SELECT booking_id, fare_per_seat, total_fare FROM safari_connect.staging_bookings;

booking_id	fare_per_seat	total_fare
BK0016	1200	KES 2400
BK0017	KES 675	675
BK0038	900	KES 1800
BK0039	KES 200	600
BK0060	280	KES 840

27 rows across fare_per_seat and total_fare carry a KES prefix that turns a number into a string PostgreSQL can't do arithmetic on. REGEXP_REPLACE strips anything that isn't a digit:

UPDATE safari_connect.staging_bookings
SET total_fare = REGEXP_REPLACE(total_fare, '[^0-9]', '', 'g')
WHERE total_fare SIMILAR TO '%[^0-9]%';

booking_id	fare_per_seat	total_fare
BK0016	1,200.00	2,400.00
BK0017	675.00	675.00
BK0038	900.00	1,800.00
BK0039	200.00	600.00
BK0060	280.00	840.00

Ratings that shouldn't exist, seats that go negative, and a booking that shows up twice

Three more categories of trouble, each fixed with a different weapon:

Impossible ratings. A 1–5 scale should never contain a 0 or a 6. 13 rows did.

SELECT DISTINCT(trip_rating) FROM safari_connect.staging_bookings;

booking_id	trip_rating
BK0021	6
BK0043	6
BK0065	0
BK0087	6
BK0109	0
BK0131	6

UPDATE safari_connect.staging_bookings
SET trip_rating = NULL
WHERE TRIM(trip_rating) NOT IN ('1', '2', '3', '4', '5', '');

A rating that can't be trusted is worse than no rating at all. This turns it into an honest NULL rather than a lie that would quietly drag down an average.

Negative seats. A booking for -1 seats makes no physical sense. You can't un-book a seat that was never booked in the first place, so the project rule is to delete rather than guess:

booking_id	passenger_name	seats_booked
BK9002	JANE SMITH	-1

DELETE FROM safari_connect.staging_bookings
WHERE NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9-]', '', 'g'), '')::INTEGER < 1;

An exact duplicate. BK0005 — Esther Njoroge, same date, same seats appears twice:

booking_id	passenger_name	departure_date	seats_booked
BK0005	Esther Njoroge	2024-01-15	2
BK0005	Esther Njoroge	2024-01-15	2

DELETE FROM safari_connect.staging_bookings
WHERE ctid NOT IN
    (SELECT MIN(ctid) FROM safari_connect.staging_bookings GROUP BY booking_id);

ctid is Postgres' own physical row address and it's what makes this command safe. booking_id is supposed to be unique, so you can't self-join on it to find "the other one" without ambiguity; ctid never lies about which physical row is which, so keeping the MIN(ctid) per booking_id keeps exactly one copy and drops the rest.

Loading the cleaned data

To load and verify the cleaned data into the clean_bookings table, we use the following script:

INSERT INTO safari_connect.clean_bookings
SELECT 
        booking_id, TRIM(passenger_name),
        NULLIF(TRIM(passenger_phone), ''),
        passenger_gender, COALESCE(NULLIF(TRIM(passenger_city),''), 'Unknown'),
        route_code, route_from, route_to, vehicle_plate,
        INITCAP(TRIM(vehicle_type)), TRIM(driver_name),
        NULLIF(REGEXP_REPLACE(driver_rating, '[^0-9.]', '', 'g'), '')::NUMERIC,
        departure_date::DATE, departure_time, seat_class,
        NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9]', '', 'g'), '')::INTEGER,
        NULLIF(REGEXP_REPLACE(fare_per_seat, '[^0-9]', '', 'g'), '')::NUMERIC,
        NULLIF(REGEXP_REPLACE(total_fare, '[^0-9]', '', 'g'), '')::NUMERIC,
        payment_method, booking_status,
        NULLIF(trip_rating, '')::INTEGER 
FROM safari_connect.staging_bookings 
WHERE departure_date SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}'
AND NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9]', '', 'g'), '')::INTEGER > 0;

-- Verify script
SELECT count(*) FROM safari_connect.clean_bookings;
SELECT DISTINCT booking_status FROM safari_connect.clean_bookings;

Once we verify the script, we get the following outputs respectively:

Between the negative-seat row and the duplicate, the table drops from 290 rows to 283. These were small, deliberate cuts, each backed by a SELECT that confirmed what was about to be deleted before it was deleted.

Casing was fixed the same INITCAP(TRIM()) way for passenger_city, driver_name, and vehicle_type; seat_class, payment_method, and booking_status each got their own CASE WHEN to collapse abbreviations like ECO/BUS/BUSINESS CLASS down to the two or three values the business actually recognises. The pattern repeats, but the logic behind each one is worth the same scrutiny as the flashier fixes above.

Chapter 3: The View That Everything Else Stands On

Once clean_bookings holds trustworthy data, a view(v_clean_trips) sits on top of it and does the last mile of preparation. It contains the data of the trips that were completed. Here is the script used to create v_clean_trips:

CREATE OR REPLACE VIEW v_clean_trips AS
SELECT *,
        TO_CHAR(departure_date, 'YYYY-MM') AS travel_month,
        TO_CHAR(departure_date, 'Month YYYY') AS month_label,
        TO_CHAR(departure_date, 'Day') AS day_name,
        EXTRACT(MONTH FROM departure_date) AS month_num,
        EXTRACT(DOW FROM departure_date) AS day_of_week,
        (fare_per_seat * seats_booked) AS calculated_fare,
        CASE 
            WHEN trip_rating BETWEEN 4 AND 5 THEN 'Satisfied'
            WHEN trip_rating = 3 THEN 'Neutral'
            WHEN trip_rating BETWEEN 1 AND 2 THEN 'Unsatisfied'
            ELSE 'No Rating'
        END AS satisfaction
FROM safari_connect.clean_bookings
WHERE booking_status = 'Completed';

-- Test v_clean_trips
SELECT count(*) FROM v_clean_trips;

After running the test script, the rows are further cleaned to give an output of 248 rows:

The v_clean_trips table is what will be used to create the Power BI presentation for this project. Every query(except Q5) in the next section reads from it.

Chapter 4: Six Questions, Six Answers

This is where the cleaning pays off. The Operations Director asked six things. Here is what the data actually said, query by query, output by output.

Q1. Which routes earn the most, which are most popular, and which are most efficient?

Three angles on the same ten routes, because "best" means different things to different people in the room.

Highest earner:

SELECT route_code, route_from, route_to, SUM(calculated_fare) AS total_fare_per_route
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_fare_per_route DESC;

Output:

route_code	route_from	route_to	total_fare_per_route
RT001	Nairobi	Mombasa	62,400.00
RT004	Nairobi	Eldoret	44,800.00
RT002	Nairobi	Kisumu	43,650.00
RT007	Nairobi	Nyeri	25,000.00
RT003	Nairobi	Nakuru	23,850.00
RT006	Mombasa	Malindi	17,150.00
RT010	Nairobi	Naivasha	15,960.00
RT009	Nairobi	Machakos	9,300.00
RT008	Kisumu	Kakamega	9,090.00
RT005	Nairobi	Thika	8,760.00

Nairobi–Mombasa (RT001) is the clear top earner, pulling in KES 62,400.00 — nearly 20,000 more than the second-place route.

Most popular by demand:

SELECT route_code, route_from, route_to,
       COUNT(booking_id) AS num_bookings,
       SUM(seats_booked) AS total_seats_sold
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_seats_sold DESC;

Output:
| route_code | route_from | route_to | num_bookings | total_seats_sold |
|---|---|---|---|---|
| RT005 | Nairobi | Thika | 35 | 71 |
| RT007 | Nairobi | Nyeri | 31 | 59 |
| RT010 | Nairobi | Naivasha | 27 | 55 |
| RT004 | Nairobi | Eldoret | 28 | 53 |
| RT001 | Nairobi | Mombasa | 30 | 50 |
| RT003 | Nairobi | Nakuru | 29 | 49 |
| RT008 | Kisumu | Kakamega | 29 | 47 |
| RT006 | Mombasa | Malindi | 27 | 47 |
| RT002 | Nairobi | Kisumu | 28 | 45 |
| RT009 | Nairobi | Machakos | 24 | 43 |

Here the story flips: Nairobi–Thika (RT005) wins on volume(the most bookings and the most seats sold), even though it earns the least revenue of all ten routes. It's a short, cheap, high-frequency commuter run, not a premium long-haul product, and the two metrics were never going to agree.

Most efficient per seat:

SELECT route_code, route_from, route_to,
       SUM(seats_booked) AS total_seats_sold,
       ROUND(AVG(trip_rating), 2) AS avg_rating,
       ROUND(SUM(calculated_fare) / NULLIF(SUM(seats_booked), 0), 2) AS revenue_per_seat
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY revenue_per_seat DESC;

Output:
| route_code | route_from | route_to | total_seats_sold | avg_rating | revenue_per_seat |
|---|---|---|---|---|---|
| RT001 | Nairobi | Mombasa | 50 | 3.48 | 1,248.00 |
| RT002 | Nairobi | Kisumu | 45 | 4.0 | 970.00 |
| RT004 | Nairobi | Eldoret | 53 | 3.5 | 845.28 |
| RT003 | Nairobi | Nakuru | 49 | 3.59 | 486.73 |
| RT007 | Nairobi | Nyeri | 59 | 3.23 | 423.73 |
| RT006 | Mombasa | Malindi | 47 | 3.62 | 364.89 |
| RT010 | Nairobi | Naivasha | 55 | 3.33 | 290.18 |
| RT009 | Nairobi | Machakos | 43 | 3.57 | 216.28 |
| RT008 | Kisumu | Kakamega | 47 | 3.52 | 193.40 |
| RT005 | Nairobi | Thika | 71 | 3.5 | 123.38 |

RT001 wins a third time. It brings in the highest revenue, most efficient per seat. Nairobi–Mombasa isn't just the biggest number on the board; it's the healthiest one, generating more shillings per seat sold than any other route.

A window-function version pulls all three lenses into one CTE and adds a revenue-share column:

WITH route_metrics AS (
    SELECT route_code, route_from || ' - ' || route_to AS route,
           COUNT(DISTINCT booking_id) AS total_bookings,
           SUM(seats_booked) AS total_seats_sold,
           SUM(total_fare) AS total_revenue,
           SUM(total_fare) / NULLIF(SUM(seats_booked), 0) AS revenue_per_seat
    FROM v_clean_trips
    WHERE booking_status = 'Completed'
    GROUP BY route_code, route_from, route_to
)
SELECT route, total_revenue, total_seats_sold, total_bookings,
       ROUND(revenue_per_seat, 2) AS revenue_per_seat,
       ROUND(total_revenue * 100.0 / SUM(total_revenue) OVER (), 2) AS revenue_share_pct
FROM route_metrics
ORDER BY total_revenue DESC;

Output:
| route | total_revenue | total_seats_sold | total_bookings | revenue_per_seat | revenue_share_pct |
|---|---|---|---|---|---|
| Nairobi - Mombasa | 62,400.00 | 50 | 30 | 1,248.00 | 24.0 |
| Nairobi - Eldoret | 44,800.00 | 53 | 28 | 845.28 | 17.23 |
| Nairobi - Kisumu | 43,650.00 | 45 | 28 | 970.00 | 16.79 |
| Nairobi - Nyeri | 25,000.00 | 59 | 31 | 423.73 | 9.62 |
| Nairobi - Nakuru | 23,850.00 | 49 | 29 | 486.73 | 9.17 |
| Mombasa - Malindi | 17,150.00 | 47 | 27 | 364.89 | 6.6 |
| Nairobi - Naivasha | 15,960.00 | 55 | 27 | 290.18 | 6.14 |
| Nairobi - Machakos | 9,300.00 | 43 | 24 | 216.28 | 3.58 |
| Kisumu - Kakamega | 9,090.00 | 47 | 29 | 193.40 | 3.5 |
| Nairobi - Thika | 8,760.00 | 71 | 35 | 123.38 | 3.37 |

That last column — revenue_share_pct, built with SUM(...) OVER (), is what turns "RT001 earns a lot" into a boardroom sentence: RT001 alone accounts for roughly a quarter of all route revenue. That's the kind of dependency a CEO needs to hear out loud.

Q2. Who are the best drivers, and does their rating actually affect how happy passengers are?

WITH driver_stats AS (
    SELECT driver_name,
           COUNT(DISTINCT booking_id) AS total_completed_bookings,
           SUM(total_fare) AS total_revenue,
           ROUND(AVG(driver_rating), 2) AS avg_driver_rating,
           ROUND(AVG(trip_rating), 2) AS avg_trip_rating,
           CORR(driver_rating, trip_rating) AS rating_correlation
    FROM v_clean_trips
    WHERE booking_status = 'Completed'
      AND trip_rating IS NOT NULL
      AND driver_rating IS NOT NULL
    GROUP BY driver_name
)
SELECT * FROM driver_stats ORDER BY total_revenue DESC;

Output:
| driver_name | total_completed_bookings | total_revenue | avg_driver_rating | avg_trip_rating |
|---|---|---|---|---|
| Isaac Korir | 33 | 33,045.00 | 3.8 | 3.39 |
| Kelvin Omondi | 32 | 30,855.00 | 4.5 | 3.32 |
| Brian Kamau | 26 | 29,340.00 | 4.2 | 3.73 |
| Tony Karanja | 34 | 28,250.00 | 3.9 | 3.74 |
| Samuel Gitonga | 33 | 28,235.00 | 4.6 | 3.59 |
| Hassan Abdi | 30 | 27,575.00 | 4.1 | 3.9 |
| Moses Kipchoge | 30 | 26,630.00 | 4.8 | 3.19 |
| Peter Ngugi | 35 | 23,880.00 | 4.3 | 3.41 |

Isaac Korir generates the most revenue of any driver, despite carrying a mid-table 3.8 platform rating. This is proof that a driver's star rating and their commercial value to the company are not the same conversation. The CORR() window function makes that gap official: the correlation between a driver's platform rating and the trip ratings passengers actually leave comes out at -0.101(essentially no relationship). A high driver rating does not reliably buy a happier passenger, which means promotion decisions built purely on stars would be pointing at the wrong signal.

Q3. How is revenue trending, and what were the best and worst months?

WITH monthly_revenue AS (
    SELECT date_trunc('month', departure_date) AS month,
           SUM(total_fare) AS revenue,
           COUNT(DISTINCT booking_id) AS bookings
    FROM v_clean_trips
    GROUP BY date_trunc('month', departure_date)
),
monthly_growth AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_monthly_revenue,
           ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) 
                  / LAG(revenue) OVER (ORDER BY month)) * 100, 2) AS growth_pct
    FROM monthly_revenue
)
SELECT to_char(month, 'YYYY-MM') AS month_label, revenue, growth_pct,
       RANK() OVER (ORDER BY revenue DESC) AS rank_month
FROM monthly_growth
ORDER BY month;

Output:
| month | revenue | bookings | growth_pct |
|---|---|---|---|
| 2024-01 | 18,475.00 | 20 | |
| 2024-02 | 19,945.00 | 22 | 7.96 |
| 2024-03 | 20,325.00 | 23 | 1.91 |
| 2024-04 | 18,390.00 | 23 | -9.52 |
| 2024-05 | 22,060.00 | 28 | 19.96 |
| 2024-06 | 23,425.00 | 25 | 6.19 |
| 2024-07 | 23,395.00 | 22 | -0.13 |
| 2024-08 | 16,740.00 | 18 | -28.45 |
| 2024-09 | 23,735.00 | 26 | 41.79 |
| 2024-10 | 25,230.00 | 28 | 6.3 |
| 2024-11 | 21,650.00 | 24 | -14.19 |
| 2024-12 | 22,205.00 | 25 | 2.56 |
| 2025-01 | 4,385.00 | 4 | -80.25 |

LAG() is doing the heavy lifting here. It reaches back one row (one month) without a self-join, which is exactly what month-over-month growth needs. The trend is choppy rather than a clean upward line. October is the standout month, September posts the sharpest single jump (+41.8%), and August is the year's quiet trough. The final row, January 2025, is a partial month in the export rather than a genuine collapse. This is worth flagging in the boardroom so nobody reads a half-month as a crisis.

Q4. Where do passengers come from, what do they book, and are they happy?

SELECT passenger_city, COUNT(DISTINCT booking_id) AS bookings, SUM(total_fare) AS revenue
FROM v_clean_trips
WHERE booking_status = 'Completed'
GROUP BY passenger_city
ORDER BY bookings DESC
LIMIT 10;

Output:
| passenger_city | bookings | revenue |
|---|---|---|
| Nairobi | 113 | 112,000.00 |
| Kisumu | 26 | 20,980.00 |
| Eldoret | 24 | 13,910.00 |
| Mombasa | 20 | 21,320.00 |
| Meru | 14 | 11,655.00 |
| Nakuru | 13 | 6,300.00 |
| Thika | 13 | 16,740.00 |
| Unknown | 11 | 9,425.00 |
| Nyeri | 10 | 8,220.00 |
| Machakos | 9 | 7,260.00 |

Nairobi dominates, contributing far more bookings and revenue than every other city combined, unsurprising for a Nairobi-headquartered platform, but worth quantifying rather than assuming.

Seat class tells its own story:

SELECT seat_class, COUNT(DISTINCT booking_id) AS bookings, SUM(total_fare) AS revenue,
       ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
WHERE booking_status = 'Completed'
GROUP BY seat_class;

Output:
| seat_class | bookings | revenue | avg_rating |
|---|---|---|---|
| Business | 52 | 38,210.00 | 3.65 |
| Economy | 201 | 189,600.00 | 3.5 |

Economy carries nearly four times the booking volume of Business, which is expected, but Business passengers rate their trips slightly higher on average, a small signal that the premium seat class is delivering on its premium.

Satisfaction, at a glance:

SELECT satisfaction, COUNT(booking_id) AS total_trips
FROM v_clean_trips
GROUP BY satisfaction
ORDER BY total_trips DESC;

Output:
| satisfaction | total_trips |
|---|---|
| Satisfied | 119 |
| Neutral | 74 |
| Unsatisfied | 50 |
| No Rating | 45 |

More passengers land in "Satisfied" than in any other bucket, but "Unsatisfied" and "No Rating" together are not a small slice of the pie. They're enough to be worth a follow-up on why so many trips go unrated at all.

And a cross-cut for completeness:

SELECT passenger_gender, seat_class, COUNT(booking_id) AS total_bookings
FROM v_clean_trips
GROUP BY passenger_gender, seat_class
ORDER BY passenger_gender, seat_class, total_bookings DESC;

Output:
| passenger_gender | seat_class | total_bookings |
|---|---|---|
| Female | Business | 29 |
| Female | Economy | 112 |
| Male | Business | 30 |
| Male | Economy | 117 |

There's no meaningful gender skew in seat class preference. Both groups book Economy at roughly the same rate. Sometimes the most useful finding is confirming that a variable doesn't matter, so nobody chases it later.

Q5. What's the cancellation rate per route, and what did it cost?

Note: we use the clean_bookings table for this query because v_clean_trips only contains completed trips

WITH route_totals AS (
    SELECT route_code,
           COUNT(*) AS total_bookings,
           COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS cancelled_bookings,
           SUM(total_fare) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_revenue
    FROM safari_connect.clean_bookings
    GROUP BY route_from, route_to, route_code
)
SELECT route_code, total_bookings, cancelled_bookings,
       ROUND(cancelled_bookings * 100.0 / total_bookings, 2) AS cancellation_rate_pct,
       lost_revenue
FROM route_totals
ORDER BY cancellation_rate_pct DESC;

Output:
| route_code | total_bookings | cancelled_bookings | lost_revenue | cancellation_rate_pct |
|---|---|---|---|---|
| RT006 | 27 | 5 | 2,800.00 | 18.52 |
| RT009 | 24 | 4 | 2,000.00 | 16.67 |
| RT005 | 35 | 5 | 1,140.00 | 14.29 |
| RT003 | 29 | 4 | 3,150.00 | 13.79 |
| RT008 | 29 | 4 | 1,620.00 | 13.79 |
| RT001 | 30 | 4 | 10,800.00 | 13.33 |
| RT002 | 28 | 3 | 5,400.00 | 10.71 |
| RT007 | 31 | 3 | 2,800.00 | 9.68 |
| RT010 | 27 | 2 | 840.00 | 7.41 |
| RT004 | 28 | 1 | 1,600.00 | 3.57 |

SELECT SUM(total_fare) AS total_lost_revenue
FROM safari_connect.clean_bookings
WHERE booking_status IN ('Cancelled', 'No Show');

total_lost_revenue: 32,150.00

RT006 (Mombasa–Malindi) has the worst cancellation rate at over 18%, which is nearly one in five bookings on that route. But the bigger shilling figure sits with RT001: even a modest cancellation rate on the highest-earning route still bleeds real money, because 13% of a big number is bigger than 18% of a small one. Across the whole business, cancellations and no-shows cost Safari Connect KES 32,150.00, money that a firmer deposit or reminder policy could claw back.

Q6. When are we busiest, and when should we add more vehicles?

SELECT EXTRACT(HOUR FROM departure_time::time) AS hour_of_day,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_passengers
FROM v_clean_trips
GROUP BY EXTRACT(HOUR FROM departure_time::time)
ORDER BY total_passengers DESC;

hour	total_bookings	total_passengers
10	27	51
6	29	50
9	27	48
8	25	46
19	25	46
12	24	44
7	24	43
16	21	42

10 a.m. is the single busiest departure hour, with 6 a.m. right behind it, exactly as you'd expect from a network anchored on Nairobi.

Breaking it down further by day and hour sharpens the picture:

SELECT day_name, EXTRACT(HOUR FROM departure_time::time) AS hour_of_day,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_passengers
FROM v_clean_trips
GROUP BY day_name, EXTRACT(HOUR FROM departure_time::time)
ORDER BY total_passengers DESC;

day_name	hour	total_bookings	total_passengers
Thursday	10	9	20
Monday	19	9	16
Monday	12	7	15
Wednesday	8	9	15
Tuesday	6	10	14
Wednesday	17	7	13

Thursday at 10 a.m. is the single busiest slot in the entire dataset. If Safari Connect is going to add a vehicle anywhere, it's that slot, and the Monday evening rush right behind it is where it pays for itself fastest.

Chapter 5: What the CEO Actually Needs to Hear

Strip away the SQL and six sentences remain:

RT001 (Nairobi–Mombasa) is the crown jewel. It brings in the highest revenue, highest revenue-per-seat, and roughly a quarter of all route income. Protect it.
RT005 (Nairobi–Thika) is the volume play. It holds the most bookings, most seats, lowest revenue. It's a different business model wearing the same uniform so price it like one.
Driver star ratings don't predict passenger satisfaction (correlation ≈ -0.101). Promote on revenue and completed trips, not on stars alone.
Revenue is trending up but noisy, with October as the peak and August as the trough. It's not a straight line, so month-to-month swings shouldn't trigger panic on their own.
Cancellations cost KES 32,150.00 across the year, concentrated on RT006 and, in absolute terms, RT001. A deposit or reminder policy targeted at these two routes is the highest-leverage fix available.
Thursday 10 a.m. is the network's busiest single slot. That's where the next vehicle belongs.

The Part That Doesn't Show Up in a Query

None of the numbers above exist without the tedious work that came first i.e. the TRIM(), the REGEXP_REPLACE, the careful SELECT before every DELETE. It's tempting to treat data cleaning as a chore standing between an analyst and the "real" analysis. This project is a good argument against that instinct: every single business answer above; the top route, the best driver or the busiest hour would have been wrong, or at least unreliable, on the raw 290-row export. 99% of the credibility in this presentation was earned in Chapter 2, not Chapter 4.

Matatus and buses move millions of Kenyans every day. Somewhere behind the app, the M-Pesa prompt, and the seat selection screen, there is a table like this one, quietly deciding whether the person running the company can trust their own numbers. In this instance, for Safari Connect, it finally can.

Handling NULL Values in SQL

David Mwandairo — Tue, 22 Sep 2026 15:53:20 +0000

Walk into any record store and you'll find a shelf of vinyl waiting to be catalogued. Some sleeves have no price sticker. Some have no year stamped on the label. One has clearly been played a hundred times but nobody wrote down its condition. The store still owns these records. The data about them is just missing.

That's what a NULL is in SQL: not zero, not an empty string, not "we checked and there's nothing there." It means "we don't know." And if you don't handle that gap on purpose, your queries will handle it for you, usually in ways you didn't ask for.

Here's the database behind our example. A small shop called The Vinyl Vault keeps its stock in one table:

CREATE TABLE records (
    record_id      INT PRIMARY KEY,
    title           VARCHAR(100),
    artist          VARCHAR(100),
    pressing_year   INT,
    condition_grade VARCHAR(20),
    list_price      DECIMAL(6,2),
    sale_price      DECIMAL(6,2),
    units_in_stock  INT
);

The owner enters stock with an INSERT statement like this one, leaving a cell out entirely whenever that detail isn't known yet:

INSERT INTO records
    (record_id, title, artist, pressing_year, condition_grade, list_price, sale_price, units_in_stock)
VALUES
    (1, 'Blue Train', 'John Coltrane', 1957, 'VG+', 45.00, NULL, 3),
    (2, 'Songs in the Key of Life', 'Stevie Wonder', NULL, 'NM', 60.00, 48.00, 1),
    (3, 'Unknown Pleasures', 'Joy Division', 1979, NULL, 30.00, NULL, 0),
    (4, 'Blue', 'Joni Mitchell', 1971, 'VG', NULL, 22.00, 2),
    (5, 'Kind of Blue', 'Miles Davis', 1959, 'NM', 55.00, NULL, 4);

Notice that NULL here is a keyword, not a quoted string. Write NULL on its own, never 'NULL'. Quoting it would insert the four-character text "NULL" into the column, which is a value like any other and breaks every one of the checks below. Once that statement runs, the table looks like this:

record_id	title	artist	pressing_year	condition_grade	list_price	sale_price	units_in_stock
1	Blue Train	John Coltrane	1957	VG+	45.00	NULL	3
2	Songs in the Key of Life	Stevie Wonder	NULL	NM	60.00	48.00	1
3	Unknown Pleasures	Joy Division	1979	NULL	30.00	NULL	0
4	Blue	Joni Mitchell	1971	VG	NULL	22.00	2
5	Kind of Blue	Miles Davis	1959	NM	55.00	NULL	4

Four columns here have gaps: a missing pressing year, a missing condition grade, a missing list price, a missing sale price. Each gap tells a different story. Maybe the owner hasn't graded the sleeve yet. Maybe a record isn't on sale. Maybe nobody has looked up its original pressing date. SQL doesn't know or care why the value is missing, and that's exactly the trap.

Why this matters

Try to find every record with no listed price using the instinct most people bring from everyday logic:

SELECT title FROM records WHERE list_price = NULL;

This returns nothing. Not an error, not a warning. NULL isn't a value you can compare with =, because comparing "unknown" to anything, even to another unknown, produces "unknown". SQL treats an unknown result the same as false when deciding whether to include a row. The query runs, the shop owner sees an empty result set, and walks away thinking every record has a price while it doesn't.

Aggregate functions carry the same trap. Count the shop's inventory:

SELECT COUNT(*) AS total_records, COUNT(sale_price) AS records_on_sale
FROM records;

Output:
| total_records | records_on_sale |
|---|---|
| 5 | 2 |

COUNT(*) counts rows. COUNT(sale_price) counts only the rows where that column has an actual value as NULLs get skipped. Both numbers are correct for what they measure, but if you meant to ask "how many records are on sale" and wrote COUNT(*) by habit, you'd report five instead of two. Averages have the same quirk: AVG(sale_price) divides by the number of non-NULL rows, not by every row in the table. Miss that, and a small sale section can look like it's pulling in more revenue per item than it really is.

None of this is a bug. It's SQL being consistent about what "I don't know" means. The job is to decide, column by column, what should happen when the value isn't there and that's where a handful of functions earn their keep.

The toolkit

COALESCE: the standard fallback

COALESCE takes a list of values and returns the first one that isn't NULL. It works the same way across MySQL, PostgreSQL, SQL Server, SQLite, and Oracle, which makes it the one worth learning first.

Say the shop wants a clean price list for a printed flyer. A record on sale should show its sale price; anything else should show its list price. Anything with neither should read "Ask staff":

SELECT
    title,
    COALESCE(sale_price, list_price, 0) AS display_price
FROM records;

Output:
| title | display_price |
|---|---|
| Blue Train | 45.00 |
| Songs in the Key of Life | 48.00 |
| Unknown Pleasures | 30.00 |
| Blue | 22.00 |
| Kind of Blue | 55.00 |

For Coltrane's Blue Train, sale_price is NULL, so COALESCE moves to the next argument and prints the list price instead. For Stevie Wonder's record, the sale price exists, so that's what shows. This is the same logic as checking a stack of fallback options in order and stopping at the first one that's actually filled in. There's nothing more mysterious than that.

ISNULL and IFNULL: the single-fallback shortcut

Where COALESCE accepts any number of arguments, ISNULL (SQL Server) and IFNULL (MySQL, SQLite) take exactly two: a value and what to use if that value is NULL. They read a little more plainly for the simple case:

-- SQL Server
SELECT title, ISNULL(pressing_year, 0) AS year_known
FROM records;

-- MySQL / SQLite
SELECT title, IFNULL(pressing_year, 0) AS year_known
FROM records;

Output:
| title | year_known |
|---|---|
| Blue Train | 1957 |
| Songs in the Key of Life | 0 |
| Unknown Pleasures | 1979 |
| Blue | 1971 |
| Kind of Blue | 1959 |

Stevie Wonder's pressing year is unknown, so the placeholder 0 shows up instead of a blank cell. That's fine for a quick script. It's a poor choice for a customer-facing report, since a shopper reading "0" might think the record was pressed in the year zero rather than "we don't have that on file." A text placeholder communicates the gap far better:

SELECT title, IFNULL(condition_grade, 'Not yet graded') AS grade
FROM records;

Output:
| title | grade |
|---|---|
| Blue Train | VG+ |
| Songs in the Key of Life | NM |
| Unknown Pleasures | Not yet graded |
| Blue | VG |
| Kind of Blue | NM |

Because ISNULL/IFNULL only run on two engines a piece and only take two arguments, COALESCE stays the safer default for anything that needs to run on more than one database or needs more than one fallback.

NULLIF: turning a value into NULL on purpose

NULLIF runs the opposite direction. It compares two values, and if they match, it returns NULL instead of the value. This sounds backwards until you hit the one problem it exists to solve: division by zero.

The shop wants to know how many units of each record are still worth restocking, based on the ratio of stock sold to stock on hand. A record with zero units in stock would divide by zero and crash the report:

SELECT
    title,
    units_in_stock,
    list_price / NULLIF(units_in_stock, 0) AS price_per_unit
FROM records;

Output:
| title | units_in_stock | price_per_unit |
|---|---|---|
| Blue Train | 3 | 15.00 |
| Songs in the Key of Life | 1 | 60.00 |
| Unknown Pleasures | 0 | NULL |
| Blue | 2 | NULL |
| Kind of Blue | 4 | 13.75 |

For Unknown Pleasures, units_in_stock is 0, so NULLIF swaps it for NULL before the division runs. Dividing by NULL returns NULL rather than throwing an error, so the query finishes and simply reports "not applicable" for that row. Joni Mitchell's Blue returns NULL for a different reason: its list_price is missing, so the division has nothing to work with regardless of stock count. Two different gaps, same NULL result — which is exactly why it helps to combine tools rather than lean on just one.

Putting it together

Real reports rarely need just one of these functions. Here's the shop's actual end-of-week summary query, which prices each record for the shelf tag and flags anything still missing key details:

SELECT
    title,
    artist,
    COALESCE(CAST(pressing_year AS VARCHAR), 'year unknown') AS pressing_year,
    COALESCE(condition_grade, 'ungraded') AS condition_grade,
    COALESCE(sale_price, list_price, 0) AS shelf_price,
    CASE
        WHEN pressing_year IS NULL OR condition_grade IS NULL OR list_price IS NULL
        THEN 'Needs cataloguing'
        ELSE 'Ready'
    END AS status
FROM records;

Output:
| title | artist | pressing_year | condition_grade | shelf_price | status |
|---|---|---|---|---|---|
| Blue Train | John Coltrane | 1957 | VG+ | 45.00 | Ready |
| Songs in the Key of Life | Stevie Wonder | year unknown | NM | 48.00 | Needs cataloguing |
| Unknown Pleasures | Joy Division | 1979 | ungraded | 30.00 | Needs cataloguing |
| Blue | Joni Mitchell | 1971 | VG | 22.00 | Needs cataloguing |
| Kind of Blue | Miles Davis | 1959 | NM | 55.00 | Ready |

One query, four columns cleaned up, and a status flag built from IS NULL checks that tells the shop owner exactly which records still need attention before they go on the shelf. Nothing here is exotic. It's the same handful of functions, applied with a clear idea of what each blank cell is supposed to mean once it reaches a human reader.

That's really the whole discipline: NULL isn't a data quality failure to panic over, and it isn't something to paper over with a random default either. It's a fact about the world — a record whose year nobody wrote down, a price nobody set yet — and your query should say so, on purpose, in whatever way fits the report you're building.

Window Functions: Seeing the Group Without Losing the Row

David Mwandairo — Tue, 22 Sep 2026 13:25:31 +0000

GROUP BY has one serious limitation: the moment it collapses rows into a summary, the detail is gone. You can find out that hive 101 produced 36.5 kg this year, but not which harvest was the biggest, or how much honey flow grew between visits, not without a second query. Window functions exist to answer exactly that kind of question: calculations that need to see an entire group, but still need to hand back an answer for every row in it.

To have a better understanding of the examples used in this article, have a look at these articles:

The four original harvests stay exactly as they were. Four new ones were added so each hive has enough history for a window function to actually do something interesting with it.

INSERT INTO harvests (harvest_id, hive_id, harvest_date, honey_kg, notes) VALUES
('H5', 102, '2024-08-01', 11.0, 'Late summer harvest'),
('H6', 103, '2024-07-20', 6.5,  'First harvest from this hive'),
('H7', 103, '2024-09-10', 8.0,  'Second harvest, steady growth'),
('H8', 101, '2024-11-01', 15.0, 'End of season harvest, strong flow');

harvests, in full

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0
H5	102	2024-08-01	11.0
H6	103	2024-07-20	6.5
H7	103	2024-09-10	8.0
H8	101	2024-11-01	15.0

Hive 999 is still the orphaned hive_id from the joins article, with no matching row in hives. It's staying in this dataset on purpose. A window function doesn't care whether a row has a valid foreign key anywhere else, it just processes whatever rows the query gives it.

The Anatomy of a Window Function

Every window function follows the same shape: a function, followed by OVER (...), followed optionally by PARTITION BY and ORDER BY inside those parentheses. Each piece answers a different question.

PARTITION BY decides which rows belong together the same way GROUP BY would. ORDER BY inside OVER() decides the sequence within that group, which matters enormously for anything about position, ranking, or what came before. Leaving both of them out leads to the function treating the entire result set as one window.

Here's the clearest way to see PARTITION BY and ORDER BY working together: number each hive's harvests in the order they happened.

SELECT hive_id, harvest_id, harvest_date, honey_kg,
    ROW_NUMBER() OVER (PARTITION BY hive_id ORDER BY harvest_date) AS harvest_seq
FROM harvests
ORDER BY hive_id, harvest_date;

Result:

hive_id	harvest_id	harvest_date	honey_kg	harvest_seq
101	H1	2024-05-01	12.5	1
101	H2	2024-09-01	9.0	2
101	H8	2024-11-01	15.0	3
102	H3	2024-06-15	15.0	1
102	H5	2024-08-01	11.0	2
103	H6	2024-07-20	6.5	1
103	H7	2024-09-10	8.0	2
999	H4	2024-07-01	5.0	1

Eight rows went in, eight rows came out, nothing collapsed. PARTITION BY hive_id reset the counter for every hive, and ORDER BY harvest_date decided which harvest counts as number one within each. Filter this result to harvest_seq = 1 and you have a clean list of each hive's very first harvest, useful for a "new hive is now productive" milestone report, without a single subquery.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER above never repeats a number, even when two rows are genuinely tied. RANK and DENSE_RANK handle ties differently, and the difference only shows up when a tie actually exists. Ranking the hives by their single best harvest produces one:

SELECT hive_id,
    MAX(honey_kg) AS best_harvest_kg,
    RANK()       OVER (ORDER BY MAX(honey_kg) DESC) AS honey_rank,
    DENSE_RANK() OVER (ORDER BY MAX(honey_kg) DESC) AS honey_dense_rank
FROM harvests
GROUP BY hive_id
ORDER BY best_harvest_kg DESC, hive_id;

Result:

hive_id	best_harvest_kg	honey_rank	honey_dense_rank
101	15.0	1	1
102	15.0	1	1
103	8.0	3	2
999	5.0	4	3

Hive 101's best single harvest, H8 at 15.0 kg, ties exactly with hive 102's H3. RANK gives both of them rank 1, then skips straight to rank 3 for the next hive, counting the two tied rows as if they'd occupied ranks 1 and 2. DENSE_RANK also gives both hives rank 1, but the next distinct value gets rank 2, no gap. Which one you want depends on the question: "top 3 hives by best harvest" reads differently depending on whether a tie for first should use up a slot.

NTILE takes a different approach to ranking: instead of ordering, it splits rows into a fixed number of equal-sized buckets. Say the co-op wants to flag its two weakest hives by total output for a closer inspection this winter:

SELECT hive_id, SUM(honey_kg) AS total_kg,
    NTILE(2) OVER (ORDER BY SUM(honey_kg) DESC) AS performance_tier
FROM harvests
GROUP BY hive_id
ORDER BY total_kg DESC;

Result:

hive_id	total_kg	performance_tier
101	36.5	1
102	26.0	1
103	14.5	2
999	5.0	2

NTILE(2) divides the four hives into two tiers of two. Hives 103 and 999 land in tier 2, the co-op's shortlist for a winter health check, generated without a single manually chosen cutoff value.

Offset Functions: LAG and LEAD

Ranking functions look at position. LAG and LEAD look sideways, at the row immediately before or after the current one, within the same partition and order. Hive 101 now has three harvests across the season, enough to ask how each one compares to its neighbors.

SELECT harvest_id, harvest_date, honey_kg,
    LAG(honey_kg)  OVER (PARTITION BY hive_id ORDER BY harvest_date) AS prev_kg,
    ROUND(honey_kg - LAG(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date), 1) AS change_kg,
    LEAD(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date) AS next_kg
FROM harvests
WHERE hive_id = 101
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	prev_kg	change_kg	next_kg
H1	2024-05-01	12.5	NULL	NULL	9.0
H2	2024-09-01	9.0	12.5	-3.5	15.0
H8	2024-11-01	15.0	9.0	6.0	NULL

LAG reaches backward, LEAD reaches forward, and both return NULL at the edges of the partition, there's nothing before H1 or after H8 for this hive. The change_kg column, honey this time minus honey last time, is the real payoff: a dip of 3.5 kg in September followed by a jump of 6.0 kg by November tells the beekeeper more than the raw totals ever would, and it took no self-join and no subquery to produce.

Aggregate Functions as Window Functions: Frames

Any aggregate function, SUM, AVG, COUNT, can run as a window function too, and this is where the frame clause matters, the part of OVER() that defines exactly which neighboring rows a calculation includes. Left unspecified with an ORDER BY present, the default frame runs from the start of the partition to the current row, which is what produces a running total. Specify the frame explicitly, and you can build something more targeted, like a two-harvest moving average across the whole co-op's season:

SELECT harvest_id, harvest_date, honey_kg,
    ROUND(AVG(honey_kg) OVER (
        ORDER BY harvest_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_2
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	moving_avg_2
H1	2024-05-01	12.5	12.5
H3	2024-06-15	15.0	13.75
H4	2024-07-01	5.0	10.0
H6	2024-07-20	6.5	5.75
H5	2024-08-01	11.0	8.75
H2	2024-09-01	9.0	10.0
H7	2024-09-10	8.0	8.5
H8	2024-11-01	15.0	11.5

No PARTITION BY this time, so every harvest across every hive is treated as one continuous timeline, a co-op-wide honey flow trend rather than a per-hive one. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW tells the database exactly which two rows to average at each step: the current one and the one right before it. The dip after H3's bumper 15.0 kg harvest and the climb back up toward H8 are both visible in the smoothed column in a way the raw honey_kg values alone don't show as clearly.

When to Reach for a Window Function

You need to...	Use
Number or sequence rows within a group, without collapsing it	`ROW_NUMBER()`
Rank rows, and gaps after ties are acceptable	`RANK()`
Rank rows, and ties shouldn't create gaps	`DENSE_RANK()`
Split rows into a fixed number of equal buckets	`NTILE(n)`
Compare a row to the one before or after it	`LAG()` / `LEAD()`
Get a running total, moving average, or other rolling calculation	Aggregate function `OVER (...)` with a frame

Try It Yourself

Take the moving-average query and change the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, a three-harvest average instead of two. Watch how much smoother the trend line gets, and how much more it lags behind sudden changes like H3's bumper crop. That trade-off, smoothness against responsiveness, is the real decision behind every moving average, in a spreadsheet or in SQL.

SQL Functions: The Toolbox Hiding Inside Every SELECT

David Mwandairo — Tue, 22 Sep 2026 12:36:46 +0000

Most of what a database does to your data happens between SELECT and FROM: turning a name into an email address, a date into a month, a pile of raw numbers into an average worth reporting. That work is done by functions, and most SQL beginners use a handful of them for months, COUNT, SUM, maybe UPPER, without ever noticing that "function" is a real, well-defined idea with its own categories and rules.

We'll stay with the beekeeping co-op from the earlier articles I wrote here and here. The notes column below is new; everything else should look familiar. Here's the script that added it:

ALTER TABLE harvests ADD COLUMN notes VARCHAR(200);

UPDATE harvests SET notes = '  Extracted in the afternoon, slight rain  ' WHERE harvest_id = 'H1';
UPDATE harvests SET notes = 'Morning harvest, strong yield'                WHERE harvest_id = 'H2';
UPDATE harvests SET notes = '  BUMPER CROP after spring bloom  '           WHERE harvest_id = 'H3';
UPDATE harvests SET notes = 'Logged by hand, hive number unclear'         WHERE harvest_id = 'H4';

The Data, With One Addition

beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL
5	Grace Achieng	2

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg	notes
H1	101	2024-05-01	12.5	`" Extracted in the afternoon, slight rain "`
H2	101	2024-09-01	9.0	`"Morning harvest, strong yield"`
H3	102	2024-06-15	15.0	`" BUMPER CROP after spring bloom "`
H4	999	2024-07-01	5.0	`"Logged by hand, hive number unclear"`

Whoever logs harvests by hand isn't consistent about spacing or capitalization. That's not an accident on my part, real free-text fields look exactly like this, and it gives the string functions below something honest to clean up.

What a Function Actually Is

A SQL function takes input, does something deterministic to it, and returns a value. That's the whole definition, but it hides an important split. Functions fall into three behaviors, and confusing them is where most beginner mistakes come from.

Scalar functions run once per row and return one value per row. UPPER('bee') doesn't care what else is in the table; feed it a string, it hands back a string, row by row.

Aggregate functions collapse many rows into one. SUM(honey_kg) doesn't return a value for each row, it consumes the whole group and returns a single number for it. This is why you can't mix a raw column with an aggregate function in the same SELECT without a GROUP BY, the database can't return one row of totals sitting next to twenty rows of detail at the same time.

Window functions are the middle ground: they look across a group of rows, like an aggregate does, but they return a value for every row instead of collapsing them. You'll see the difference directly in the examples below.

Scalar Functions: Cleaning and Shaping Text

The co-op needs a keeper@murchisonapiary.coop email address for everyone, generated from their name rather than typed in by hand.

SELECT name,
    LOWER(REPLACE(name, ' ', '.')) || '@murchisonapiary.coop' AS coop_email
FROM beekeepers;

Result:

name	coop_email
Amara Wanjiru	amara.wanjiru@murchisonapiary.coop
Brian Otieno	brian.otieno@murchisonapiary.coop
Chiara Mwangi	chiara.mwangi@murchisonapiary.coop
David Kimani	david.kimani@murchisonapiary.coop
Grace Achieng	grace.achieng@murchisonapiary.coop

Three scalar functions, nested inside each other, run on every row: REPLACE swaps the space for a period, LOWER normalizes the case, and || glues the domain on the end. (MySQL doesn't support || for concatenation by default; use CONCAT() there instead.)

Now clean up the messy notes field:

SELECT harvest_id, TRIM(notes) AS clean_note, LENGTH(TRIM(notes)) AS note_length
FROM harvests;

Result:

harvest_id	clean_note	note_length
H1	Extracted in the afternoon, slight rain	39
H2	Morning harvest, strong yield	29
H3	BUMPER CROP after spring bloom	30
H4	Logged by hand, hive number unclear	35

TRIM removes the leading and trailing spaces someone left when they typed H1 and H3 by hand; LENGTH then measures the cleaned-up result. Run LENGTH before TRIM and H1 would report 43, the padding spaces counted as real characters. The order you nest functions in changes the answer, not just the syntax.

Aggregate Functions: Turning Rows Into a Summary

SELECT
    COUNT(*) AS harvest_count,
    SUM(honey_kg) AS total_kg,
    ROUND(AVG(honey_kg), 2) AS avg_kg,
    MIN(honey_kg) AS smallest_kg,
    MAX(honey_kg) AS largest_kg
FROM harvests;

Result:

harvest_count	total_kg	avg_kg	smallest_kg	largest_kg
4	41.5	10.38	5.0	15.0

Four rows go in, one row comes out. That's the aggregate signature: COUNT, SUM, AVG, MIN, and MAX all take a column and reduce it, whatever number of rows they're given, to a single number. Notice ROUND wrapping AVG here too, a scalar function applied to the result of an aggregate one. Functions compose like this constantly; there's no rule against feeding one function's output into another.

Aggregates get more useful paired with GROUP BY, which runs the same collapse once per group instead of once for the whole table:

SELECT hive_id,
    SUM(honey_kg) AS total_kg,
    FLOOR(SUM(honey_kg) * 1000 / 350) AS jars_of_350g
FROM harvests
GROUP BY hive_id
ORDER BY hive_id;

Result:

hive_id	total_kg	jars_of_350g
101	21.5	61
102	15.0	42
999	5.0	14

SUM collapses each hive's harvests into a total, and FLOOR figures out how many complete 350-gram jars that total fills, rounding down because a co-op can't sell three-quarters of a jar. Hive 999 shows up here because this query only looks at harvests, a reminder from the joins article that this hive_id has no matching row in hives at all.

Date Functions: Reading Time Out of a Timestamp

SELECT harvest_id, harvest_date, EXTRACT(MONTH FROM harvest_date) AS harvest_month
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	harvest_month
H1	2024-05-01	5
H3	2024-06-15	6
H4	2024-07-01	7
H2	2024-09-01	9

EXTRACT pulls a single component out of a date, useful the moment you want to group harvests by month rather than by exact day, spotting a June bloom pattern buried inside four rows is impossible by eye, but trivial once EXTRACT(MONTH FROM ...) feeds into a GROUP BY.

Dates also support direct arithmetic. Assume today is 2024-10-01, and the co-op wants to know how stale each harvest log is:

SELECT harvest_id, harvest_date, ('2024-10-01' - harvest_date) AS days_since_harvest
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	days_since_harvest
H1	2024-05-01	153
H3	2024-06-15	108
H4	2024-07-01	92
H2	2024-09-01	30

Subtracting two dates in PostgreSQL returns a plain integer number of days. MySQL wants DATEDIFF('2024-10-01', harvest_date), and SQL Server wants DATEDIFF(day, harvest_date, '2024-10-01'), same idea, different name and argument order per engine.

Conditional Functions: Branching Without Leaving SQL

SELECT harvest_id, honey_kg,
    CASE
        WHEN honey_kg < 8  THEN 'Small'
        WHEN honey_kg < 13 THEN 'Medium'
        ELSE 'Large'
    END AS harvest_size
FROM harvests
ORDER BY honey_kg;

Result:

harvest_id	honey_kg	harvest_size
H4	5.0	Small
H2	9.0	Medium
H1	12.5	Medium
H3	15.0	Large

CASE checks its conditions top to bottom and stops at the first match, which is why the boundaries (< 8, < 13) don't need to repeat the lower bound of each bucket.

COALESCE solves a narrower, more common problem: what to display in place of a NULL.

SELECT b.name, COALESCE(m.name, 'Founding member') AS mentor_name
FROM beekeepers b
LEFT JOIN beekeepers m ON b.mentor_id = m.keeper_id;

Result:

name	mentor_name
Amara Wanjiru	Founding member
Brian Otieno	Amara Wanjiru
Chiara Mwangi	Amara Wanjiru
David Kimani	Founding member
Grace Achieng	Brian Otieno

The self join finds each keeper's mentor by name, and returns NULL for the two keepers who don't have one. COALESCE takes that NULL and replaces it with the first non-null value in its argument list, here just one fallback string, though COALESCE accepts as many candidates as you give it and returns the first one that isn't NULL.

Window Functions: Per-Row Answers That Still See the Group

SELECT hive_id,
    SUM(honey_kg) AS total_kg,
    RANK() OVER (ORDER BY SUM(honey_kg) DESC) AS honey_rank
FROM harvests
GROUP BY hive_id;

Result:

hive_id	total_kg	honey_rank
101	21.5	1
102	15.0	2
999	5.0	3

This looks like the GROUP BY query from earlier, and it starts the same way, but RANK() OVER (...) doesn't collapse anything further. It ranks each already-grouped row against the others and keeps every row visible. Try to get a rank number next to a raw total using only GROUP BY and an aggregate, and you can't; the aggregate would need to already know every group's total before ranking any of them, which is exactly the problem window functions exist to solve.

The same idea works without collapsing rows at all, tracking a running total across a hive's season:

SELECT harvest_id, harvest_date, honey_kg,
    SUM(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date) AS running_total_kg
FROM harvests
WHERE hive_id = 101
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	running_total_kg
H1	2024-05-01	12.5	12.5
H2	2024-09-01	9.0	21.5

Both rows from hive 101 survive, unlike a plain SUM(honey_kg) GROUP BY hive_id, which would flatten them into a single 21.5 and lose the harvest-by-harvest detail. PARTITION BY restarts the running total for each hive, and ORDER BY inside the OVER() clause decides the order the total accumulates in.

When to Reach for Each Kind

You need to...	Use
Clean, reformat, or combine text on every row	Scalar string functions
Do arithmetic or rounding on every row	Scalar numeric functions
Collapse many rows into one summary number	Aggregate functions
Pull a component out of a date, or measure a time gap	Date functions
Branch on a condition, or fill in a value for `NULL`	`CASE` / `COALESCE`
Rank, running-total, or compare rows without losing any rows	Window functions

Try It Yourself

Take the notes column and write a query that extracts just the first word of each note, using whatever substring function your database offers, SPLIT_PART, SUBSTRING_INDEX, or LEFT combined with a search for the first space. It's a small problem, but it forces you to nest two or three scalar functions correctly, which is the skill this whole article has really been about.

Subqueries and CTEs: Asking a Question Inside a Question

David Mwandairo — Mon, 21 Sep 2026 19:00:13 +0000

Some questions can't be answered in one pass. "Which hive produced the most honey?" needs the maximum honey figure before it can find the hive that matches it. "Which keepers are above average?" needs the average before it can compare anyone to it. SQL handles this the same way you'd handle it on paper: work out the smaller number first, then use it.

That's what a subquery is. A CTE does the same job with a different shape. We'll reuse the beekeeping co-op from the joins article, so the data below should look familiar, with one addition that makes the later examples worth running.

The Data, Recapped

See the tables used for this article here.
beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL
5	Grace Achieng	2

Grace Achieng is new. She's Brian's apprentice, which gives the co-op a three-generation mentorship line: Amara trained Brian, and Brian is now training Grace. We'll need that depth later.

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0

What a Subquery Is

A subquery is a query nested inside another one, wrapped in parentheses. The database runs it, gets a result, and hands that result to the outer query as if it had been typed there directly. Depending on where it sits, a subquery can return a single value, a list of values, or an entire result set standing in for a table.

A scalar subquery: finding the single best harvest

SELECT hive_id, harvest_date, honey_kg
FROM harvests
WHERE honey_kg = (SELECT MAX(honey_kg) FROM harvests);

Result:

hive_id	harvest_date	honey_kg
102	2024-06-15	15.0

The inner query, SELECT MAX(honey_kg) FROM harvests, runs first and reduces to one number: 15.0. The outer query never sees the subquery at all once it's been resolved. A subquery that returns exactly one value is called a scalar subquery, and it can sit almost anywhere a literal number could.

A subquery with IN: hives that have never been harvested

SELECT hive_id, location
FROM hives
WHERE hive_id NOT IN (SELECT hive_id FROM harvests);

Result:

hive_id	location
103	Kiambu Backyard
104	Abandoned Lot

Here the inner query returns a list, (101, 101, 102, 999), and NOT IN checks each hive against that list. Hives 103 and 104 never appear in harvests, so they survive the filter. One thing worth flagging is that NOT IN turns dangerous the moment the subquery's list can contain a NULL, since NOT IN against a list containing NULL returns no rows at all, silently. harvests.hive_id is never NULL here, so it's safe, but NOT EXISTS is the safer default habit for this exact pattern once nullable columns get involved.

A correlated subquery: each hive's most recent harvest

SELECT h1.hive_id, h1.harvest_date, h1.honey_kg
FROM harvests h1
WHERE h1.harvest_date = (
    SELECT MAX(h2.harvest_date)
    FROM harvests h2
    WHERE h2.hive_id = h1.hive_id
);

Result:

hive_id	harvest_date	honey_kg
101	2024-09-01	9.0
102	2024-06-15	15.0
999	2024-07-01	5.0

This one is different in kind, not just in syntax. The inner query references h1.hive_id, a column from the outer query, so it can't run once and be done. The database runs it fresh for every row in harvests, filtered to that row's hive. Hive 101 has two harvest dates, and this picks the later one. This "latest record per group" shape shows up constantly: latest login per user, latest price per product, latest reading per sensor.

A correlated subquery in SELECT: hive count per keeper

SELECT b.name,
    (SELECT COUNT(*) FROM hives h WHERE h.keeper_id = b.keeper_id) AS hive_count
FROM beekeepers b;

Result:

name	hive_count
Amara Wanjiru	1
Brian Otieno	1
Chiara Mwangi	1
David Kimani	0
Grace Achieng	0

Same correlation, different location. The subquery sits in the column list this time, running once per row of beekeepers and returning a single number for each. David and Grace get 0, not NULL, because COUNT(*) on an empty match still counts to zero rather than finding nothing.

A subquery in FROM: a derived table

SELECT h.location, hive_totals.total_honey_kg
FROM hives h
JOIN (
    SELECT hive_id, SUM(honey_kg) AS total_honey_kg
    FROM harvests
    GROUP BY hive_id
) AS hive_totals ON h.hive_id = hive_totals.hive_id
WHERE hive_totals.total_honey_kg > 10;

Result:

location	total_honey_kg
Ruiru Rooftop	21.5
Thika Road Garden	15.0

The subquery in the FROM clause builds a small temporary table, hive_totals, that the outer query joins against like any other table. Note that hive 999's total of 5.0 kg quietly vanishes here: it has no matching row in hives, so the JOIN drops it before the WHERE clause even runs. A derived table behaves exactly like a real one for the rest of the query, it just doesn't exist anywhere except for the duration of this statement.

What a CTE Is

A CTE, common table expression, does the same job as a subquery i.e. it names a temporary result set for one query to use. The difference is where you write it. Instead of nesting it inside the query, you declare it up front with WITH, give it a name, and then write the rest of the query as if that name were a real table.

Rewriting the derived-table example above as a CTE:

WITH hive_totals AS (
    SELECT hive_id, SUM(honey_kg) AS total_honey_kg
    FROM harvests
    GROUP BY hive_id
)
SELECT h.location, hive_totals.total_honey_kg
FROM hives h
JOIN hive_totals ON h.hive_id = hive_totals.hive_id
WHERE hive_totals.total_honey_kg > 10;

Result:

location	total_honey_kg
Ruiru Rooftop	21.5
Thika Road Garden	15.0

Identical output, identical execution in most modern databases. The value here isn't a new capability, it's that you now read the query top to bottom: first, here's what hive_totals means; then, here's what to do with it. Once a query needs three or four steps, that ordering stops being a nicety and starts being the difference between a query you can debug and one you can't.

Where CTEs pull ahead: using the same result twice

Go back to an earlier goal: find keepers whose total honey is above the co-op average. Written with nested subqueries alone, the "total honey per keeper" calculation has to appear twice, once to list it, once to average it:

SELECT name, total_honey_kg
FROM (
    SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
    FROM beekeepers b
    LEFT JOIN hives h ON b.keeper_id = h.keeper_id
    LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
    GROUP BY b.name
) AS keeper_totals
WHERE total_honey_kg > (
    SELECT AVG(total_honey_kg)
    FROM (
        SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
        FROM beekeepers b
        LEFT JOIN hives h ON b.keeper_id = h.keeper_id
        LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
        GROUP BY b.name
    ) AS t
);

It works, but the five-line calculation is now duplicated word for word. Change one join later and you have to remember to change it twice. A CTE removes the duplication by naming the calculation once and referencing it as often as needed:

WITH keeper_totals AS (
    SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
    FROM beekeepers b
    LEFT JOIN hives h ON b.keeper_id = h.keeper_id
    LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
    GROUP BY b.name
)
SELECT name, total_honey_kg
FROM keeper_totals
WHERE total_honey_kg > (SELECT AVG(total_honey_kg) FROM keeper_totals);

Result:

name	total_honey_kg
Amara Wanjiru	21.5
Brian Otieno	15.0

The co-op's average sits at 7.3 kg across all five keepers, including the three who've harvested nothing yet. Amara and Brian clear it; everyone else doesn't. Same logic as the nested version, defined once, and one CTE now stands in for what would otherwise be two identical subqueries.

Where a subquery can't follow: recursive CTEs

WITH RECURSIVE lets a CTE reference itself, which makes it the only one of the two that can walk a chain of unknown length. The mentorship data is exactly that shape: Amara trained Brian, Brian is training Grace, and nothing in the schema tells you in advance how many links that chain will have.

WITH RECURSIVE mentorship_chain AS (
    -- Anchor: keepers with no mentor, the top of each line
    SELECT keeper_id, name, mentor_id, name AS lineage, 0 AS depth
    FROM beekeepers
    WHERE mentor_id IS NULL

    UNION ALL

    -- Recursive step: find apprentices of keepers already in the chain
    SELECT b.keeper_id, b.name, b.mentor_id,
           mc.lineage || ' -> ' || b.name, mc.depth + 1
    FROM beekeepers b
    JOIN mentorship_chain mc ON b.mentor_id = mc.keeper_id
)
SELECT name, depth, lineage
FROM mentorship_chain
ORDER BY depth, name;

Result:

name	depth	lineage
Amara Wanjiru	0	Amara Wanjiru
David Kimani	0	David Kimani
Brian Otieno	1	Amara Wanjiru -> Brian Otieno
Chiara Mwangi	1	Amara Wanjiru -> Chiara Mwangi
Grace Achieng	2	Amara Wanjiru -> Brian Otieno -> Grace Achieng

The anchor half of the query finds Amara and David, the two keepers with no mentor. The recursive half then runs repeatedly: first it finds anyone whose mentor is Amara or David (Brian and Chiara), then it runs again on those new rows and finds anyone whose mentor is Brian or Chiara (Grace), then it runs once more, finds nobody new, and stops. Three generations, discovered without knowing in advance how many there'd be. No plain subquery can do this; a subquery is a single, fixed level of nesting, and there's no way to tell it "keep going until you run out of matches." That open-ended repetition is what RECURSIVE adds.

Note: (The || operator concatenates text in PostgreSQL and SQLite. SQL Server uses +, and MySQL uses CONCAT(), so adjust that one line for your engine; everything else here is standard SQL.)

Subquery or CTE: How to Choose

Situation	Reach for
A single value used once, inline, in a `WHERE` or `SELECT`	Subquery
The same intermediate result needed more than once in one query	CTE
A query more than two or three logical steps deep	CTE, for the readability
Walking a hierarchy or chain of unknown depth	Recursive CTE, no alternative
A quick, throwaway filter you'll never look at again	Subquery

The two aren't really competitors. A subquery is a value or a table, dropped exactly where you need it. A CTE is a name, declared once, that the rest of the query can refer to as often as it likes, and in the recursive case, refer to before it's even finished being built. Most real queries end up mixing both: a CTE or two to name the steps that matter, and small scalar subqueries wherever a single value needs to be looked up in passing.

One caution before you assume a CTE is always the faster choice: whether a database materializes a CTE (runs it once, stores the result) or inlines it (folds it into the surrounding query, like a subquery) depends on the engine and its version. PostgreSQL 12 and later will inline a non-recursive CTE when it can; older versions treated every CTE as an optimization fence. When performance is on the line, check your engine's execution plan rather than assuming either form is faster by default.

Try It Yourself

Add a sixth beekeeper as Grace's apprentice, rerun the recursive query, and watch the chain grow by one row without changing a single line of SQL. That's the property no ordinary subquery can offer, and it's the clearest way to feel the difference between the two.

SQL Joins Explained: A Beekeeping Co-op in Six Queries

David Mwandairo — Mon, 21 Sep 2026 17:54:35 +0000

A join is not a special SQL feature reserved for experts. It is the answer to a problem every relational database creates on purpose: your data lives in more than one table, and a single query rarely cares about table boundaries.

To see how each join behaves, forget sales orders and customer records for a moment. We'll run a small urban beekeeping co-op instead: a handful of keepers, their hives, and the honey those hives produce. The data is small enough to hold in your head, which makes it easier to see exactly what each join adds or drops.

The Setup: Three Tables That Don't Line Up Perfectly

Here's the schema.

CREATE TABLE beekeepers (
    keeper_id INT PRIMARY KEY,
    name      VARCHAR(50),
    mentor_id INT REFERENCES beekeepers(keeper_id)
);

CREATE TABLE hives (
    hive_id           INT PRIMARY KEY,
    keeper_id         INT REFERENCES beekeepers(keeper_id),
    location          VARCHAR(50),
    established_year  INT
);

CREATE TABLE harvests (
    harvest_id    VARCHAR(5) PRIMARY KEY,
    hive_id       INT,
    harvest_date  DATE,
    honey_kg      DECIMAL(5,1)
);

And the data, deliberately messy in a realistic way:

beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0

Notice three deliberate cracks in this data. David Kimani (keeper 4) has never registered a hive. Hive 104 lists keeper_id = 99, a keeper who doesn't exist in the beekeepers table at all, maybe a data-entry error, maybe a keeper who left the co-op and got deleted. And harvest H4 points to hive_id = 999, a hive that was never created, likely a typo when someone logged the harvest by hand. Real databases have rows like this. A good demonstration of joins should too.

What a Join Actually Does

A join combines rows from two tables based on a matching condition, usually a shared key. The database doesn't merge the tables permanently; it builds a temporary result set for that one query, row by row, checking the condition each time.

The six join types differ only in one decision: what happens to a row that has no match on the other side. Some joins throw it away. Others keep it and fill the missing columns with NULL. Once you see that single rule at work, every join becomes predictable instead of memorized.

The Joins, One at a Time

1. INNER JOIN: only the rows that match on both sides

SELECT b.name, h.location, h.established_year
FROM beekeepers b
INNER JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023

David Kimani disappears because he has no hive. Hive 104 disappears because its keeper doesn't exist. INNER JOIN is strict: a row survives only if both sides agree it exists.

Use it when an unmatched row would be meaningless in context, for example, a report on "active hive locations by keeper" has no use for a keeper with zero hives.

2. LEFT JOIN: keep everyone from the first table, matched or not

SELECT b.name, h.location, h.established_year
FROM beekeepers b
LEFT JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
David Kimani	NULL	NULL

David is back, with NULL standing in for the hive data he doesn't have. LEFT JOIN never drops a row from the table named on the left of the keyword, no matter what's missing on the right.

Use it whenever the absence itself is the answer you're looking for, a membership list that should show every member even if some haven't done anything yet.

3. RIGHT JOIN: keep everyone from the second table, matched or not

SELECT b.name, h.location, h.established_year
FROM beekeepers b
RIGHT JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
NULL	Abandoned Lot	2020

The mirror image of the previous query. Every hive shows up, including hive 104, whose orphaned keeper_id = 99 produces a NULL name. RIGHT JOIN is rare in practice mostly because you can rewrite it as a LEFT JOIN by swapping the table order, and most style guides prefer that for readability. It's worth knowing, though, especially when you're editing someone else's query rather than writing your own from scratch.

4. FULL OUTER JOIN: keep every row from both sides

SELECT b.name, h.location, h.established_year
FROM beekeepers b
FULL OUTER JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
David Kimani	NULL	NULL
NULL	Abandoned Lot	2020

This is the union of the LEFT and RIGHT results: nothing gets dropped from either table. It's the join to reach for during data audits, where the whole point is finding orphaned or incomplete records on both sides at once. One caution: MySQL doesn't support FULL OUTER JOIN directly. You emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. PostgreSQL, SQL Server, and Oracle all support the keyword as written above.

5. CROSS JOIN: every row paired with every row, no condition at all

Say the co-op is scheduling two workshops and wants a sign-up grid with one row per keeper, per workshop.

CREATE TABLE workshops (
    workshop_id VARCHAR(3),
    topic       VARCHAR(50),
    workshop_date DATE
);
-- W1, Swarm Prevention, 2024-03-10
-- W2, Honey Extraction, 2024-03-17

SELECT b.name, w.topic, w.workshop_date
FROM beekeepers b
CROSS JOIN workshops w;

Result:

name	topic	workshop_date
Amara Wanjiru	Swarm Prevention	2024-03-10
Amara Wanjiru	Honey Extraction	2024-03-17
Brian Otieno	Swarm Prevention	2024-03-10
Brian Otieno	Honey Extraction	2024-03-17
Chiara Mwangi	Swarm Prevention	2024-03-10
Chiara Mwangi	Honey Extraction	2024-03-17
David Kimani	Swarm Prevention	2024-03-10
David Kimani	Honey Extraction	2024-03-17

Four keepers times two workshops gives eight rows. No ON clause, no matching logic, just every possible combination. CROSS JOIN is the one to be careful with: on two tables of ten thousand rows each, you'd get a hundred million rows. Reach for it deliberately, for generating combinations, filling calendar grids, building test data, not by forgetting a WHERE clause on a regular join.

6. SELF JOIN: a table matched against itself

The beekeepers table already tracks mentorship through mentor_id. To turn that into a readable list of who trained whom, join the table to a second copy of itself.

SELECT apprentice.name AS apprentice, mentor.name AS mentor
FROM beekeepers apprentice
INNER JOIN beekeepers mentor ON apprentice.mentor_id = mentor.keeper_id;

Result:

apprentice	mentor
Brian Otieno	Amara Wanjiru
Chiara Mwangi	Amara Wanjiru

There's only one physical table here. The two aliases, apprentice and mentor, let the database treat it as two separate tables for the length of the query. Amara and David don't appear as apprentices because their mentor_id is NULL, an INNER JOIN drops them the same way it would drop any other unmatched row. Swap in a LEFT JOIN if you want every keeper listed, with NULL in the mentor column for those who trained themselves.

A self join is the right tool whenever a table describes a relationship between rows in its own set: employees and managers, cities and neighboring cities, comments and their replies.

Bringing In the Harvests

So far every example has used beekeepers and hives. The harvests table has been sitting there unused, which is its own kind of realistic: plenty of production databases have a table nobody's queried in months. Let's put it to work and see how a hive with no harvest, and a harvest with no hive, behave.

SELECT h.hive_id, h.location, hv.harvest_date, hv.honey_kg
FROM hives h
LEFT JOIN harvests hv ON h.hive_id = hv.hive_id;

Result:

hive_id	location	harvest_date	honey_kg
101	Ruiru Rooftop	2024-05-01	12.5
101	Ruiru Rooftop	2024-09-01	9.0
102	Thika Road Garden	2024-06-15	15.0
103	Kiambu Backyard	NULL	NULL
104	Abandoned Lot	NULL	NULL

Hive 101 shows up twice because it has two harvest records, a reminder that a join's row count follows the data, not the table it started from. Hive 103, Chiara's hive, is only a year old and hasn't been harvested yet, so it appears once with NULL in place of harvest data. LEFT JOIN keeps it in the results instead of hiding a hive that simply hasn't produced anything yet.

Now flip it around and check for harvest H4's orphaned hive_id:

SELECT hv.harvest_id, hv.hive_id, hv.honey_kg, h.location
FROM harvests hv
LEFT JOIN hives h ON hv.hive_id = h.hive_id;

Result:

harvest_id	hive_id	honey_kg	location
H1	101	12.5	Ruiru Rooftop
H2	101	9.0	Ruiru Rooftop
H3	102	15.0	Thika Road Garden
H4	999	5.0	NULL

There it is: 5.0 kg of honey logged against a hive that doesn't exist in the hives table. In a real co-op this is exactly the kind of row a data-quality check should flag, and a LEFT JOIN followed by WHERE h.hive_id IS NULL is the standard way to find it.

Chain all three tables together and you get the report the co-op actually wants: total honey per keeper, including keepers who haven't harvested a drop.

SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
FROM beekeepers b
LEFT JOIN hives h ON b.keeper_id = h.keeper_id
LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
GROUP BY b.name;

Result:

name	total_honey_kg
Amara Wanjiru	21.5
Brian Otieno	15.0
Chiara Mwangi	0.0
David Kimani	0.0

Two LEFT JOINs in a row, one linking keepers to hives, the next linking hives to harvests, and every keeper still makes the list even if the chain runs into a NULL partway through. This is the pattern behind most real reporting queries: keep the entity you're reporting on, then reach outward through as many related tables as the question needs.

Choosing the Right One

You want...	Use
Only rows with a confirmed match on both sides	`INNER JOIN`
Every row from the main table, matches or not	`LEFT JOIN`
Every row from the secondary table, matches or not	`RIGHT JOIN`
Every row from both tables, to catch mismatches on either side	`FULL OUTER JOIN`
Every possible combination, with no matching condition	`CROSS JOIN`
Rows from a table related to other rows in the same table	`SELF JOIN`

Try It Yourself

The fastest way to make this stick is to break your own data the way hive 104 is broken here: create two small tables, delete a foreign key's parent row on purpose, then run all six joins against them and watch what changes. The gaps are where the learning happens.

DDL and DML: how a database designs a room and remembers who escaped it

David Mwandairo — Mon, 21 Sep 2026 17:30:50 +0000

A two-person escape room studio runs on paper for its first year. Room names on a whiteboard, puzzle notes in a shared folder, playtester feedback in a stack of sticky notes. It works until the studio opens its third room and someone asks a simple question: which puzzle has the worst average solve time across all three rooms? Nobody can answer without an afternoon of digging.

This is the moment a studio like this needs a database. And it's the moment every SQL learner runs into two commands that sound similar and get confused constantly: DDL and DML. One builds the containers. The other fills and changes what's inside them. Mixing them up is like confusing the shelf you built with the books you put on it.

We'll build a small database for a fictional studio called Vault of Whispers, using its rooms, puzzles, playtesters, and playtest sessions as the running example. Every query below includes the kind of output you'd see in a database GUI such as MySQL Workbench, DBeaver, or pgAdmin: a status line for the command, plus a result grid where a query returns rows.

What DDL actually does

DDL stands for Data Definition Language. It defines and changes the structure of a database: the tables, their columns, the data types those columns hold, and the constraints that keep bad data out. DDL doesn't touch the data sitting inside tables. It touches the tables themselves.

The core DDL commands:

CREATE: builds a new table, database, index, or view
ALTER: changes an existing table's structure, such as adding or dropping a column
DROP: removes a table or database entirely, structure and data both
TRUNCATE: empties every row from a table but keeps the table structure in place

A useful way to think about it: DDL runs before the studio has any data to work with, and again whenever the shape of that data needs to change. A GUI usually confirms a DDL command with a short status message rather than a grid of rows, because there's no data to display yet.

What DML actually does

DML stands for Data Manipulation Language. It works inside the structure DDL already built. Once a table exists, DML adds rows to it, changes the values in those rows, removes rows, and retrieves rows for reading.

The core DML commands:

INSERT: adds new rows to a table
UPDATE: changes existing values in one or more rows
DELETE: removes rows from a table
SELECT: retrieves rows, whether one column or the result of a join across several tables

Some SQL references classify SELECT under a separate category called DQL, for Data Query Language, since it only reads and never writes. In practice, most teams and most textbooks group it with DML, and we'll do the same here since it's the command you'll use most to check the results of every other one.

The order you write a query in isn't the order it runs in

SELECT causes more confusion than any other DML command, and most of it traces back to one fact: the order you type its clauses is not the order the database engine processes them. Written order puts SELECT first because that's the habit every tutorial teaches. Execution order puts it near the end.

Here's how the two line up:

Writing order	Execution order
SELECT	FROM / JOIN
FROM	WHERE
JOIN	GROUP BY
WHERE	HAVING
GROUP BY	SELECT
HAVING	ORDER BY
ORDER BY	LIMIT
LIMIT

The engine builds its working set from FROM and JOIN first, narrows it with WHERE, collapses it into groups with GROUP BY, filters those groups with HAVING, only then computes the actual output columns in SELECT, and finally sorts and trims with ORDER BY and LIMIT.

This explains a rule every SQL learner hits and few get explained to them: why WHERE can't reference a column alias defined in SELECT, but ORDER BY can. Take the studio's escape-rate query and add a filter for rooms with more than fifteen sessions:

SELECT
    r.room_name,
    COUNT(s.session_id) AS total_sessions,
    ROUND(100.0 * SUM(CASE WHEN s.escaped THEN 1 ELSE 0 END) / COUNT(s.session_id), 1) AS escape_rate_percent
FROM rooms r
JOIN playtest_sessions s ON r.room_id = s.room_id
GROUP BY r.room_name
HAVING COUNT(s.session_id) > 15
ORDER BY escape_rate_percent DESC;

FROM and JOIN run first, combining every room with its matching sessions. HAVING runs after GROUP BY, which is why it can filter on COUNT(s.session_id), a value that only exists once the grouping has happened; WHERE runs before grouping, so it can't see that count at all. SELECT runs next and defines the alias escape_rate_percent. Only then does ORDER BY run, which is why it can sort by that alias directly instead of repeating the full calculation. Write a WHERE escape_rate_percent > 50 clause into this same query and the engine rejects it: at the point WHERE executes, that column doesn't exist yet.

SQL Hack: An acrostic I use to remember the SQL execution order is "Fried Wings Give Heartburn So Order Lightly." (From, Where, Group by, Having, Select, Order by, Limit)

Building the studio's database

Here's the studio's first table, holding the escape rooms themselves.

CREATE TABLE rooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(100) NOT NULL,
    theme VARCHAR(50),
    difficulty VARCHAR(20),
    run_time_minutes INT
);

Output:

Query OK, 0 rows affected (0.04 sec)

A brand-new table has no rows to show, so the GUI confirms the command ran and moves on. Switching to the table's structure tab would now show four named columns with no data in any of them.

Three months later, the studio starts tracking how many hints each puzzle needs on average, and the puzzles table needs a new column. That's ALTER, not INSERT, because it changes the shape of the table, not its contents.

ALTER TABLE puzzles
ADD COLUMN average_hint_count DECIMAL(3,1) DEFAULT 0.0;

Output (Table Structure panel, after refresh):

Column	Type	Null	Key	Default
puzzle_id	INT	NO	PRI	NULL
room_id	INT	NO	MUL	NULL
puzzle_name	VARCHAR(100)	NO		NULL
puzzle_type	VARCHAR(50)	YES		NULL
average_solve_seconds	INT	YES		NULL
average_hint_count	DECIMAL(3,1)	YES		0.0

The new column sits at the bottom, and every existing puzzle row gets the default value of 0.0 automatically. No puzzle data was touched, added, or removed. Only the table's definition changed.

Filling the rooms with data

With the structure in place, the studio's staff enter their first room and its puzzles. This is DML, specifically INSERT.

INSERT INTO rooms (room_name, theme, difficulty, run_time_minutes)
VALUES
    ('The Cartographer''s Study', 'Victorian explorer', 'Hard', 60),
    ('Signal Lost', 'Abandoned space station', 'Medium', 45);

Output:

Query OK, 2 rows affected (0.02 sec)

INSERT INTO puzzles (room_id, puzzle_name, puzzle_type, average_solve_seconds)
VALUES
    (1, 'The Silent Vault', 'Combination lock', 480),
    (1, 'Ink and Compass', 'Physical assembly', 210),
    (2, 'Static on the Line', 'Audio cipher', 300);

Output:

Query OK, 3 rows affected (0.03 sec)

Checking the work means switching to a SELECT, which returns an actual grid rather than a status line.

SELECT puzzle_id, puzzle_name, puzzle_type, average_solve_seconds
FROM puzzles
WHERE room_id = 1;

Output:

puzzle_id	puzzle_name	puzzle_type	average_solve_seconds
1	The Silent Vault	Combination lock	480
2	Ink and Compass	Physical assembly	210

Updating and removing data

The Silent Vault turns out to run faster than expected once players get used to the lock mechanism. The design team wants the average solve time reflected after twenty new playtests bring the number down.

UPDATE puzzles
SET average_solve_seconds = 365
WHERE puzzle_name = 'The Silent Vault';

Output:

1 row(s) affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Running the earlier SELECT again confirms the change in place:

puzzle_id	puzzle_name	puzzle_type	average_solve_seconds
1	The Silent Vault	Combination lock	365
2	Ink and Compass	Physical assembly	210

Later, a playtester who signed up twice by mistake needs one of their duplicate records removed.

DELETE FROM playtesters
WHERE playtester_id = 14;

Output:

1 row(s) affected (0.01 sec)

Where the two meet: a report across four tables

The real payoff of separating structure from data shows up once the studio has enough playtest sessions to ask harder questions, like which room has the highest escape rate. Answering that means joining four tables that DDL built, using the rows that DML filled.

SELECT
    r.room_name,
    COUNT(s.session_id) AS total_sessions,
    SUM(CASE WHEN s.escaped THEN 1 ELSE 0 END) AS successful_escapes,
    ROUND(100.0 * SUM(CASE WHEN s.escaped THEN 1 ELSE 0 END) / COUNT(s.session_id), 1) AS escape_rate_percent
FROM rooms r
JOIN playtest_sessions s ON r.room_id = s.room_id
GROUP BY r.room_name
ORDER BY escape_rate_percent DESC;

Output:

room_name	total_sessions	successful_escapes	escape_rate_percent
Signal Lost	18	15	83.3
The Cartographer's Study	22	9	40.9

That result grid only exists because CREATE TABLE gave each piece of data somewhere to live, and INSERT put real sessions in those tables. Change the table structure later, with ALTER or DROP, and every query built on it changes with it. Change the data, with INSERT, UPDATE, or DELETE, and the structure never moves.

Writing order and execution order aren't the same

Write a SELECT statement and you type it top to bottom: SELECT the columns, FROM the table, WHERE some condition holds, GROUP BY a column, HAVING some aggregate condition, ORDER BY the result. That's the order your fingers move in. It's not the order the database engine runs the statement.

The engine works through roughly this sequence instead:

FROM: gather the source tables and resolve any joins
WHERE: filter individual rows before any grouping happens
GROUP BY: collapse the filtered rows into groups
HAVING: filter those groups
SELECT: pick and compute the columns to return
ORDER BY: sort the final result
LIMIT: cut the result down to a set number of rows

Take the escape rate query from earlier. SELECT sits first on the page, but the engine doesn't touch it first. It starts by joining rooms to playtest_sessions, because nothing else can happen until those source rows exist. Only after grouping the filtered rows by room_name does the engine evaluate the SELECT list, which is why a column alias defined there, like escape_rate_percent, can't be reused in a WHERE clause on the same query: WHERE runs before that alias exists. It can be reused in ORDER BY, since ORDER BY runs after SELECT.

This explains a rule that trips up a lot of people. Filtering on an aggregate inside WHERE fails:

SELECT room_name, COUNT(*) AS total_sessions
FROM playtest_sessions
WHERE COUNT(*) > 10
GROUP BY room_name;

Output:

Error: Invalid use of group function

COUNT(*) doesn't exist yet when WHERE runs, since grouping hasn't happened. Swap WHERE for HAVING, which runs after GROUP BY, and the same idea works:

SELECT room_name, COUNT(*) AS total_sessions
FROM playtest_sessions
GROUP BY room_name
HAVING COUNT(*) > 10;

Output:

room_name	total_sessions
Signal Lost	18

The execution order matters most for SELECT, since it's the one command that runs through all seven steps. DDL commands like CREATE and ALTER skip this pipeline entirely: they act on a table's structure directly, with no rows to filter, group, or sort.

The distinction that actually matters

DDL answers "what does this database look like." DML answers "what does this database currently hold." Confuse a TRUNCATE for a DELETE with a WHERE clause and you'll empty an entire table instead of removing a handful of rows. Confuse an ALTER TABLE for an UPDATE and you'll be trying to change a table's blueprint using a command built to fill in the blanks.

Next time you're staring at a schema, ask which category each command belongs to before you run it: are you reshaping the room, or filling it? Try rebuilding this Vault of Whispers schema yourself, add a hints_used table linked to playtest_sessions, and see what other questions your own DML can answer once the DDL is in place.

A Step-by-Step Tutorial on Power BI’s Local and Cloud Connections

David Mwandairo — Sun, 05 Jul 2026 16:13:46 +0000

Introduction

The data we work on using PowerBI is collected from different sources. These sources include:

Files such as excel, csv and PDF.
Databases such as MySQL, PostgreSQL, SQL Server, Oracle.
Cloud Services such as Azure SQL, Snowflake, BigQuery.
Web/APIs such as REST, OData, SharePoint.

PowerBI can connect to these data sources through different ways. The most basic way is through importing a local csv/excel/PDF files which can be quite tedious especially when the data to be imported is huge. Huge collections of data are majorly stored in databases since it is relatively easy to manage data when it is stored in a database. In this tutorial, we will be covering the basic steps one should apply when connecting PowerBI to either a local or cloud PostgreSQL database.

Connecting to a Local PostgreSQL Database

To connect to a local PostgreSQL database, you have to ensure that PostgreSQL server is installed on your computer. You can get it at PostgreSQL Download. Once installed, follow the instructions in the documentation to install it correctly on your device and set a password that you will remember easily. Another essential software tool needed to manage your PostgreSQL database is Dbeaver which provides an interactive interface for you to interact with your PostgreSQL database.

Once installed, ensure that you connect Dbeaver to your PostgreSQL database by pressing Ctrl+Shift+N and enter your database details as shown below:
Note: The default database that comes installed with PostgreSQL server is named postgres. You can, however, create different databases that you would like to use.

Once you have entered your details, click on Test Connection then click on OK to connect to your database. To upload data to your database, follow the steps that I included in a previous article here, specifically in the Upload Data to a Schema section.

Now that the data has been uploaded, we can import it using PowerBI. First, open PowerBI, create a blank report and click Get data on the home ribbon as shown below:

Next, select the More option and the following dialogue box will appear:

Since we are connecting to a PostgreSQL database, select the PostgreSQL database option to access the following dialogue box:

The server details include the host and the port and they should be entered in the format; host:port, which in this case will be 127.0.0.1:5432. The rest of the details in this section are similar to the details entered in Dbeaver initially.
Note:The Use encrypted connection box is left unchecked because the database is hosted locally.

After entering the details, click next and the following dialogue box will appear:

From this point, click Next and the data will be imported by PowerBI for you to analyze.

Connecting to a Cloud-based PostgreSQL Database

The process involved in connecting to a cloud-based PostgreSQL database is relatively similar to that of a locally hosted database. In this case, we will use an Aiven database. To get an Aiven database, you need to create an account and subsequently create a new project which should be a PostgreSQL instance. Once that is created, you will have a database with details in this form:

Downloading and Importing the SSL Certificate

Now that we have a cloud database, it is possible to connect to it through PowerBI, however, we need to download an SSL certificate to enable the computer to trust the connection between PowerBI Desktop and the Aiven cloud-based database. This is where the Use encrypted connection box will be needed. To download the SSL certificate, go to the CA certificate section as shown in the screenshot above and click on the download button.

Next, on your taskbar, search for computer certificates and select the result shown below:

Once selected, the following dialogue box will appear:

In this dialogue box, navigate to Trusted Root Certification Authorities and click on the drop-down arrow, then right click on Certificates, then go to All Tasks, then Import....

This will lead you to the following dialogue box:

Click Next, upload the certificate file which will have been downloaded as ca.pem from Aiven. You will be required to select the all files option when browsing your device for the certificate file. Continue until you get the confirmation that import is successful as shown below:

Now that the SSL certificate is imported, we can connect PowerBI to the Aiven database.
The procedure to connect to the Aiven database shares the same steps as connecting to the local PostgreSQL database, only that the details entered will be different as shown below:

Since the device now trusts the Aiven cloud-based database after importing the certificate, we can check the Use encrypted connection box to encrypt the connection and access the data needed as shown below:

Conclusion

PowerBI is a useful tool in extracting data from either local sources or cloud-based sources. Local database connections are quite straightforward since everything is available locally. Sources that are cloud-based require additional authentication in order for the extraction to work. This enables any PowerBI user to pull data from different sources all over the internet.

Connecting Data the Right Way: Modeling, Relationships, and Schema Design in Power BI

David Mwandairo — Mon, 29 Jun 2026 01:39:51 +0000

As a data professional, one norm that I have had to accept is that raw data rarely comes in one neat package. Most of the times, it'll be scattered, messy, and it lives across different tables that naturally don't talk to each other. Have you ever stayed up late looking at several spreadsheets trying to figure out how they relate to each other? Well, the good folks at Microsoft built Power BI to solve this. Power BI gives you the tools to create relationships between different sets of messy data. Before building beautiful dashboards with Power BI, however, you will need to understand what is happening beneath the surface.

This article examines the importance of data modelling, relationships, schemas and joins in Power BI. Understanding how these concepts work together and getting them right makes all the difference in creating insightful dashboards.

Beyond the Spreadsheet: Giving Your Data a Voice through Meaningful Relationships

Data modelling involves structuring and organizing your data in a way that Power BI can make sense of it. It's similar to setting up a filing system before you start working. Without having a sensible structure in place, your reports will either break or produce results that are untrustworthy.

In Power BI, the data model resides in the Model View, where all your tables and the connections between them are visibly laid out. A good data model answers one simple question: how does each piece of data relate to every other piece?

In this article, we will use one relatable example. Imagine you own an online bookstore and your data lives in three different tables:

Customers - stores customer names, emails and locations.
Orders - stores order IDs, dates, amounts, and customer references.
Books - stores book titles, authors, genres, and prices.

Each table is useful on its own but limited. Structuring a good data model will connect them in a way that Power BI will understand the full story, for example who bought what, when and for how much.

Using Joins in Power BI to Merge Tables

A join is an operation that combines rows from two tables based on a related column they share. This happens in Power BI within the Power Query Editor, which is the data transformation workspace before the data enters the model. To perform a join in Power Query, you navigate to:

Home > Merge Queries

You then select the two tables that you would like to combine and choose the matching columns. For our bookstore, you would match the Customer_ID column in the Orders table with the Customer_ID column in the Customers table.

There are six types of joins in Power BI and picking the right one is critical:
Note: The left table is the primary table you will choose and make reference to. The right table will be the table you'd like to establish a connection with.

Inner Join

This join returns only the rows where there is a match in both tables. In our bookstore data, only customers who have placed at least one order will be seen in the results

Left Outer Join

This join returns all rows from the left table and only the matching rows from the right table. From our bookstore data, it will return all the customers, even the ones with no orders yet.

Right Outer Join

This is the opposite of a left outer join. It returns all rows from the right table and only the matching rows from the left. From our bookstore example, it will return all the orders even if the customer record is missing.

Full Outer Join

This join returns everything from both tables whether they match or not and missing values will appear as null. From our data, it will return every customer and every order regardless of a match.

Left Anti Join

This join returns only the rows from the left that have no match in the right table. From our bookstore data, it will return the customers who have never placed an order, thus can be used in targeting inactive users.

Right Anti Join

This join returns the rows from the right that have no match in the left table. In our case, it will return the orders with no associated customer record.

Selecting the wrong type of join can subtly corrupt you reports, therefore, always pause and ask yourself what you actually need to see.

Connecting the Dots: How Relationships Shape Power BI Data Models

While joins are necessary in connecting tables during the data preparation stage, relationships connect tables at the model level, thus allowing them to interact during analysis. In the Model View, relationships are created by dragging a column from one table and dropping it onto the matching column in another table. The relationship between them will be represented by a line that Power BI will draw between the two tables.

Types of Relationships

One-to-Many (1:*)

In this relationship type, one record in table X relates to multiple records in table Y, but each record in table Y relates to only one record in table X. For example, one customer can make many orders but each order belongs to one customer.

Many-to-Many (* : *)

Several records in table X can relate to multiple records in table Y. This relationship is complex and can return unanticipated results if not handled carefully even though it is supported in Power BI. It requires special attention to execute. For example, one book can appear in many orders and a single order can contain many books.

One-to-One (1:1)

Each record in table X matches exactly on record in table Y. For example, each customer has exactly one loyalty profile record.

Cross-Filter Direction

Filter direction controls how filters flow between tables when interacting with reports. They are classified into:

Single Direction - Filters flow in one direction i.e. from the lookup table to the data table. This is the usual default.
Bidirectional - Filters flow in both directions. If overused, this can produce confusing results.

How Schema Design Shapes Your Data Model

A schema is the basic layout and structure of a data model. The schema you choose will affect the performance and readability of your Power BI reports. There are two types of schema used in Power BI:

Star Schema

This schema consists of one central Fact Table enveloped by multiple Dimension Tables. The fact table holds the numerical, measurable data while the dimension table holds the descriptive, categorical data. The figure below shows a representation of the star schema:

        [Customers]
             |
[Books] — [Orders] — [Dates]
             |
         [Locations]

Table Type	Example	Contains
Fact Table	Orders	Order amounts, quantities, revenue
Dimension Table	Customers	Names, emails, locations
Dimension Table	Books	Titles, authors, genres, prices
Dimension Table	Dates	Day, month, quarter, year

The star schema is fast and easy to understand. Power BI's engine is optimized to work with this structure, hence reports will load faster and DAX calculations will work more efficiently.

Snowflake Schema

This schema further extends the star schema by breaking down the dimension tables into more related tables. It's structure resembles that of a snowflake because of its branching structure. Its structure is as shared below:

[Author Details] — [Books] — [Orders] — [Customers] — [Customer Segments]

The snowflake schema is known to add complexity to a data model even though it may save on storage space and reduce data redundancy. Filters, in effect, have to move through more relationships to get to their destination, thus slowing down performance and making the data model hard to maintain.
Pro-tip: Stick with the star schema when starting out with Power BI because it is the most used in Power BI projects.

Why Do We Need to Know This?

Charts and visuals are exciting to create when we need to showcase our Power BI projects, but a poorly structured data model will produce bad results. Some of the effects of a poorly structured data model include; filters behaving strangely and numbers not adding up when using DAX functions.

A solid foundation in the creation of good data models will save you from hours of debugging.

Getting your joins, relationships and schema right from the beginning results to:

Simpler DAX calculations.
Faster report performance.
Dashboards that are easier to scale and maintain.
Accurate and trustworthy numbers.

The data model is the foundation of a good Power BI report. When you build it well, everything else will become considerably easier.

Conclusion

Power BI is a capable Business Intelligence tool. It just requires you to understand the fundamentals and it will treat you well. Joins, modelling and relationships aren't the most exciting topics but understanding them well will separate data professionals that prepare reports which look good from those who prepare reports that actually work.

To perfect the Star Schema concept, you should ensure that you use intentional joins in your data. The one-to-many relationships that you build will have to be clean and always ask yourself whether you data reflects real-world logic. Once that foundation is solid, the dashboards and insights will take care of themselves.

Linux Fundamentals for Data Engineering

David Mwandairo — Mon, 15 Jun 2026 22:07:34 +0000

Introduction

The Linux operating system has become the go to platform for handling data engineering workloads in the modern data landscape. Some of the prominent data-related software it powers include:

Amazon Redshift which is a cloud based data warehouse that runs on a customized version of Linux.
Apache Spark which is a big data framework that runs on Linux clusters.
Apache Airflow which is an orchestration tool that is deployed on Linux servers.

Since these industry standard tools depend on Linux to run, proficiency in Linux is now a core competency, therefore, data engineers need to know how to navigate, manipulate and automate data workflows using the command line interface. This article covers some of the basic commands that a beginner in using Linux should know.

Connecting to a Remote Server

To demonstrate how to use the commands, I will first connect to a remote Linux server using the secure shell(ssh) command. Since I use the Fedora Linux distribution, I did not have to install any proprietary software to access the server. I can do it directly from the terminal. To access the server from a specific port, I added -p 22 at the end of the server IP address and entered the password to log in.
ssh root@159.65.222.96 -p 22

Once I logged in, I first updated the server to be able to use the latest features using the command sudo apt update. The commands used in the Ubuntu Linux distribution may be different from other Linux distributions. In this case, we use apt since the server is an Ubuntu Linux distribution.

After completing the update, I created my user account within the server using the command sudo adduser DavidM to isolate my workflow.

I got an error telling me to change the naming convention to my user name but I intend to keep it as it is to make it distinct. To bypass the error, I use the command sudo adduser DavidMw --force-badname and proceed to create my user account.

To switch to my user, I run the command su DavidMw.

Since my account is active, I can alternatively log in directly into the server without going through the root user as shown below. To do this, I run the command ssh DavidMw@159.65.222.96 -p 22.

Checking Postgresql version

To check the Postgresql version available, I run the command psql --version.

Next, I check the status of the Postgresql server using the command sudo systemctl status postgresql.

Now that I have confirmed it is enabled, I can log in to the Postgresql server using the command sudo -i -u postgres. After that, I access the Postgres interface using the command psql.

Creating a Database and Schema

Now that I have logged in to the Postgres user interface, I can create my database using the SQL script CREATE DATABASE DavidMw;.

To confirm whether it has been created, I run the command \l and I am able to see it as shown below. To exit the list of databases, I press 'q'.

Next, I create a schema named 'staging' within my database in which I will upload my data. To do that, I will first move into my database using the command \c then I run the script CREATE SCHEMA staging;.
Note: Always include a semi-colon(;) at the end of your SQL scripts to mark the end of the statement.

To view the schema, I run the command \dn.

Upload Data to a Schema

To upload data to a schema, we use Dbeaver which is a universal database management tool. First, we connect it to the Postgresql server following the steps below.
First, I create a new connection in Dbeaver

Next, I setup the connection using the details I created previously.

Next, I establish my connection and connect to my database.

To import the data, I right-click the staging schema and hover to the "Import Data" option after which I select the file I want to upload.

In this case, I select the 'salary_data.csv' file and continue.

After a few seconds, the data is uploaded.

Common Linux Commands

The core commands used in Linux are necessary for creating, editing and navigating through files. The creating, editing and updating of files in Linux systems happens within directories, hence a data engineer should always know where he/she is within the file system. Thus, the command to run to know where you are in the file system is pwd(print working directory) as shown below:

To see the contents of the directory, we use the command ls(list) and to create a new directory, we use the command mkdir(make directory) to create the 'newfolder' as shown below:

When working with remote servers, it is possible to upload files and directories directly using the scp(secure copy) command. To locate the file to be uploaded, I first navigated into the folder containing the file by using the cd(change directory) command. The difference between uploading a file and a folder is adding '-r' after the scp command. In my case, I uploaded the 'hotel_data.csv', 'salary_data.csv' files and 'stocks' folder to my instance of the server using the process below.
Note: When running the scp command, ensure that to run the command in the origin device's command line interface.

To create files in Linux, one can use either the echo or touch command. The echo command inputs content directly within a file while the touch command creates an empty file. Below is an example of how I created the 'file1.txt' and 'file2.py' files using the two commands respectively.

To insert data into the empty 'file2.py' file, I used the vim editor by running the vi command as shown below. To insert data into the file, press "i" to access the interactive interface and once done, press "Esc" + ":" and subsequently "wq" to save the changes.

To view the changes made, use the command cat followed by the file name.

Files can be viewed in different formats. The ls command enables one to view the files within a directory through the basic format. To view them in a more detailed format, we use the commands:

ls -l   #lists the files in a directory in the long listing format.
ls -la  #shows all the details of all the files within a directory.
ls -a   #lists all the files including the hidden ones.

Copying and moving files are also important commands used in Linux. They are executed by the cp and mv commands respectively. The cp command comes in handy when creating back-up/duplicate files. The mv command moves files from one folder to another and can also be used to rename files. Directories can also be copied by including -r after the cp command as shown below.

To delete files, we use the rm command. Folders can also be deleted by adding "-r" to the rm command to delete the files recursively. To move up a directory, we use the cd .. command.

File Permissions

Setting file permissions help data engineers determine who has access to particular files. File permissions are determined using a 10-character string. An example of a permission string is -rw-rw-r-- where the first character represents the file type which can be "-" to signify a file, "d" to signify a directory and "l" to signify a link. The next 3 characters represent the file owner's permissions, the middle 3 characters represent the group's file permissions and the last 3 characters represent others' file permissions. There are two ways in which file permissions can be assigned as shown in the tables below.

The example below shows how file permissions are given on the command line using the chmod command.

Conclusion

Mastering the Linux fundamentals transforms a data engineer from someone that just runs scripts to someone that can design, build and deploy strong data systems. The commands are important in managing tasks such as file navigation and handling, remote access, file permissions and text processing which form the foundation of data engineering. Through frequent practice, the command line becomes a powerful tool to run data engineering tasks on a large scale.