SQL Data Analysis Practical

A hands-on exercise for querying, joining, summarizing, validating, and interpreting structured data with SQL

Author

Complex Data Insights

Published

Aug 2026

COMPLEX DATA INSIGHTS
CDI Practicals

Hands-on exercises and step-by-step tools for doing, applying, and building evidence.

SQL Data Analysis Practical

A hands-on exercise for querying, joining, summarizing, validating, and interpreting structured data with SQL

AuthorComplex Data Insights
PublishedAug 2026
ID CDI-DATASCIENCE-PRACTICAL-005
Type Practical Product
Audience Students, researchers, analysts, professionals, mentors, and learners developing practical data science skills
Theme Reliable SQL analysis begins with a clearly understood relational structure, carefully constructed queries, validated results, and evidence-aligned interpretation

Practical Overview

SQL is most useful when it does more than return rows. A dependable analysis must preserve the intended unit of observation, combine tables without silently duplicating records, reconcile totals, expose data-quality problems, and support a conclusion that is no stronger than the evidence.

In this practical, you will analyse a synthetic service-delivery database containing clients, facilities, regions, services, and service records. You will work from broad schema inspection to focused operational questions, saving selected query results as reproducible outputs.

What You Will Produce

By the end, you will have:

  • a synthetic SQLite database;
  • a database inventory;
  • regional and monthly performance tables;
  • a within-region service ranking;
  • a data-quality summary;
  • a monthly completion-rate figure; and
  • a short, evidence-aligned interpretation.

Learning Outcomes

You will be able to:

  1. inspect relational tables, columns, keys, and relationships;
  2. define the grain of a query before interpreting its output;
  3. filter, derive, aggregate, and sort fields with SQL;
  4. join tables while checking cardinality and row counts;
  5. use common table expressions and window functions;
  6. identify missing, invalid, duplicate, and unmatched records;
  7. validate totals and exported results; and
  8. distinguish findings from explanations and recommendations.

All people, facilities, identifiers, and values in this practical are synthetic. They are intended only for teaching and workflow testing.

Before You Begin

Tools and Requirements

You need Python 3.10 or later, VS Code with the Python and Jupyter extensions, and Quarto for rendering the practical. The packages listed in requirements.txt provide the data-analysis, SQL, visualization, and Jupyter environment. Python includes the sqlite3 module, so you do not need to install a separate database server.

From the repository root, create and activate a dedicated virtual environment:

python3 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

On Windows PowerShell, activate the environment with:

python -m venv .venv
.venv\Scripts\Activate.ps1
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

Register the environment as a Jupyter kernel:

python -m ipykernel install --user \
  --name sql-data-analysis-practical \
  --display-name "Python (SQL Data Analysis Practical)"

Confirm the main tools:

python --version
python -c "import sqlite3, numpy, pandas, sqlalchemy, matplotlib, seaborn, jupyter, ipykernel, nbformat, nbclient; print('SQLite:', sqlite3.sqlite_version); print('NumPy:', numpy.__version__); print('pandas:', pandas.__version__); print('SQLAlchemy:', sqlalchemy.__version__); print('Matplotlib:', matplotlib.__version__); print('Seaborn:', seaborn.__version__); print('Jupyter, ipykernel, nbformat, and nbclient imported successfully')"

Project Workflow

Run the complete workflow from the repository root:

bash scripts/bash/run-workflow.sh

This command generates data/service-analysis.sqlite, executes the reference queries, checks important totals, and creates the example outputs under results/.

To work step by step, run:

python scripts/python/generate-example-database.py
python scripts/python/run-sql-analysis.py

The Analysis Contract

Before writing SQL, state four things:

Element Decision for this practical
Question How do service volume, completion, waiting time, and satisfaction vary over time and across regions?
Primary grain One row per service record
Time period 1 January–31 December 2025
Main safeguards Preserve row grain, validate joins, reconcile counts, and report missingness

In one sentence, explain why “one row per service record” matters when calculating completion rates.

Step 1: Understand the Relational Structure

