Readable queries

What you will be able to do

Write a long query as a list of small named steps. Then you can count the rows of each step, and find a quiet mistake before it reaches a report.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions, including the step counts and the 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 diagram 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 45 minutes

What you need to know

• GROUP BY, COUNT(DISTINCT …) and CASE, from Aggregate correctly: GROUP BY and Count with a condition: CASE.
• LEFT JOIN, and why you count the rows after a join, from Join tables and its misconception “a join only adds columns”.
• The 14 repeated ticket rows, and SELECT DISTINCT *, from Relational concepts: check ticket_id.

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 31- and 32-.
• 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. The notebook was tested in Jupyter.

Larkfield is a fictional online shop for home and garden products. Its help desk exports every support ticket. Mei, the data engineer, prepares this export for a model that predicts escalations. The data is made up for this course; there are no real people in it. In this module, Mei writes queries that she will use again and again. They work in readable steps, handle time correctly, and make an extraction that anybody can repeat.

One query, many jobs

Omar, the product manager, asks Mei: “Which customer segment has the most escalated tickets?” To answer, one query must do four jobs:

1. Remove the 14 repeated ticket rows.
2. Find the tickets that have an escalated event in outcomes.
3. Join each ticket to its customer, to get the segment.
4. Count the tickets and the escalations per segment.

You can write all of this as one query, with subqueries inside subqueries. It runs. But when the total is wrong, you cannot see which job went wrong, because the middle results have no names. You cannot count them.

A common table expression (CTE) gives a middle result a name. You write it with WITH name AS (query) at the start of a query. The named result exists only while that query runs. It is not saved in the database. After it, the main SELECT can use the name as if it were a table.

A tiny example

Five ticket rows. T-10002 is in the table twice, an exact copy, as in the real export:

ticket_id	customer_id	team
T-10001	C-0001	delivery
T-10002	C-0001	returns
T-10002	C-0001	returns
T-10003	C-0002	delivery
T-10004	NULL	account

The CTE unique_tickets keeps one copy of each row. The main query then counts its rows:

WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
)
SELECT COUNT(*) AS row_count
FROM unique_tickets;

Predict the count before you run it.

Run SQL in your browser

Try it: a first CTE

This box has only the five rows above. Run the query: the result is 4. Then change the last line to FROM tickets; and run it again. Now the result is 5, because the repeated row counts twice.

SQL query (you can change it)WITH unique_tickets AS ( SELECT DISTINCT * FROM tickets ) SELECT COUNT(*) AS row_count FROM unique_tickets;

Two things make this useful:

• The step has a name. unique_tickets says what the step does. A reader does not have to decode a subquery.
• You can look inside the step. Change only the final SELECT, and you see any step: its rows, or its row count.

You can define several CTEs after one WITH, with a comma between them. A later step can use an earlier one, as a step in a recipe uses the result of the step before.

The pipeline

This diagram shows Mei’s four steps for Omar’s question, with the row count at each step.

Each CTE is one step with a name and a row count. ticket_rows must have exactly as many rows as unique_tickets: one row per ticket.

Text description of the diagram

Three tables are the inputs: tickets (1,417 rows), customers (240 rows) and outcomes (1,874 rows). Step 1, unique_tickets, removes the repeated ticket rows: 1,403 rows. Step 2, escalated_tickets, keeps one row per ticket that has an escalated event: 225 rows. Step 3, ticket_rows, starts from unique_tickets. Two LEFT JOINs add the customer’s segment and an escalated flag. It has 1,403 rows, the same as step 1. Step 4, by_segment, groups the rows by segment: 5 rows.

The important number is the one that must not change. A LEFT JOIN keeps every row on the left side. If each right side has at most one row per key, the join adds columns but no rows. So ticket_rows must have 1,403 rows, exactly as unique_tickets. If it has more, a join multiplied rows.

Worked example: escalations by segment

Mei writes the four steps as four CTEs. Comments name each step’s job:

