Time and ranking

What you will be able to do

Select tickets by time without losing rows at the edge of a range. Then pick exactly one latest outcome event per ticket, even when two events have the same time.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions, including the tie failure case. Its first cell downloads the practice data and builds the database, so it works without the local project from Module 1. Read the explanations and the partition table here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• How to write a query as CTE steps and count the rows of each step, from Readable queries: Look inside each step.
• The 14 repeated ticket rows and SELECT DISTINCT *, from Relational concepts: check ticket_id.
• How a join with outcomes repeats a ticket once per event, from Join tables: the join adds rows.

What you need on your computer (only for the local route; the notebook and the boxes on this page need none of it)

• The folder ~/projects/ticket-data with larkfield.db and q.py. You made it in Set up a practice database: Build the database.
• If the folder or the database is missing or changed, follow that section again, or reset the database with python3 get_data.py --reset.
• The folder queries/, where you save .sql files and run them with q.py, as in Run a saved query. This lesson adds 33-customer-history.sql.
• A terminal open in that folder. This lesson writes python3, as on macOS and Linux. On Windows, type python instead, as explained in Which Python command to type.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4). The SQL boxes run SQLite 3.49.1 in the browser. Window functions need SQLite 3.25 or later. The notebook was tested in Jupyter.

Dates are text in SQLite

SQLite has no date type. Larkfield’s export stores every time as text in one fixed format, YYYY-MM-DD HH:MM:SS, for example 2026-01-05 07:30:24. The order of the parts is year, month, day, hour, minute, second: from the largest unit to the smallest. So the alphabetical order of the text is also the time order, and <, > and MAX work on it.

SQLite has functions that read this text:

Expression	Result for T-10001 (2026-01-05 07:30:24)	What it does
date(created_at)	2026-01-05	Keeps the day only.
strftime('%Y-%m', created_at)	2026-01	Formats the time. %Y is the year, %m the month, %H the hour.
datetime(created_at, '+72 hours')	2026-01-08 07:30:24	Adds time. '-1 day' and '+30 minutes' also work.
julianday(created_at)	2461045.81…	A day number with a fraction. The difference of two day numbers is a number of days.

In PostgreSQL

Times have a real type, timestamp. Write created_at::date, date_trunc('month', created_at) or to_char(created_at, 'YYYY-MM'), and created_at + INTERVAL '72 hours'. The difference of two timestamps is an interval: EXTRACT(EPOCH FROM b - a) / 86400 gives days. Not run in this course.

The model in this course predicts an escalation within 72 hours of a ticket’s creation. datetime gives that deadline. Ticket T-10008 has two outcome events:

Run SQL in your browser

Try it: a 72-hour deadline

You see two rows. The deadline is 2026-01-09 08:14:45. The escalation came about 4 hours after creation, so within_72h is 1 (true). SQLite shows true as 1 and false as 0. Change '+72 hours' to '+2 hours' and run it again: both events are now after the deadline.

SQL query (you can change it)SELECT t.ticket_id, t.created_at, datetime(t.created_at, '+72 hours') AS deadline, o.recorded_at, o.status, o.recorded_at <= datetime(t.created_at, '+72 hours') AS within_72h FROM tickets AS t JOIN outcomes AS o ON o.ticket_id = t.ticket_id WHERE t.ticket_id = 'T-10008';

First, check the range

Before you filter by time, look at the smallest and the largest value. Mei runs this on the export:

SELECT MIN(created_at) AS first_ticket, MAX(created_at) AS last_ticket
FROM tickets;

first_ticket	last_ticket
2026-01-05 07:30:24	2036-01-24 21:27:43

The export is from 2026-07-01, so a ticket from 2036 cannot exist. It is T-10147, a warranty ticket with a mistyped year. MAX does not warn you. Any “latest ticket” or “last 30 days” query that uses MAX(created_at) is now wrong by ten years. Mei does not change the export. She makes her selections with explicit start and end dates, and Module 4 decides what to do with the row.

Half-open ranges

Grace wants the tickets of March. Tomás writes:

SELECT COUNT(*) FROM tickets
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31';

The result is 242. Mei writes:

SELECT COUNT(*) FROM tickets
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

The result is 252. Ten ticket rows are missing from Tomás’s count: all the tickets of 31 March. BETWEEN a AND b means >= a AND <= b. As text, '2026-03-31 08:12:00' comes after '2026-03-31', because it is longer and starts with the same characters. So every ticket on 31 March after midnight is outside the range.

