SQL Data Analysis Practical
A hands-on exercise for querying, joining, summarizing, validating, and interpreting structured data with SQL
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
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:
- inspect relational tables, columns, keys, and relationships;
- define the grain of a query before interpreting its output;
- filter, derive, aggregate, and sort fields with SQL;
- join tables while checking cardinality and row counts;
- use common table expressions and window functions;
- identify missing, invalid, duplicate, and unmatched records;
- validate totals and exported results; and
- distinguish findings from explanations and recommendations.
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.txtOn 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.txtRegister 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.shThis 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.pyThe 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;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_pctNeither 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 JOINwhen unmatched rows are outside the defined analysis population. - Use a
LEFT JOINwhen 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.
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:
- one about regional completion;
- one about the monthly pattern; and
- 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
- Define the question and row grain before writing a complex query.
- Treat joins as analytical transformations that must be validated.
- Preserve numerators, denominators, counts, and missingness beside rates and averages.
- Use CTEs to make multi-stage logic inspectable.
- Use window functions when you need within-group context without collapsing rows.
- Reconcile every important grouped output to a trusted base total.
- Save queries, outputs, validation evidence, and a database checksum together.
- 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:
sqlite3database 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.