The database separates concepts into related tables instead of repeating every descriptive field in every service record.

Table Grain Primary key Purpose
regions One row per region region_id Region names
facilities One row per facility facility_id Facility names and region membership
services One row per service service_id Service names and categories
clients One row per client client_id Home region, age, and client segment
service_records One row per service event record_id Date, status, waiting time, and satisfaction

The central service_records table contains foreign keys that point to descriptive tables. A service record belongs to one client, one facility, and one service.

Inspect the available objects:

SELECT name, type
FROM sqlite_master
WHERE type IN ('table', 'index')
ORDER BY type, name;

Inspect a table definition:

PRAGMA table_info(service_records);

Preview records without assuming their order:

SELECT *
FROM service_records
ORDER BY record_id
LIMIT 10;

LIMIT makes exploration manageable; it does not create a representative sample. Always add an explicit ORDER BY when the selected rows must be reproducible.

Check Table Sizes

SQLite does not support a variable table name inside a standard COUNT(*) query, so use a deliberate union:

SELECT 'clients' AS table_name, COUNT(*) AS row_count FROM clients
UNION ALL
SELECT 'facilities', COUNT(*) FROM facilities
UNION ALL
SELECT 'regions', COUNT(*) FROM regions
UNION ALL
SELECT 'services', COUNT(*) FROM services
UNION ALL
SELECT 'service_records', COUNT(*) FROM service_records
ORDER BY table_name;

Expected anchor: the database contains 720 service records. This becomes a reconciliation total for later queries.

Step 2: Select, Filter, Sort, and Derive

A focused query should return only the columns and rows needed for its purpose.

SELECT
    record_id,
    service_date,
    status,
    wait_minutes,
    satisfaction_score
FROM service_records
WHERE service_date >= '2025-10-01'
  AND status = 'Completed'
ORDER BY wait_minutes DESC, record_id
LIMIT 20;

SQLite stores the example dates in ISO format (YYYY-MM-DD), which allows correct lexical date filtering. Production databases may provide native date types and different date functions.

Use CASE to create an analysis field:

SELECT
    record_id,
    wait_minutes,
    CASE
        WHEN wait_minutes <= 20 THEN 'Short'
        WHEN wait_minutes <= 45 THEN 'Moderate'
        ELSE 'Long'
    END AS wait_band
FROM service_records
ORDER BY record_id;

Handle Missing Values Explicitly

NULL represents absence, not zero. Compare it with IS NULL or IS NOT NULL:

SELECT
    COUNT(*) AS completed_records,
    COUNT(satisfaction_score) AS completed_with_score,
    SUM(satisfaction_score IS NULL) AS completed_without_score
FROM service_records
WHERE status = 'Completed';

COUNT(*) counts rows. COUNT(satisfaction_score) counts non-null values in that column. Their difference is analytically meaningful.

Step 3: Summarize with Aggregate Functions

Start with the overall status distribution:

SELECT
    status,
    COUNT(*) AS service_records,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS percentage
FROM service_records
GROUP BY status
ORDER BY service_records DESC;

The multiplication by 100.0 forces a decimal calculation. Without it, behavior can vary across database engines and column types.

Summarize by month:

SELECT
    substr(service_date, 1, 7) AS service_month,
    COUNT(*) AS service_records,
    SUM(status = 'Completed') AS completed_records,
    ROUND(
        100.0 * SUM(status = 'Completed') / COUNT(*),
        1
    ) AS completion_rate_pct
FROM service_records
GROUP BY substr(service_date, 1, 7)
ORDER BY service_month;

In SQLite, a Boolean expression such as status = 'Completed' evaluates to 1 or 0, making conditional sums concise. A portable alternative is SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Ask What the Denominator Represents

The completion rate above uses all service records in the month as its denominator. A different question might exclude cancelled records:

ROUND(
    100.0 * SUM(status = 'Completed')
    / NULLIF(SUM(status IN ('Completed', 'Pending')), 0),
    1
) AS active_case_completion_rate_pct

Neither denominator is automatically correct. The correct choice follows the operational definition and must be documented.