Mei’s form is a half-open range: it includes the start and excludes the end. The end is the first moment that you do not want, here 1 April. This form has three good properties:

• It works for every time of day, with no 23:59:59 tricks.
• Months fit together with no gap and no overlap: March ends where April starts.
• The same form works in PostgreSQL with a real timestamp column.

Run SQL in your browser

Your turn: the tickets of May

Run the query first: 228 rows. Change the filter to a half-open range. Then the check passes. Predict the difference before you run it: how many tickets were created on 31 May?

SQL query (you can change it)-- Count the ticket rows created in May 2026. SELECT COUNT(*) AS may_rows FROM tickets WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31';

Show the example answer

SELECT COUNT(*) AS may_rows
FROM tickets
WHERE created_at >= '2026-05-01'
  AND created_at <  '2026-06-01';

The result is 232 rows. The 4 extra rows are the tickets of 31 May. (232 counts rows. The repeated ticket rows are still in the table, so May has 228 distinct tickets.)

Window functions

A GROUP BY turns many rows into one row per group. Sometimes you want to keep every row, and add a value that depends on the other rows of its group. Examples: “this is the customer’s 3rd ticket”, or “this is the ticket’s latest event”.

A window function does this. It computes a value for each row from a group of related rows, the window, and it keeps every row. You write it with OVER (...):

ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY recorded_at DESC, outcome_id DESC)

• PARTITION BY ticket_id splits the rows into partitions, one per ticket. Each partition is numbered on its own.
• ORDER BY recorded_at DESC, outcome_id DESC orders the rows inside each partition: the newest event first.
• ROW_NUMBER() gives 1, 2, 3, … in that order. Each row gets a different number, even when two rows are equal in the ORDER BY.

Three functions are enough for this course:

Function	Gives	Ties (equal values in ORDER BY)
ROW_NUMBER()	1, 2, 3, …	Different numbers. The database decides which tied row comes first, unless you add a column that breaks the tie.
RANK()	1, 2, 2, 4, …	The same number. After a tie, it skips numbers.
LAG(column)	The value of the column in the previous row of the partition	NULL for the first row.

The partitioned window

Here are the outcome events of three tickets, numbered with the ROW_NUMBER() above. The first column shows the partitions: one cell spans all the events of one ticket. The highlighted row in each partition has rn = 1: it is the latest event, the row to keep.

Partition (ticket_id)	outcome_id	recorded_at	status	rn	RANK by time only
T-10120	O-00246	2026-01-29 19:41:19	resolved	1 (kept)	1
	O-00155	2026-01-21 21:26:23	escalated	2	2
T-10135	O-00182	2026-01-24 05:13:30	resolved	1 (kept)	1
T-10136	O-00229	2026-01-28 19:46:52	resolved	1 (kept)	1
	O-00228	2026-01-28 19:46:52	reopened	2	1
	O-00183	2026-01-24 08:26:29	resolved	3	3

Read it one partition at a time:

• T-10120 was escalated, then resolved about 8 days later. The resolved event is the latest.
• T-10135 has one event. It is number 1 in its own partition.
• T-10136 was resolved on 24 January. On 28 January, the customer wrote again: the ticket was reopened and resolved in the same second. recorded_at cannot tell which came first. outcome_id can: the help desk gives IDs in logging order, so O-00229 was logged after O-00228. The latest event is the resolution.

The last column shows RANK() OVER (PARTITION BY ticket_id ORDER BY recorded_at DESC), with no outcome_id. For T-10136 it gives two rows with rank 1. A filter WHERE rank = 1 keeps both, and the ticket appears twice.

Worked example: the latest outcome of each ticket

Priya, the data scientist, asks Mei for one row per ticket with its latest status. A window function cannot go in a WHERE clause, because WHERE runs before the window is computed. So Mei uses two CTE steps: first number the rows, then keep number 1.

WITH ranked AS (
    SELECT
        ticket_id,
        outcome_id,
        recorded_at,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY ticket_id
            ORDER BY recorded_at DESC, outcome_id DESC
        ) AS rn
    FROM outcomes
),
latest AS (
    SELECT ticket_id, outcome_id, recorded_at, status
    FROM ranked
    WHERE rn = 1
)
SELECT
    COUNT(*) AS latest_rows,
    COUNT(DISTINCT ticket_id) AS tickets
FROM latest;

