Business questions are almost always anchored in time: How many people did we hire each year? Are newer cohorts paid differently from older ones? Has headcount grown? Answering any of them means grouping rows by a period rather than by a department or a status. In this dataset every employee carries a hired_on date stored as text in the YYYY-MM-DD shape SQLite expects. That text is sortable and sliceable — sort it and the years fall into chronological order; slice the first four characters and you have the year. A time series plots a metric over those periods; a cohort groups people by the period they started and then compares them. Both rest on the same move: extract a period from the date, group by it, and aggregate.
flowchart LR D["hired_on = 2021-09-15"] --> S["substr(hired_on, 1, 4)"] S --> Y["year = 2021"] Y --> G["GROUP BY year"] G --> R["one row per year"] style R fill:#0e7490,color:#fff
Pull the period out of the date
The workhorse function is substr(hired_on, 1, 4), which takes the first four characters of the date text — the year. Group by that expression and COUNT(*) becomes hires per year; AVG(salary) becomes the average pay of each year's hires. Because the dates are ISO text, the years sort correctly as ordinary strings: '2016' before '2017' before '2018'. For a coarser or finer period, change the slice — substr(hired_on, 1, 7) gives YYYY-MM for a monthly series, and SQLite's strftime('%Y', hired_on) does the same year extraction by name. Whatever period you choose, the shape is identical: derive a period value, group by it, aggregate the rest. The period column is just another grouping key.
SELECT substr(hired_on, 1, 4) AS hire_year,
COUNT(*) AS hires
FROM employees
GROUP BY hire_year
ORDER BY hire_year;
Cohorts: compare groups by when they started
A cohort is a group defined by a shared starting event — here, the year someone was hired. The analysis asks whether that starting event predicts anything later: do 2020 hires out-earn 2016 hires, are recent cohorts larger? Mechanically it is the same query as a time series — group by the hire year — but the question shifts from how does a total move over time? to how do these groups differ? The average salary of each hire-year cohort is one row per year, and reading those rows top to bottom tells you whether pay drifts with tenure or with the market year someone entered. Same SQL, different question — which is the point: once you can group by a period, both analyses are open to you.
flowchart TD E1["hired 2017"] --> C1["cohort 2017"] E2["hired 2020"] --> C2["cohort 2020"] E3["hired 2020"] --> C2 E4["hired 2023"] --> C3["cohort 2023"] C1 --> M1["avg salary per cohort"] C2 --> M2["compare across cohorts"] C3 --> M2 style M2 fill:#0e7490,color:#fff
SELECT substr(hired_on, 1, 4) AS cohort,
ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY cohort
ORDER BY cohort;
From period counts to a running total
A list of hires per year answers how many each year; a running total answers how many in total by this year. That cumulative is a window function: take the per-year count and wrap it in SUM(hires) OVER (ORDER BY hire_year), and each row shows the total of itself and every earlier year. The ORDER BY inside OVER is what makes it cumulative rather than a flat grand total — drop it and every row gets the same company-wide number. Because the years sort chronologically as text, the accumulation runs in the right order automatically. This is the same window machinery from the earlier lesson, now pointed at time: the running total grows with each period, tracing the company's growth.
flowchart LR Y1["2019: 2 hires"] --> R1["running: 4"] Y2["2020: 3 hires"] --> R2["running: 7"] Y3["2021: 3 hires"] --> R3["running: 10"] R1 --> R2 R2 --> R3 style R3 fill:#0e7490,color:#fff
WITH per_year AS (
SELECT substr(hired_on, 1, 4) AS hire_year, COUNT(*) AS hires
FROM employees GROUP BY hire_year
)
SELECT hire_year,
hires,
SUM(hires) OVER (ORDER BY hire_year) AS total_headcount
FROM per_year
ORDER BY hire_year;
Why the dates are text
SQLite has no dedicated date type; it stores dates as text, real, or integer and leaves the handling to you. This dataset uses ISO-8601 text — YYYY-MM-DD — precisely because that format sorts chronologically as ordinary text and slices cleanly with substr. A date stored as DD/MM/YYYY would sort by day then month and slice into nonsense, which is why every database guide recommends ISO order. When you meet a real table with messy dates, the first job is usually coercing them into this sortable shape. For this course the storage is already correct, so the period extraction just works — but knowing why it works saves you when it suddenly does not.
Count how many employees were hired in each year. Extract the year with substr(hired_on, 1, 4) aliased hire_year, count the rows as hires, and order the result from earliest to latest year.
SELECT -- extract the year and count hires here FROM employees -- group and order here ;
Year column: substr(hired_on, 1, 4) AS hire_year.
Count: COUNT(*) AS hires.
GROUP BY hire_year ORDER BY hire_year for a chronological series.
SELECT substr(hired_on, 1, 4) AS hire_year, COUNT(*) AS hires FROM employees GROUP BY hire_year ORDER BY hire_year;
For each hire-year cohort, show the average salary rounded to the nearest whole number, aliased avg_salary, ordered by cohort from earliest to latest.
SELECT -- cohort year and average salary here FROM employees -- group and order here ;
Cohort column: substr(hired_on, 1, 4) AS cohort.
Wrap AVG(salary) in ROUND(..., 0).
GROUP BY cohort ORDER BY cohort.
SELECT substr(hired_on, 1, 4) AS cohort, ROUND(AVG(salary), 0) AS avg_salary FROM employees GROUP BY cohort ORDER BY cohort;
This query is meant to show one row per hire year, but it groups by the full hired_on date, producing a separate row for every single day someone was hired. Fix it to group by the year instead, counting hires per year and ordering earliest to latest.
SELECT hired_on AS period, COUNT(*) AS hires FROM employees GROUP BY hired_on ORDER BY hired_on ;
Replace hired_on in the SELECT, GROUP BY, and ORDER BY with substr(hired_on, 1, 4).
Alias it hire_year so the column reads cleanly.
You should end with one row per year, not one per day.
SELECT substr(hired_on, 1, 4) AS hire_year, COUNT(*) AS hires FROM employees GROUP BY hire_year ORDER BY hire_year;
Show the company's headcount growing over time: for each hire_year, the hires that year and the total_headcount accumulated up to and including that year. Compute the per-year counts in a subquery or CTE, then apply a running-total window ordered by year. Order the final rows earliest to latest.
SELECT hire_year, hires, -- running total window here AS total_headcount FROM ( SELECT substr(hired_on, 1, 4) AS hire_year, COUNT(*) AS hires FROM employees GROUP BY hire_year ) ORDER BY hire_year;
The inner query already gives one row per year with its hire count.
Wrap hires in SUM(hires) OVER (ORDER BY hire_year) to accumulate it.
The final row's total_headcount should equal the whole-company headcount.
WITH per_year AS (SELECT substr(hired_on, 1, 4) AS hire_year, COUNT(*) AS hires FROM employees GROUP BY hire_year) SELECT hire_year, hires, SUM(hires) OVER (ORDER BY hire_year) AS total_headcount FROM per_year ORDER BY hire_year;
You run GROUP BY hired_on expecting one row per hire year, but you get one row per day. Why?
The raw column holds a full day, so grouping by it makes a group per day. Derive the period first — substr(hired_on, 1, 4) for the year — and group by that to collapse the days into years.
Recap
- Group time data by a period derived from the date, not the raw date:
substr(hired_on, 1, 4)gives the year. - ISO dates sort and slice correctly as text; messy dates must be coerced to
YYYY-MM-DDfirst. - A cohort groups rows by a starting event (hire year) and compares a metric across the groups.
- A running total over time is
SUM(n) OVER (ORDER BY period)— theORDER BYis what makes it cumulative. - Grouping by the full date instead of the period fragments the series into one row per day.
Next you will guard these reports with data-quality checks, writing queries that surface duplicates, gaps, and broken references before they reach a reader.