queries/31-segment-report.sql

-- Escalations by customer segment, in named steps.
WITH unique_tickets AS (
    -- Step 1: one row per ticket (the export has 14 repeated rows)
    SELECT DISTINCT *
    FROM tickets
),
escalated_tickets AS (
    -- Step 2: one row per ticket that has an escalated event
    SELECT DISTINCT ticket_id
    FROM outcomes
    WHERE status = 'escalated'
),
ticket_rows AS (
    -- Step 3: each ticket with its segment and a 0/1 escalated flag
    SELECT
        u.ticket_id,
        CASE
            WHEN u.customer_id IS NULL THEN 'guest'
            WHEN c.customer_id IS NULL THEN 'unknown'
            ELSE LOWER(c.segment)
        END AS segment,
        CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated
    FROM unique_tickets AS u
    LEFT JOIN customers AS c
        ON c.customer_id = u.customer_id
    LEFT JOIN escalated_tickets AS e
        ON e.ticket_id = u.ticket_id
),
by_segment AS (
    -- Step 4: one row per segment
    SELECT
        segment,
        COUNT(*) AS tickets,
        SUM(escalated) AS escalated,
        ROUND(100.0 * SUM(escalated) / COUNT(*), 1) AS escalated_pct
    FROM ticket_rows
    GROUP BY segment
)
SELECT segment, tickets, escalated, escalated_pct
FROM by_segment
ORDER BY tickets DESC;

Step 3 uses CASE to give every ticket a segment. A CASE expression returns the value of the first WHEN that is true. A ticket with no customer_id is a guest checkout, so its segment is guest. A ticket whose customer is not in customers (a deleted account) gets unknown. LOWER makes Trade (3 customers) and trade one group. The second CASE turns “has a matching escalated row” into 1, and “no match” into 0. Then SUM(escalated) counts the escalated tickets.

The result:

segment	tickets	escalated	escalated_pct
trade	634	107	16.9
home	403	44	10.9
business	345	70	20.3
guest	12	1	8.3
unknown	9	3	33.3

Before she sends it, Mei checks that the totals match the source:

• Tickets: 634 + 403 + 345 + 12 + 9 = 1,403, the number of distinct tickets.
• Escalated: 107 + 44 + 70 + 1 + 3 = 225, the number of rows in escalated_tickets.

Her answer to Omar: business customers escalate most often (20.3% of their tickets), and trade customers have the most escalated tickets (107). The guest and unknown groups are too small to compare. This report counts every escalation in the export. The model’s target in Module 5 is narrower: an escalation within 72 hours of creation.

Note

The WITH syntax is the same in PostgreSQL. SQLite has supported CTEs since version 3.8.3 (2014).

Look inside each step

The main SELECT at the end can read any CTE. To check a step, replace only the last part of the query. Run the box below as it is: it shows the row count of ticket_rows.

Run SQL in your browser

Try it: count one step

The result is 1,403 rows. Change FROM ticket_rows to FROM escalated_tickets and run it again: 225. Then replace the last two lines with SELECT * FROM ticket_rows WHERE segment = 'unknown'; to see the 9 tickets of deleted customers.

SQL query (you can change it)WITH unique_tickets AS ( SELECT DISTINCT * FROM tickets ), escalated_tickets AS ( SELECT DISTINCT ticket_id FROM outcomes WHERE status = 'escalated' ), ticket_rows AS ( SELECT u.ticket_id, CASE WHEN u.customer_id IS NULL THEN 'guest' WHEN c.customer_id IS NULL THEN 'unknown' ELSE LOWER(c.segment) END AS segment, CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated FROM unique_tickets AS u LEFT JOIN customers AS c ON c.customer_id = u.customer_id LEFT JOIN escalated_tickets AS e ON e.ticket_id = u.ticket_id ) SELECT COUNT(*) AS row_count FROM ticket_rows;

Guided practice: save the report and its checks

Run the same steps on your computer, and save a second query that prints every step’s row count at once.

