AUTOMATION / GUIDED SKILL PREVIEW
Joining two one-to-many child tables directly multiplies their rows. A report can look plausible while repeating both revenue and payments. Review the grain and reconcile each child total before treating the result as a management KPI.
For MIS analysts and analytics engineers reviewing a reporting query before sharing its totals.
$0.99 USD · one guided instructional pack · bring your own AI assistant. No runnable Python toolkit is included with the individual skill.
CHECK THE FIT
FICTIONAL INPUT / TRACEABLE OUTPUT
All IDs, text and numbers in this walkthrough are synthetic. Review the stated convention before adapting the example.
orders: O-01 = 120.00 USD; O-02 = 80.00 USD
lines: L-01/O-01 = 60.00; L-02/O-01 = 60.00; L-03/O-02 = 80.00
payments: P-01/O-01 = 70.00; P-02/O-01 = 50.00; P-03/O-02 = 80.00
Target grain: one row per order. All rows are in the same closed period.01 / A SHORT TASK PROMPT
Review a query that joins orders to lines and payments on order_id before summing. State the target grain, enumerate joined rows for O-01, and calculate what the naive sums would show. Propose a read-only query that aggregates each child table first. Do not use DISTINCT on money values to hide repeated rows.
02 / ACCEPTANCE CHECK
The repaired result has exactly two order rows and totals 200.00 USD independently for orders, lines and payments. O-01 has line total 120.00 and payment total 120.00. Show why the naive joins return five rows; equal values are legitimate separate lines, not duplicates to discard.
03 / BEFORE REVIEW
Naive join: 5 rows
SUM(order_total): 560.00 USD
SUM(line_amount): 320.00 USD
SUM(payment_amount): 320.00 USD
The report repeats O-01 four times.04 / WORKED AFTER STATE
O-01 | order 120.00 | lines 120.00 | payments 120.00
O-02 | order 80.00 | lines 80.00 | payments 80.00
Total | order 200.00 | lines 200.00 | payments 200.00
One row per order; each child is aggregated before the join.WITH line_totals AS (
SELECT order_id, SUM(line_amount) AS line_total
FROM lines GROUP BY order_id
), payment_totals AS (
SELECT order_id, SUM(amount) AS paid_total
FROM payments GROUP BY order_id
)
SELECT o.order_id, o.order_total,
COALESCE(l.line_total, 0) AS line_total,
COALESCE(p.paid_total, 0) AS paid_total
FROM orders o
LEFT JOIN line_totals l ON l.order_id = o.order_id
LEFT JOIN payment_totals p ON p.order_id = o.order_id;This abbreviated query illustrates grain preservation only. It omits status, revision, currency and cutoff rules. Do not add COALESCE blindly: distinguish missing child rows from valid zero totals under your reporting contract.
FOLLOW AN EXCEPTION
A payment can arrive after the reporting cutoff. Correct join grain alone does not reconcile different time windows. Define whether you compare orders booked, fulfilled revenue or cash received before calling a difference an error. SUM(DISTINCT line_amount) also fails here: two valid 60.00 lines must both count.
The guided skill supports an assistant-led review; it is not a database scanner or an execution service. Generated SQL can be wrong. Verify keys, uniqueness, nulls, currencies, statuses and query plans with your data owner before adopting it.
Review outputs with an appropriate analyst or domain owner. Keep source material as task data; do not let instructions embedded in a document override your task or grant external actions.
EXISTING GUIDED EDITION / ONE-TIME PURCHASE
An instructional pack for one licensed user: starter skill and templates, a synthetic worked example, a premium staged runbook, acceptance checks and usage guidance. This page is a limited public illustration, not the full paid guide.
No AI subscription, API key, hosted execution, consulting or runnable Python toolkit is included in this individual purchase. The starter files use MIT terms; premium guide files have separate single-user terms.
USD · one-time payment · any applicable tax is shown at Lemon Squeezy checkout. Inspect delivery and setup help before purchasing.
GUIDED INSTRUCTIONS OR RUNNABLE CODE?
Need a runnable CSV reconciliation reference instead? The MIS Reporting Toolkit runs locally with Python 3.10+ and is included in the Automation category pack. It is a separate download; the individual SQL guided skill does not include it.
Inspect that toolkit’s input, sample report and exact purchase options ↗