Write the numerator and denominator for the main completion-rate measure in plain language.

Step 4: Join Tables Without Changing the Question

Join descriptive tables when their fields are needed:

SELECT
    sr.record_id,
    sr.service_date,
    r.region_name,
    f.facility_name,
    s.service_name,
    sr.status,
    sr.wait_minutes,
    sr.satisfaction_score
FROM service_records AS sr
JOIN facilities AS f
  ON sr.facility_id = f.facility_id
JOIN regions AS r
  ON f.region_id = r.region_id
JOIN services AS s
  ON sr.service_id = s.service_id
ORDER BY sr.record_id;

Each foreign key points from many service records to one lookup row. Therefore, this series of many-to-one joins should preserve the service-record grain and return 720 rows.

Validate the Join

WITH joined AS (
    SELECT sr.record_id
    FROM service_records AS sr
    JOIN facilities AS f ON sr.facility_id = f.facility_id
    JOIN regions AS r ON f.region_id = r.region_id
    JOIN services AS s ON sr.service_id = s.service_id
)
SELECT
    COUNT(*) AS joined_rows,
    COUNT(DISTINCT record_id) AS distinct_records
FROM joined;

Both values should equal 720. If joined_rows is larger than distinct_records, at least one join has duplicated the intended grain. If it is smaller, an inner join has removed unmatched records.

Inner Join Versus Left Join

  • Use an INNER JOIN when unmatched rows are outside the defined analysis population.
  • Use a LEFT JOIN when all rows from the primary table must remain visible, including unmatched keys.

To diagnose referential problems, use left joins:

SELECT
    SUM(c.client_id IS NULL) AS unmatched_clients,
    SUM(f.facility_id IS NULL) AS unmatched_facilities,
    SUM(s.service_id IS NULL) AS unmatched_services
FROM service_records AS sr
LEFT JOIN clients AS c ON sr.client_id = c.client_id
LEFT JOIN facilities AS f ON sr.facility_id = f.facility_id
LEFT JOIN services AS s ON sr.service_id = s.service_id;

Step 5: Compare Regional Performance

Build a regional summary while retaining explicit numerators and denominators:

SELECT
    r.region_name,
    COUNT(*) AS service_records,
    SUM(sr.status = 'Completed') AS completed_records,
    ROUND(
        100.0 * SUM(sr.status = 'Completed') / COUNT(*),
        1
    ) AS completion_rate_pct,
    ROUND(AVG(sr.wait_minutes), 1) AS mean_wait_minutes,
    ROUND(AVG(sr.satisfaction_score), 2) AS mean_satisfaction
FROM service_records AS sr
JOIN facilities AS f
  ON sr.facility_id = f.facility_id
JOIN regions AS r
  ON f.region_id = r.region_id
GROUP BY r.region_id, r.region_name
ORDER BY completion_rate_pct DESC, r.region_name;

Notice that AVG(satisfaction_score) excludes null scores. Interpret it as the mean among records with observed satisfaction, not all service records.

Validate the grouped result:

WITH regional AS (
    SELECT r.region_id, COUNT(*) AS service_records
    FROM service_records sr
    JOIN facilities f ON sr.facility_id = f.facility_id
    JOIN regions r ON f.region_id = r.region_id
    GROUP BY r.region_id
)
SELECT SUM(service_records) AS reconciled_records
FROM regional;

The result should reconcile to 720.

Step 6: Organize Logic with a Common Table Expression

A common table expression (CTE) gives a name to an intermediate result, making complex logic easier to inspect.

WITH completed_services AS (
    SELECT
        facility_id,
        service_id,
        wait_minutes,
        satisfaction_score
    FROM service_records
    WHERE status = 'Completed'
),
facility_summary AS (
    SELECT
        facility_id,
        COUNT(*) AS completed_records,
        ROUND(AVG(wait_minutes), 1) AS mean_wait_minutes,
        ROUND(AVG(satisfaction_score), 2) AS mean_satisfaction
    FROM completed_services
    GROUP BY facility_id
)
SELECT
    f.facility_name,
    fs.completed_records,
    fs.mean_wait_minutes,
    fs.mean_satisfaction