1. Open a terminal in your project folder, ~/projects/ticket-data.

2. Make the file queries/31-segment-report.sql. Copy Mei’s query from the worked example into it. The number in the name only keeps your files in order.

3. Run the query:

   python3 q.py queries/31-segment-report.sql

   Output

   segment   tickets  escalated  escalated_pct
   --------  -------  ---------  -------------
   trade     634      107        16.9
   home      403      44         10.9
   business  345      70         20.3
   guest     12       1          8.3
   unknown   9        3          33.3
   
   5 rows

4. Make a second file, queries/32-step-counts.sql. Copy the first three CTEs into it (unique_tickets, escalated_tickets, ticket_rows).

5. After the CTEs, add one SELECT per step, joined with UNION ALL. UNION ALL puts the rows of several queries one under the other. Each SELECT must have the same number of columns.

   queries/32-step-counts.sql (end of the file)

   SELECT 'tickets' AS step, COUNT(*) AS row_count FROM tickets
   UNION ALL
   SELECT 'unique_tickets', COUNT(*) FROM unique_tickets
   UNION ALL
   SELECT 'escalated_tickets', COUNT(*) FROM escalated_tickets
   UNION ALL
   SELECT 'ticket_rows', COUNT(*) FROM ticket_rows
   UNION ALL
   SELECT 'distinct tickets in ticket_rows', COUNT(DISTINCT ticket_id) FROM ticket_rows;

6. Run it:

   python3 q.py queries/32-step-counts.sql

   Output

   step                             row_count
   -------------------------------  ---------
   tickets                          1417
   unique_tickets                   1403
   escalated_tickets                225
   ticket_rows                      1403
   distinct tickets in ticket_rows  1403
   
   5 rows

7. Check the last two lines. They must be equal: one row per ticket in ticket_rows.

Show the complete file 32-step-counts.sql

queries/32-step-counts.sql

-- Row count of each step in 31-segment-report.sql.
WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
),
escalated_tickets AS (
    SELECT DISTINCT ticket_id
    FROM outcomes
    WHERE status = 'escalated'
),
ticket_rows AS (
    SELECT
        u.ticket_id,
        CASE
            WHEN u.customer_id IS NULL THEN 'guest'
            WHEN c.customer_id IS NULL THEN 'unknown'
            ELSE LOWER(c.segment)
        END AS segment,
        CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated
    FROM unique_tickets AS u
    LEFT JOIN customers AS c
        ON c.customer_id = u.customer_id
    LEFT JOIN escalated_tickets AS e
        ON e.ticket_id = u.ticket_id
)
SELECT 'tickets' AS step, COUNT(*) AS row_count FROM tickets
UNION ALL
SELECT 'unique_tickets', COUNT(*) FROM unique_tickets
UNION ALL
SELECT 'escalated_tickets', COUNT(*) FROM escalated_tickets
UNION ALL
SELECT 'ticket_rows', COUNT(*) FROM ticket_rows
UNION ALL
SELECT 'distinct tickets in ticket_rows', COUNT(DISTINCT ticket_id) FROM ticket_rows;

Keep this file next to the report. When you change the report, run the counts again. If ticket_rows and distinct tickets in ticket_rows are different, a join added rows.

Your turn: escalations by team

Grace, the support lead, asks the same question per team: delivery, returns, payment, warranty and account. Change one condition: group by team instead of the customer’s segment. The team is a column of tickets, so you do not need the customers table.

Run SQL in your browser

Your turn: escalations by team

Write the query. The result has five rows, one per team, with three columns: team, tickets, escalated. The tickets must add up to 1,403 and the escalations to 225. The check compares your result with ours; the row order does not count.

SQL query (you can change it)WITH unique_tickets AS ( SELECT DISTINCT * FROM tickets ), escalated_tickets AS ( SELECT DISTINCT ticket_id FROM outcomes WHERE status = 'escalated' ) -- Add a step ticket_rows with ticket_id, team and a 0/1 escalated flag. -- Then return three columns: team, tickets, escalated. SELECT COUNT(*) FROM unique_tickets;

