Deliver a checked SQL analysis and evidence note
Learn the reasoning. Try the task. Compare your answer.
Prepared for DataCareerHub · Curriculum version 2026-10-15.3
By the end of this lesson
- Combine safe joins and monthly metrics in one readable analytical pipeline.
- Use exception checks and independent answers to review a result.
- Produce a reproducible handover without unsupported business claims.
SQLite syntax · Read-only exercises run on your local practice copy · Monetary inputs are integer cents, converted to EUR in the query outputs.
Understand the method
Your final task is to deliver a small piece of analytical work that another person can reproduce. It needs more than a query that runs. State the question, source version, engine, reporting coverage, metric population and units. Keep the script readable and preserve the data used to check its answers.
Define the final output
Q14 produces one row per covered month with completed-order count, sales EUR, AOV EUR and a count of missing or mismatched item totals. It first aggregates item records to order grain, then LEFT JOINs them to completed orders. The join does not multiply order values. The exception count tests whether the associated item total agrees with the order amount.
The quality count is diagnostic. Do not subtract it from sales or use a hidden global PASS to redefine the business population. If it is nonzero, investigate and explain which affected orders need review. This fixture has no item mismatches, so both monthly issue counts are zero. That narrow result does not certify every field or source-system control.
A useful review sequence
- Check 12 verified orders, 24 item rows, three unique channel keys and 13 raw source rows.
- Confirm the completed-order population is eight orders and €1,100.
- Demonstrate the raw duplicate and the join-fanout trap, so you can recognise their effect.
- Reconcile item totals at order grain and channel totals to monthly sales.
- Check that prior-period NULLs and zero-activity channels remain intentional and explained.
- Run Q14 and compare its named output columns with expected_results.json.
The expected-results file is a reference for this exact fixture, not a general grading service. Compare column meaning and values rather than relying on a screenshot or the fact that a row count matches. ORDER BY clauses make the answer ordering explicit. Monetary values are stored as cents; euro outputs use real-valued division. In larger systems, decimal precision and rounding policies need their own design.
Test a change on a disposable copy
As an independent extension, change F001 from 10000 to 15000 cents in a disposable local copy while leaving its item rows unchanged. Predict the outcome: February completed sales become €550 and AOV €137.50, and one item mismatch appears. Do not make this edit to the reference database you submit. Restore or reopen a clean copy afterwards. Your normal exercise scripts remain read-only SELECT statements.
Write a proportionate conclusion
Completed sales rose €100, or 20%, between the two covered months. Completed-order count stayed at four per month while AOV rose €25. Web and store each contributed €50 to the increase. These are descriptive arithmetic findings from a fictional sample. They do not establish why customers purchased, campaign effectiveness, profit or future sales.
A useful next step is to confirm source coverage and gather the extra evidence needed for the actual business decision. Keep the decision brief separate from the observed result. If you use this module in a portfolio, identify it as a guided fictional training case, explain your own checks and do not present the shop as a real client.
What to hand over
Keep your completed read-only query script, a short schema/quality note and a 120–180 word evidence note together. Include the exact fixture and engine version you used. The public site does not need your files, credentials or a payment. You can continue studying without registration; existing signed-in users can separately record lesson completion.
Practical exercise
Complete Q14 and write your evidence note. Submit locally a query script covering Q1–Q14, the schema and quality notes, and a test log for the disposable F001 change. Check the expected answers only after trying each task. Use the rubric below to identify weak areas rather than claiming an automatically verified grade.
Final deliverables: one reproducible SQL analysis package and one clear statement of its limitations. No upload, live-database connection or payment is required.
Open the worked solution
WITH item_totals AS (
SELECT order_id, SUM(quantity * unit_price_cents) AS item_total_cents
FROM order_items GROUP BY order_id
), completed AS (
SELECT o.order_id, substr(o.order_date, 1, 7) AS month,
o.channel, o.order_value_cents, t.item_total_cents
FROM orders AS o
LEFT JOIN item_totals AS t ON t.order_id = o.order_id
WHERE o.status = 'completed'
)
SELECT month, COUNT(*) AS completed_orders,
SUM(order_value_cents) / 100.0 AS sales_eur,
SUM(order_value_cents) / (100.0 * COUNT(*)) AS aov_eur,
SUM(CASE WHEN item_total_cents IS NULL
OR item_total_cents <> order_value_cents THEN 1 ELSE 0 END) AS item_issues
FROM completed GROUP BY month ORDER BY month;The final rows are February: 4 orders, €500 sales, €125 AOV, 0 item issues; March: 4, €600, €150, 0. On the disposable F001 edit, February changes to 4 orders, €550 sales, €137.50 AOV and 1 issue. This demonstrates that the diagnostic observes a mismatch without secretly changing the sales population.
Example evidence note
I queried the fictional Northstar SQLite practice dataset at verified order grain for full February and March 2026, including completed orders only. Sales rose from €500 to €600, an increase of €100 or 20%, while completed-order count stayed at four. AOV increased from €125 to €150. Web and store each contributed €50 to the change. I aggregated item records to order grain before joining, and the reference data produced no order/item reconciliation exceptions. The raw-source duplicate and defective fields were examined separately rather than silently altering the verified analysis. The partner channel correctly appears with zero activity under the case's complete-coverage assumption. These results describe a small teaching sample; they cannot establish marketing causation, profit or a forecast. Before any budget decision, I would confirm extraction coverage and scope a separate evaluation with campaign exposure, spend and an appropriate comparison design.
Self-review rubric: 10 points
- Schema and population, 0–2: correct grain, status, dates and units.
- Core queries, 0–2: correct filters, aggregates, denominators and NULL handling.
- Joins and reconciliation, 0–2: controlled cardinality and meaningful exception checks.
- Comparisons and reproducibility, 0–2: ordered windows, saved scripts, fixture/version and test log.
- Communication, 0–2: accurate findings, limitations and proportionate next step.
A suggested target is 8/10, with no zero for core queries or joins and no unsupported causal claim. Rework a weak area and record what you corrected. This is a self-assessment for learning, not an accredited qualification or an automatic skills certification.
One more question.
SQL references: SELECT and joins, aggregate functions, window functions.
How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io
Read and practice without an account. Sign in with an existing account to save lesson completion.
Continue to Module 04 →Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.