latest_rows	tickets
1399	1399

The two numbers are equal: one row per ticket. outcomes has events for 1,399 different ticket IDs. Four of them (T-9990 to T-9993) are not in tickets. The extraction in the next lesson leaves them out.

Then Mei checks the tie handling. She replaces the window with RANK() and leaves out outcome_id:

Ranking	Rows with number 1	Tickets
ROW_NUMBER(), ORDER BY recorded_at DESC, outcome_id DESC	1,399	1,399
RANK(), ORDER BY recorded_at DESC	1,432	1,399

33 tickets have more than one event at their latest time. Eight are like T-10136: reopened and resolved in the same second. The other 25 are retries: the same event logged twice, with a new outcome_id. RANK() shows every tie, which is useful to find them. To keep exactly one row, use ROW_NUMBER() and a column that breaks the tie.

Run SQL in your browser

Try it: ROW_NUMBER and RANK side by side

You see the six rows of the table above. Look at T-10136: rn is 1 and 2, rank_by_time is 1 and 1. Then add 'T-10115' to the list. Its escalation was logged twice (O-00143 and O-00144): a retry.

SQL query (you can change it)WITH ranked AS ( SELECT ticket_id, outcome_id, recorded_at, status, ROW_NUMBER() OVER ( PARTITION BY ticket_id ORDER BY recorded_at DESC, outcome_id DESC ) AS rn, RANK() OVER ( PARTITION BY ticket_id ORDER BY recorded_at DESC ) AS rank_by_time FROM outcomes ) SELECT ticket_id, outcome_id, recorded_at, status, rn, rank_by_time FROM ranked WHERE ticket_id IN ('T-10120', 'T-10135', 'T-10136') ORDER BY ticket_id, rn;

Put each customer’s tickets in order

The same tools answer a question about customers. Omar asks: “Do customers come back soon after a ticket?” For each customer, Mei numbers the tickets in time order and computes the days since the previous ticket with LAG.

1. Make the file queries/33-customer-history.sql with this query:

   queries/33-customer-history.sql

   -- Each customer's tickets in time order, with the days since the previous ticket.
   WITH unique_tickets AS (
       SELECT DISTINCT *
       FROM tickets
   )
   SELECT
       customer_id,
       ticket_id,
       created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS nth_ticket,
       ROUND(
           julianday(created_at)
           - julianday(LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id)),
           1
       ) AS days_since_previous
   FROM unique_tickets
   WHERE customer_id = 'C-0003'
   ORDER BY customer_id, nth_ticket;

2. Predict the value of days_since_previous in the first row.

3. Run the query:

   python3 q.py queries/33-customer-history.sql

   Output

   customer_id  ticket_id  created_at           nth_ticket  days_since_previous
   -----------  ---------  -------------------  ----------  -------------------
   C-0003       T-10047    2026-01-10 12:00:09  1           NULL
   C-0003       T-10391    2026-02-24 13:05:53  2           45.0
   C-0003       T-10414    2026-02-26 17:40:38  3           2.2
   C-0003       T-10598    2026-03-20 13:35:27  4           21.8
   C-0003       T-10877    2026-04-24 08:18:10  5           34.8
   
   5 rows

4. Check the first row. LAG is NULL, because the first ticket has no previous ticket, and the difference with NULL is NULL.

5. Check one difference by hand. From 10 January 12:00 to 24 February 13:05 is 45 days and about one hour: 45.0.

6. Remove the line WHERE customer_id = 'C-0003' and run the query again. Now every customer has a partition, and nth_ticket starts at 1 for each one.

Two details in the query matter:

• ORDER BY created_at, ticket_id inside OVER. If one customer opens two tickets in the same second, ticket_id decides the order. Without it, the order of those two tickets could change from one run to the next.
• WHERE runs before the window. A filter on customer_id is safe here, because each customer is a whole partition. A filter on a date would remove earlier tickets, and then “the previous ticket” would be a different ticket.

Your turn: a customer with a repeated ticket

Customer C-0052 has a ticket that appears twice in the export. The query below reads tickets directly. Run it, and look at the second row.

Run SQL in your browser

Your turn: one row per ticket

The second row says that C-0052 came back after 0 days, and the customer seems to have 5 tickets. Change the query so that each ticket counts once. Keep the four columns and the order. The check passes when the result has 4 rows.