Show the example answer

WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
),
escalated_tickets AS (
    SELECT DISTINCT ticket_id
    FROM outcomes
    WHERE status = 'escalated'
),
ticket_rows AS (
    SELECT
        u.ticket_id,
        u.team,
        CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated
    FROM unique_tickets AS u
    LEFT JOIN escalated_tickets AS e
        ON e.ticket_id = u.ticket_id
)
SELECT team, COUNT(*) AS tickets, SUM(escalated) AS escalated
FROM ticket_rows
GROUP BY team
ORDER BY tickets DESC;

team	tickets	escalated
delivery	449	52
returns	321	46
payment	240	51
warranty	205	60
account	188	16

The totals are 1,403 tickets and 225 escalations, as for the segments. Warranty tickets escalate most often: 60 of 205, or 29.3%. The team counts are a little smaller than in the raw table (delivery: 449, not 452), because unique_tickets removed the repeated rows.

Common mistake

A step without DISTINCT adds three tickets

Symptom: Tomás copies the report and removes DISTINCT from escalated_tickets, because “a ticket is escalated or not”. The query runs with no error. The segment table looks normal. But the tickets add up to 1,406, not 1,403, and the escalations to 228, not 225.

Cause: outcomes has one row per event, not per ticket. Three tickets have their escalated event logged twice (a retry: T-10115, T-10887 and T-11005). Without DISTINCT, escalated_tickets has 228 rows for 225 tickets. The LEFT JOIN matches each of the three tickets twice, so ticket_rows gets 1,406 rows.

Diagnose: run the step counts (queries/32-step-counts.sql). ticket_rows has 1,406 rows, but distinct tickets in ticket_rows is still 1,403. The first step whose count goes up is the step with the problem.

Fix: make each step have the grain that the next step needs. Here: one row per ticket, so SELECT DISTINCT ticket_id. Then run the counts again.

In AI work: a ticket that appears twice in a training table counts twice. It gets more weight in training, and it can land in both the train set and the test set. Module 5 explains these sets. Nothing warns you. Only a row count does.

Check your understanding

In Mei's report, you remove DISTINCT from unique_tickets, so the step reads every row of tickets. Predict the row count of ticket_rows, and explain it.

Show the answer

1,417 rows. tickets has 1,417 rows, 14 of them exact copies. The LEFT JOINs keep every left row and add no rows, because customers and escalated_tickets have at most one row per key. So ticket_rows has one row per ticket row, and the 14 repeated tickets count twice. The step counts show it: ticket_rows 1,417, but distinct tickets in ticket_rows 1,403.

Check your understanding

The same report can be one query with nested subqueries. Give two reasons to write it with CTEs.

Show the answer

1. Each step has a name that says what it does (unique_tickets, escalated_tickets), so a reader can follow the query from top to bottom.
2. You can check each step: change only the final SELECT to count or show the rows of any CTE. With nested subqueries, the middle results have no names, and you must cut the query apart to see them.

Check your understanding

Which row count in the pipeline must not change, and why?

Show the answer

ticket_rows must have the same count as unique_tickets: 1,403. Step 3 only adds columns (the segment and the flag) with LEFT JOINs. A LEFT JOIN keeps every left row, and adds rows only when the right side has more than one match. So a higher count means that a right side has repeated keys.

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

The online-shop case study builds a customer-retention query as a chain of named steps.

Recap

• A CTE (WITH name AS (...)) gives a middle result a name for one query. Several CTEs, separated by commas, make a pipeline of steps.
• To check a step, change only the final SELECT: count its rows, or show them.
• Write down the row count that each step must have. A LEFT JOIN that only adds columns must not change the count.
• Save a step-count query next to each report, and run it after every change.

Next, you work with time: filter tickets by date, and find each ticket’s latest outcome event with window functions.

Next: Time and ranking