FROM facility_summary fs
JOIN facilities f ON fs.facility_id = f.facility_id
ORDER BY fs.completed_records DESC, f.facility_name;

The first CTE defines the population. The second defines the aggregation. The final query adds labels and presentation order. This separation makes each stage easier to test.

Step 7: Rank Within Groups with a Window Function

Window functions calculate across related rows without collapsing them into a single row per group.

WITH service_summary AS (
    SELECT
        r.region_name,
        s.service_name,
        COUNT(*) AS service_records,
        ROUND(AVG(sr.wait_minutes), 1) AS mean_wait_minutes
    FROM service_records sr
    JOIN facilities f ON sr.facility_id = f.facility_id
    JOIN regions r ON f.region_id = r.region_id
    JOIN services s ON sr.service_id = s.service_id
    GROUP BY r.region_name, s.service_name
)
SELECT
    region_name,
    service_name,
    service_records,
    mean_wait_minutes,
    RANK() OVER (
        PARTITION BY region_name
        ORDER BY service_records DESC
    ) AS volume_rank
FROM service_summary
ORDER BY region_name, volume_rank, service_name;

PARTITION BY restarts the ranking in each region. RANK() gives tied values the same rank and may skip the following number. Use DENSE_RANK() if ranks should remain consecutive or ROW_NUMBER() if every row must have a unique sequence.

Step 8: Run Data-Quality Checks

Quality checks should be executable and saved, not left as informal assumptions.

SELECT
    COUNT(*) AS total_records,
    SUM(satisfaction_score IS NULL) AS missing_satisfaction,
    SUM(wait_minutes < 0) AS invalid_wait_times,
    COUNT(*) - COUNT(DISTINCT record_id) AS duplicate_record_ids
FROM service_records;

Check categorical values:

SELECT status, COUNT(*) AS service_records
FROM service_records
GROUP BY status
ORDER BY status;

Check plausible ranges:

SELECT
    MIN(wait_minutes) AS minimum_wait,
    MAX(wait_minutes) AS maximum_wait,
    MIN(satisfaction_score) AS minimum_satisfaction,
    MAX(satisfaction_score) AS maximum_satisfaction
FROM service_records;

Interpret Missing Satisfaction Carefully

In the generated data, pending and cancelled records do not normally have a satisfaction score. Some completed records also have missing scores. Missingness therefore has more than one mechanism and should not be treated automatically as a data-entry error.

SELECT
    status,
    COUNT(*) AS service_records,
    SUM(satisfaction_score IS NULL) AS missing_satisfaction,
    ROUND(100.0 * SUM(satisfaction_score IS NULL) / COUNT(*), 1) AS missing_pct
FROM service_records
GROUP BY status
ORDER BY status;

Step 9: Inspect Query Plans and Indexes

Indexes can reduce the work needed to locate rows, but they also use storage and add write overhead. Inspect the plan for a date-filtered query:

EXPLAIN QUERY PLAN
SELECT record_id, service_date, status
FROM service_records
WHERE service_date >= '2025-10-01'
ORDER BY service_date;

The generated database includes an index on service_date. Query-plan wording is database-specific, so treat the output as engine evidence rather than a universal SQL result.

Do not add indexes only because a column appears in a query. Use representative workloads, query plans, table sizes, and measured performance.

Step 10: Export Reproducible Results

The supplied Python workflow stores SQL in named query strings, reads each result into a pandas DataFrame, and writes CSV files. The core pattern is:

import sqlite3
import pandas as pd

with sqlite3.connect("data/service-analysis.sqlite") as connection:
    result = pd.read_sql_query(
        """
        SELECT status, COUNT(*) AS service_records
        FROM service_records
        GROUP BY status
        ORDER BY service_records DESC;
        """,
        connection,
    )

result.to_csv("results/tables/status-summary.csv", index=False)

The complete workflow creates:

Output Purpose
database-overview.csv Table-level row counts
regional-performance.csv Regional volume, completion, wait, and satisfaction
monthly-completion.csv Monthly completion trend
service-ranking.csv Within-region service ranking
quality-checks.csv Missing, invalid, duplicate, and unmatched counts
monthly-completion-rate.png Communication-ready trend figure
run-manifest.json Database checksum, query list, and validation status

Why Keep a Manifest?

The manifest records which database was analysed, its SHA-256 checksum, which named queries ran, and whether validation passed. It does not prove that every analytical decision is correct, but it improves traceability.

Step 11: Validate Before Interpreting

Use a compact validation checklist:

Check Expected result
Base service-record count 720
Joined row count 720
Distinct joined record IDs 720
Sum of regional record counts 720
Sum of monthly record counts 720
Duplicate primary keys 0
Unmatched foreign keys 0
Negative waiting times 0

The workflow enforces these anchors with assertions. If an assertion fails, stop and investigate before using the outputs.

Common Failure Patterns

Symptom Likely cause First check
Joined rows exceed 720 Many-to-many or non-unique lookup join Count matches per key
Joined rows fall below 720 Unmatched keys removed by inner join Repeat with left joins
Rates are 0 or 100 unexpectedly Integer division or wrong denominator Inspect numerator and denominator
Mean satisfaction looks too high Null or status-dependent selection Report observed-score count
Monthly totals do not reconcile Date filter or grouping expression Compare min/max dates and totals
Results change between runs Uncontrolled input or missing order Check checksum, seed, and ORDER BY

Step 12: Write Defensible Findings

A result states what the query returned. An interpretation explains what the result may mean. A recommendation proposes an action. Keep them separate.

Result: In the synthetic 2025 database, [region/month/service] had [metric and value], compared with [reference value]. The result is based on [numerator/denominator or observed count].

Interpretation: This pattern is consistent with [limited operational explanation], but the descriptive analysis does not establish causation.

Data-quality note: [State missingness, exclusions, join validation, or measurement limitation relevant to the result.]

Next step: Review [specific process or additional data] before deciding whether an intervention is needed.

Avoid causal language such as “the region caused lower satisfaction.” The database supports descriptive comparisons, not randomized causal inference. Differences could reflect service mix, client mix, staffing, facility conditions, measurement, or chance.

Your Completion Task

Using the generated outputs, write three concise findings:

  1. one about regional completion;
  2. one about the monthly pattern; and
  3. one about waiting time or satisfaction.

For each finding, include the supporting value, denominator or record count, one limitation, and one reasonable next check.

Practical Completion Checklist

Key Takeaways

  1. Define the question and row grain before writing a complex query.
  2. Treat joins as analytical transformations that must be validated.
  3. Preserve numerators, denominators, counts, and missingness beside rates and averages.
  4. Use CTEs to make multi-stage logic inspectable.
  5. Use window functions when you need within-group context without collapsing rows.
  6. Reconcile every important grouped output to a trusted base total.
  7. Save queries, outputs, validation evidence, and a database checksum together.
  8. Report what the data show without claiming what the design cannot establish.

Extension Challenges

After completing the core practical, try one or more extensions:

  • calculate a three-month moving average of completion rate;
  • compare client home region with facility region;
  • identify facilities above their regional mean waiting time;
  • calculate the percentage of completed records with an observed satisfaction score;
  • parameterize a start and end date in Python instead of editing SQL text; or
  • translate selected SQLite queries to PostgreSQL or DuckDB and document syntax differences.

Further Reading

  • SQLite documentation: SQL language, query planner, and Python interface
  • PostgreSQL documentation: queries, joins, aggregate functions, and window functions
  • Python documentation: sqlite3 database interface
  • pandas documentation: read_sql_query

Use official documentation for the database engine running your production workload. SQL concepts transfer broadly, but functions, date handling, Boolean behavior, query plans, and optimization details vary by engine.

Practical complete: You have moved from raw relational tables to validated SQL outputs and evidence-aligned interpretation using a fully reproducible workflow.

© Complex Data Insights | CDI Practicals

Hands-on exercises and step-by-step tools