SQL query (you can change it)SELECT ticket_id, created_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS nth_ticket, ROUND( julianday(created_at) - julianday(LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id)), 1 ) AS days_since_previous FROM tickets WHERE customer_id = 'C-0052' ORDER BY nth_ticket;

Show the example answer

WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
)
SELECT
    ticket_id,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS nth_ticket,
    ROUND(
        julianday(created_at)
        - julianday(LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id)),
        1
    ) AS days_since_previous
FROM unique_tickets
WHERE customer_id = 'C-0052'
ORDER BY nth_ticket;

ticket_id	created_at	nth_ticket	days_since_previous
T-10609	2026-03-22 07:00:30	1	NULL
T-10924	2026-04-30 06:35:02	2	39
T-10980	2026-05-08 05:08:09	3	7.9
T-11109	2026-05-25 19:41:30	4	17.6

T-10609 was in the export twice. The copy became the customer’s “2nd ticket”, 0 days after the first. A feature such as “tickets in the last 90 days” would count it twice.

Common mistake

Tied events make resolved tickets look open

Symptom: Tomás writes the latest-outcome query with ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY recorded_at DESC), with no outcome_id. The query returns 1,399 rows, one per ticket, so the count check passes. But his status summary says that 9 tickets end in reopened. Mei’s says 1. Grace checks two of the tickets in the help desk: they are resolved.

Cause: for 8 tickets, the reopened and resolved events have the same recorded_at. With only recorded_at in the ORDER BY, the two rows are a tie, and ROW_NUMBER() may number them in any order. On the SQLite of this course, it put reopened first for all 8. Another database version, or the same query after an index change, can choose differently. The result is not wrong in one obvious way: it is not reproducible.

Diagnose: count the ties. Use RANK() with the same ORDER BY, and compare the rows with rank 1 to the number of tickets: 1,432 rows for 1,399 tickets. Then look at a few tied tickets, for example T-10136.

Fix: add a column that makes the order unique and has a meaning: ORDER BY recorded_at DESC, outcome_id DESC. outcome_id follows the logging order, so the event logged last wins. Write this rule down, because Priya must know it.

In AI work: the latest status often becomes the target, the value that a model learns to predict. Arbitrary ties put wrong targets on some rows, and a rerun of the same query can give a different training set.

Check your understanding

BETWEEN '2026-06-01' AND '2026-06-30' selects the June tickets. Which tickets does it miss, and how do you write the filter instead?

Show the answer

It misses every ticket created on 30 June after midnight, because '2026-06-30 20:04:36' is greater than '2026-06-30' as text. Write a half-open range: created_at >= '2026-06-01' AND created_at < '2026-07-01'. On this export, the difference is 12 rows: the tickets of 30 June.

Check your understanding

For a ticket whose two latest events have the same recorded_at, what do ROW_NUMBER() and RANK() return, if the ORDER BY has only recorded_at DESC?

Show the answer

RANK() gives both rows the number 1, so a filter on 1 keeps two rows for the ticket. ROW_NUMBER() gives 1 and 2, so a filter keeps one row. But the database chooses which one, and it may choose differently another time. Add outcome_id DESC to the ORDER BY to make the choice fixed and correct.

Check your understanding

Why can you not write WHERE ROW_NUMBER() OVER (...) = 1 directly?

Show the answer

The database runs WHERE before it computes window functions, so the row numbers do not exist yet. Compute the number in one step (a CTE), and filter on it in the next step: WHERE rn = 1.

See it end to endExplain an online shop's salesCase study

The online-shop case study uses LAG to find where two copies of the data overlap, and ROW_NUMBER with real ties.

See it end to endPlan overnight bike rebalancingCase study

The bike case study uses LAG over each bike to find moves that no rider made.

Recap

• In SQLite, times are text in the form YYYY-MM-DD HH:MM:SS. date(), strftime(), datetime(x, '+72 hours') and julianday() read and change them.
• Check MIN and MAX before you trust a time column: here MAX(created_at) is a ticket from 2036.
• Use half-open ranges: >= start AND < end. BETWEEN loses the last day.
• A window function adds a value to each row from its partition and keeps every row. ROW_NUMBER() numbers rows, RANK() shows ties, LAG() reads the previous row.
• For “latest per ticket”, order by time and by a column that breaks ties: ORDER BY recorded_at DESC, outcome_id DESC. Then check that rows = distinct tickets.

Next, you run these queries from Python with parameters, and record what you selected, so that anybody can repeat the extraction.

Next: Reproducible extraction