10 Accenture Data Analyst Interview Questions & Answers

I would start with the business decision, not the dashboard tool. I would ask what executive decision the dashboard should support, who will use it, and what action they should take. Then I would define exactly what counts as a delay, including the eligible population, numerator, denominator, on-time rule, and exclusions. Next I would agree on the grain—order, shipment, lane, or day—the reporting window, time zone, and refresh cadence. Finally, I would check completeness, missing values, duplicates, outliers, freshness, and reconciled totals before building the dashboard.

The main problem is that “fewer delays” is not yet a measurable dashboard requirement. Before building anything, I need to understand what decision executives will make and turn “delay” into a precise KPI. I would define the eligible population, numerator, denominator, on-time rule, and exclusions. I would also agree on the analytical grain, reporting window, time zone, and refresh cadence. Finally, I would validate completeness, missing values, duplicates, outliers, freshness, and reconciled totals so every dashboard view uses one consistent, trusted definition.

1. What executive decision should this dashboard support?
2. Who is the audience, and what action should they take from the dashboard?
3. What exactly counts as a “delay,” such as arrival after an agreed delivery date or outside an agreed service tolerance?
4. What is the eligible population for the KPI?
5. What belongs in the numerator: delayed eligible units at the agreed grain?
6. What belongs in the denominator: all eligible units from the same population and reporting window?
7. What counts as “on time”?
8. What should be excluded, such as cancellations, returns, or other agreed exceptions?
9. What should the analytical grain be: order, shipment, lane, or day?
10. If an order contains multiple lines or shipments, how should it be counted so the KPI does not mix grains or double-count business events?
11. What reporting window should the dashboard cover?
12. Which time zone defines the reporting boundary and delay calculation?
13. How current must the data be, and what refresh cadence is required: near real time, daily, weekly, or another agreed interval?
14. Is the data complete for the selected window, and are missing values, duplicates, outliers, or stale records present?
15. Can control totals be reconciled across relevant levels, such as order and shipment counts, before the KPI is published?

I would organize the analysis into the same five-step flow shown in the diagram.

First, I would clarify the business decision and audience. The dashboard should support a specific executive decision rather than simply display supply-chain data. I would ask who owns the decision, who will use the dashboard, and what action they are expected to take from the insight.

Second, I would define the KPI before charting it. “Fewer delays” is ambiguous until the business defines what counts as delayed and on time. I would identify the eligible population, the numerator of delayed eligible units, the denominator of total eligible units, and any exclusions. If the chosen KPI is a delay rate, its conceptual definition is delayed eligible units divided by total eligible units for the same population, grain, and reporting window.

Third, I would establish the data grain and time window. Grain means the level represented by one agreed analytical unit. The client must choose whether the dashboard measures orders, shipments, lanes, or days. This matters because an order can contain multiple shipments, so mixing levels can create double counting or misleading rates. I would also define the reporting window, the time zone used for date boundaries, and how often the data must refresh.

Fourth, I would perform data checks and validation. I would confirm that the selected reporting window is complete, check missing values, duplicates, and outliers, verify data freshness and update cadence, and reconcile totals across relevant levels before analysis. These checks protect both the numerator and denominator from incomplete or duplicated records.

Fifth, I would document the agreed dashboard scope. That definition should include the decision owner and audience, the exact delay KPI, the chosen grain, refresh cadence, reporting window and time zone, filters and exclusions, and the validation checks required before publication.

The key Finding Logic is that “fewer delays” cannot be interpreted reliably until the business fixes the metric definition, eligible population, grain, denominator, window, and exclusions. Different valid definitions can produce different results, so those choices must be explicit rather than assumed. Once the definitions and data checks are stable, trend analysis or drill-down views can be added without changing the meaning of the executive KPI.

1. Clarify the executive decision, decision owner, audience, and expected action.
2. Define what counts as delayed and on time for an agreed eligible population.
3. Define the KPI numerator, denominator, and exclusions using the same population and reporting window.
4. Choose one consistent analytical grain: order, shipment, lane, or day, and define how multi-line or multi-shipment cases are counted.
5. Agree on the reporting window, time zone, and refresh cadence.
6. Validate completeness, missing values, duplicates, outliers, freshness, and reconciled totals.
7. Document the final dashboard scope before interpreting trends or publishing the KPI.

There is no meaningful algorithmic time or space complexity because this is a requirements and measurement-design task rather than a coding problem. The main tradeoff is detail versus simplicity: a finer grain supports better drill-down and diagnosis but requires more careful counting and data-quality controls, while a coarser executive view is easier to read but can hide important differences. A faster refresh cadence also creates more operational and validation work, especially when source systems update at different times.

Common mistakes are starting with charts or tools before clarifying the decision; treating “delay” as self-explanatory; defining a numerator without an eligible denominator; mixing order and shipment grain; counting multi-shipment orders inconsistently; ignoring exclusions; comparing periods that use different definitions; omitting the reporting window or time zone; choosing a refresh cadence without checking data freshness; and publishing the KPI before checking missing records, duplicates, outliers, and reconciliation totals.

Present the answer in this order: business decision and audience, KPI definition, data grain and time window, data checks, then agreed dashboard scope. Explicitly say that you would define the metric before charting it and use one agreed grain throughout the analysis.

I would first make the decision process explicit: both directors provide requirements and concerns, but one named metric owner has governance authority and is accountable for the final definition. I would document on-time delivery as a metric contract covering the business meaning, exact numerator and denominator, qualifying delivery event, due-date rule, time zone and calendar, exclusions, assumptions, grain, source of truth, lineage, owner or steward, effective date, and validation rules. Any change creates a new version, goes through impact review and named approval, and leaves an audit trail. Only the approved version is published to reports. Then I would run recurring reconciliation, data-quality, lineage, and exception checks, and route any needed definition change back through the same approval process rather than editing it directly.

The task is to turn a disagreement about on-time delivery into one governed metric that everyone uses the same way. I would give one metric owner clear decision authority, bring both directors together to agree the business meaning, and document the definition as a metric contract. That contract should state the calculation, qualifying delivery event, due-date rule, time zone, calendar, exclusions, assumptions, grain, source of truth, lineage, owner, effective date, and validation rules. Then I would control every change through versioning, approval, an audit trail, publication of only the active version, and recurring validation.

1. Who has final decision authority for this metric if the directors cannot agree?
2. What business event counts as delivery, and what event or commitment defines the due date?
3. What population belongs in the denominator, and which exclusions are legitimate?
4. Which source system is authoritative for the delivery event and due-date information?
5. What row grain should the metric use, and what time zone and business calendar apply?
6. Which reports, dashboards, decisions, or downstream users would be affected by a definition change?
7. Who must approve a new metric version, and how should effective dates and prior versions be retained?
8. What recurring validation cadence and alerting process already exists, if any?

I would use one end-to-end governance flow.

1. Resolve the disagreement and assign ownership. I would facilitate a working session with both directors and the relevant data or operational stakeholders. The goal is not to average two definitions. It is to expose the decision points: what event counts as delivered, what commitment date is compared against it, which records are in scope, how boundary dates are handled, and which time zone or calendar applies. Both directors contribute requirements and risks, but one named metric owner must have final governance authority. That avoids a permanent two-definition compromise and creates accountability.

2. Write one canonical metric contract. The agreed definition becomes the single source of truth in governed documentation. I would record the business meaning; exact numerator and denominator; qualifying delivery event and due-date rule; time zone and calendar; exclusions and assumptions; row grain; source of truth; data lineage; owner or steward; effective date; and validation rules. I would also record known data limits. This makes the definition reproducible instead of dependent on memory or a dashboard author's interpretation.

3. Make calculation and boundary rules explicit. I would not invent the actual on-time-delivery formula before the stakeholders agree it. Once agreed, I would state exactly which records can enter the numerator and denominator and how null dates, duplicates, late-arriving records, cancelled or excluded records, and boundary timestamps are treated. If a record violates a documented rule, it should not be silently dropped, overwritten, imputed, or repaired. It should follow an auditable exception or quarantine rule with a recorded reason.

4. Version every definition change. Direct edits to the active definition should be prohibited. A proposed change creates a new version. The change request should state the reason, affected logic, effective date, impacted reports or users, data implications, and migration need. Prior versions remain available so historical results can be explained and reproduced.

5. Require impact review and named approval. Before activation, I would assess which dashboards, reports, data products, decisions, and downstream calculations use the metric. The named approver or approval group then accepts or rejects the version. The audit trail should show what changed, who approved it, and when it became effective. Only an approved version becomes active.

6. Publish only the approved version to reporting. Dashboards, reports, and data products should use the same governed definition rather than recreating local logic. If different business contexts genuinely require different metrics, they should receive different names and contracts instead of silently changing the meaning of "on-time delivery."

7. Validate the metric from source to report. Recurring validation checks whether the published metric still behaves as documented. I would reconcile source and report row counts or control totals where meaningful, profile nulls, duplicates, ranges, and unexpected categories, validate keys and referential integrity where applicable, and compare the output with the authoritative source. Reconciliation means confirming that records and key totals carried through the transformation agree with the source within the documented boundary.

8. Use data-quality dimensions as diagnostic checks. Completeness asks whether required records or fields are present. Accuracy asks whether values reflect the authoritative source. Consistency asks whether the same rule produces the same meaning across reports. Validity asks whether values satisfy allowed formats and business rules. Uniqueness checks that records expected to be unique are not duplicated. Timeliness checks whether data arrives within the agreed reporting window. These checks support the governed metric; they do not redefine it.

9. Trace failures through lineage. If the metric changes unexpectedly, I would trace the lineage from the report back through transformations to the source system. That helps separate a source defect from a transformation defect or an unauthorized definition change. Schema changes, rule changes, broken joins, or altered source fields should trigger investigation rather than silent adaptation.

10. Handle exceptions and downstream impact. Failed quality rules should create an exception record or report with the reason, owner, and affected metric or report. I would assess whether the issue could change a business decision. If the metric cannot be trusted, the safe response is to flag or pause the affected output rather than publish a number that looks authoritative.

11. Prevent definition drift. I would monitor lineage, schema changes, rule changes, recurring quality results, and metric usage. If validation shows that the definition itself must change, the request loops back to versioning, impact review, and approval. No one edits the active metric directly. This preserves one accountable owner, one canonical definition, one approved active version, and a controlled feedback loop.

The main tradeoff is governance speed versus stability. Strong change control adds review work, but it prevents silent metric drift, conflicting executive reports, and unexplained historical changes. The process should therefore be strict enough to protect decision quality while keeping the approval path clear and practical.

1. Name one metric owner with final governance authority.
2. Facilitate agreement on the business event, due-date rule, scope, grain, source of truth, time zone, calendar, exclusions, and denominator.
3. Document the agreed metric contract, lineage, ownership, effective date, assumptions, and validation rules.
4. Require each proposed change to create a new version.
5. Perform impact review across reports, users, data products, and downstream decisions.
6. Obtain named approval and retain an audit trail and prior versions.
7. Publish only the approved active version to reporting.
8. Run recurring source-to-report reconciliation, data profiling, key and integrity checks, and lineage monitoring.
9. Record and investigate exceptions and assess downstream decision impact.
10. Route any needed definition change back through versioning and approval instead of direct editing.

This is mainly a governance and validation cost rather than a coding-complexity problem. The upfront work is stakeholder alignment, documentation, lineage review, and impact analysis. Recurring cost comes from quality checks, reconciliation, exception investigation, and approval of changes. More control adds process overhead, but it reduces the larger risk of inconsistent reports, silent definition drift, and historical results that cannot be explained.

Common mistakes are letting both directors keep separate definitions under the same metric name; choosing a formula before resolving the business meaning and denominator; leaving ownership ambiguous; documenting only the formula but not grain, source, time zone, exclusions, lineage, or effective date; overwriting a definition without version history; allowing dashboard authors to make local edits; approving a change without checking downstream impact; validating only the final number instead of reconciling source-to-report data; silently dropping or repairing bad records; and treating a schema or lineage change as harmless without investigation.

Frame the answer as a governance loop: one accountable owner, one canonical metric contract, versioned and approved changes, one active published version, recurring source-to-report validation, and a feedback path that sends any needed change back through approval. That shows both stakeholder judgment and data-quality discipline.

I would first make the legacy and modern reports directly comparable: same reporting period, currency, entity scope, grain, filters, business rules, and agreed source of truth. Then I would reconcile row counts and revenue control totals and segment the 6% gap by date, business unit, product, region, and channel to find where it appears. I would profile nulls, duplicates, invalid ranges, categories, keys, referential integrity, and schema drift, quarantining bad records instead of silently dropping them. Next I would trace the affected metric through lineage and test logic, data-quality, and timing causes such as joins, filters, aggregation, late-arriving data, refresh cutoffs, and time zones. I would give stakeholders frequent evidence-based updates, hold or clearly caveat publication if the result is not safe, and finish with the exact root cause, affected scope, corrective action, reconciliation evidence, owner, and prevention checks.

The task is to explain why the modern revenue dashboard is 6% below the legacy report and make a safe reporting decision within 48 hours. I would not assume either report is correct. I would first make both reports comparable, then localize the difference, test data-quality problems, trace the metric through its lineage, and separate logic defects from timing differences. Every conclusion should be supported by reconciliation evidence. Invalid records should be quarantined with reasons rather than silently removed, and stakeholders should receive clear updates about impact, uncertainty, publication status, and next steps.

1. What system or report is the agreed source of truth during the cutover, and is the legacy report authoritative or only a comparison baseline?
2. Are both reports expected to use the same revenue definition, reporting period, currency, entity scope, row grain, filters, and business rules?
3. What are the refresh schedules, cutoff times, time zones, and rules for late-arriving or backfilled data in each system?
4. Can I access source-to-report lineage, transformation logic, schema-change history, quality-rule results, and row-level exception data?
5. Which business segments and downstream decisions are most sensitive to the discrepancy, and who owns the publication decision?

I would structure the investigation as a five-step validation plan and keep an audit trail of every check, assumption, exception, and decision.

1. Set the reconciliation boundary. Before comparing numbers, I would confirm that the two outputs represent equivalent populations and measures. That means matching the reporting period, currency, entity scope, row grain, filters, and revenue business rules, and identifying the agreed source of truth. If these boundaries differ, part or all of the 6% variance could come from a comparison mismatch rather than a defect. I would record any unresolved assumption because it limits how strongly I can interpret later comparisons.

2. Reconcile and segment the 6% gap. Reconciliation means comparing equivalent counts and control totals from source to report. I would reproduce both revenue totals using the same boundary, compare row counts before and after major transformations, and reconcile revenue at progressively finer levels. I would segment the variance by dimensions supported by the data, such as date, business unit, product, region, and channel. The purpose is to identify where the difference first becomes concentrated. A variance isolated to one period or segment provides a much smaller search area than the dashboard-wide 6% difference. I would not infer a cause from the aggregate variance alone.

3. Run data-quality checks. Data quality asks whether the records are fit for the reporting decision. Completeness asks whether expected records or required values are missing. Accuracy asks whether values correctly represent the intended source facts. Consistency asks whether the same concept is represented compatibly across systems and transformations. Validity asks whether values conform to defined formats, domains, ranges, and rules. Uniqueness asks whether records that should appear once are duplicated. Timeliness asks whether required data arrived and was processed within the expected reporting window.

I would profile nulls, duplicates, valid ranges, unexpected categories, key validity, referential integrity, and schema drift such as changed column types or mappings. I would compare row counts across transformation stages to identify where records appear, disappear, or multiply. Invalid, missing, or orphaned records that matter to the investigation would be recorded in an exception report or quarantine with a reason and count. I would not silently delete, overwrite, or impute them because doing so would hide evidence and weaken the audit trail.

4. Trace lineage and test the three cause families. Data lineage is the path from the dashboard metric back through transformations to its source fields. I would trace the revenue metric at column and transformation level and identify the first point where the modern path diverges from the validated comparison.

For a logic cause, I would compare formulas, joins, filters, deduplication rules, mappings, and aggregation logic. A changed join type, filter condition, grain, or aggregation can change revenue even when the underlying source records are correct.

For a data-quality cause, I would look for missing, duplicated, invalid, orphaned, or incorrectly mapped records and determine whether their affected totals explain part of the discrepancy. I would distinguish defects already present in the source from defects introduced during ingestion or transformation.

For a timing cause, I would compare load watermarks, refresh cutoffs, processing windows, late-arriving records, backfills, and time-zone treatment. If one report includes transactions that the other has not yet loaded, the reports may temporarily disagree even when both pipelines are logically correct.

The three cause families can coexist, so I would quantify only what the available reconciliation evidence supports rather than forcing the full 6% into one category.

5. Decide, communicate, and document. I would provide stakeholder updates throughout the 48-hour window that state what has been checked, where the variance is concentrated, what is confirmed, what remains uncertain, which decisions may be affected, and when the next update will arrive. I would clearly separate observations from hypotheses.

For interim publication, I would use a safe-failure approach. If the discrepancy remains material or unexplained for the intended decision, I would recommend holding the modern dashboard. If enough of the output has been reconciled for a clearly bounded use case, I could recommend publishing that view with an explicit caveat describing the affected scope and known limitation. The decision should depend on reconciliation evidence and downstream impact, not simply on the 48-hour deadline.

The final root-cause narrative should state the exact fault or combination of faults, where it occurred in the lineage, why it changed the reported revenue, which periods or segments were affected, what corrective action was taken, and what reconciliation evidence confirms recovery. It should also document assumptions, known limits, and ownership so the conclusion is no stronger than the evidence.

Finally, I would close the loop with prevention and monitoring. I would automate appropriate source-to-report reconciliation checks, row-count controls, data-quality rules, schema-change checks, and lineage monitoring; define alerts and owners; retain exception and recovery evidence; and add regression checks for the failure mode found. That turns the incident into an auditable control rather than a one-time manual correction.

1. Freeze the reconciliation boundary: reporting period, currency, entity scope, row grain, filters, business rules, and source of truth.
2. Reproduce both revenue totals and reconcile row counts and source-to-report control totals.
3. Segment the 6% variance by supported dimensions such as date, business unit, product, region, and channel to localize the difference.
4. Profile nulls, duplicates, ranges, categories, keys, referential integrity, and schema drift; record and quarantine invalid records with reasons.
5. Trace the affected metric through column-level lineage and transformation stages.
6. Test logic causes including formulas, joins, filters, deduplication, mappings, and aggregation.
7. Test data-quality causes by identifying missing, duplicate, invalid, orphaned, or incorrectly mapped records and distinguishing source defects from transformation defects.
8. Test timing causes including watermarks, late-arriving data, refresh cutoffs, backfills, and time-zone differences.
9. Keep stakeholders updated with confirmed findings, uncertainty, affected scope, and the next update time.
10. Decide whether to hold the dashboard or publish a bounded interim view with a clear caveat based on reconciliation evidence and downstream impact.
11. Produce a root-cause narrative with fault, affected scope, corrective action, reconciliation evidence, assumptions, and ownership.
12. Add automated reconciliation, data-quality rules, lineage monitoring, alerts, regression checks, and an audit trail.

The main cost is investigation breadth rather than algorithmic complexity. A full row-by-row comparison across a large dataset can be expensive, so I would start with control totals and row counts, then segment the variance to reduce the search space before drilling into individual records. Data profiling and lineage review add compute and analyst time, but they provide stronger evidence than checking only dashboard totals. The 48-hour constraint favors prioritized and parallel checks, while the publication decision trades speed against the risk of exposing decision makers to an unexplained number.

Common mistakes are treating the legacy report as automatically correct; comparing different periods, grains, currencies, filters, or business definitions; investigating only the aggregate 6% instead of segmenting it; checking dashboard logic without checking source data and timing; ignoring row-count changes across transformations; silently dropping or repairing invalid records; failing to distinguish source defects from transformation defects; assuming late-arriving data is a logic bug; publishing an unresolved material discrepancy without a clear caveat; giving stakeholders hypotheses as facts; and correcting the incident without adding regression checks, alerts, ownership, and an audit trail.

Present the investigation as a narrowing funnel: make the reports comparable, localize the 6% gap, test data quality, trace lineage to separate logic, data, and timing causes, then make a safe publication decision. Emphasize evidence, stakeholder communication, and prevention rather than guessing the root cause.

I would first reduce deliveries to one row per ship_id using MIN(delivered_date), because a shipment can have multiple delivery scans. Then I would LEFT JOIN that result to shipments so shipments with no delivery scan still remain in the denominator. I would group by the month of shipped_date and ship_from_warehouse, count total shipments, use a CASE expression to count shipments whose earliest delivery date is on or before promised_date, and divide on-time shipments by total shipments. I would also verify that the joined row count still matches the shipment count.

The key problem is the data grain. The shipments table represents shipments, while deliveries can contain several rows for the same shipment because of multiple scans. If we join those tables directly, one shipment may appear several times and inflate both counts. The safe approach is to first collapse deliveries to one row per ship_id using the earliest delivered_date. Then join that one-row-per-shipment result back to shipments, classify each shipment as on time or not, and finally aggregate by shipped month and warehouse. Undelivered shipments stay in total shipments but are not counted as on time.

1. Should the reporting month be based on shipped_date, delivered_date, or promised_date? The approved diagram uses shipped_date.
2. Should shipments with no delivery scan remain in total shipments and count as not on time? The approved diagram uses that rule.
3. Are shipped_date, promised_date, and delivered_date DATE values or timestamps, and if timestamps are used, which reporting time zone should define the month and date comparison?
4. Can promised_date be NULL, and if so, should those shipments be treated as not on time, excluded, or reported separately?
5. Is shipments.ship_id guaranteed to be unique, as represented in the diagram, or should duplicate shipment records be handled separately?

Start with the join grain before writing SQL. The target intermediate grain is one row per shipment. The shipments table is represented as one row per ship_id, while deliveries can contain multiple scans for one ship_id. A direct one-to-many join would multiply shipment rows, so first aggregate deliveries with MIN(delivered_date) GROUP BY ship_id. That produces the earliest delivery scan and at most one delivery row per shipment.

Next, LEFT JOIN that result to shipments. The LEFT JOIN is important because shipments with no delivery scan must remain in the denominator. Those rows receive a NULL delivered_date. They still count toward total_shipments but do not satisfy the on-time condition.

The output grain is one row per shipped month and warehouse. Following the diagram, derive month from s.shipped_date with DATE_TRUNC('month', s.shipped_date) and use s.ship_from_warehouse as warehouse. Because deliveries has already been reduced to one row per ship_id, COUNT() counts shipment rows without multiplication.

Classify a shipment as on time when the earliest delivered_date is not NULL and delivered_date <= promised_date. Equality matters: delivery exactly on the promised date counts as on time. Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) for on_time_shipments.

Calculate on_time_rate as on_time_shipments divided by total_shipments. Multiplying the numerator by 1.0 prevents integer division in this PostgreSQL-style implementation, and ROUND(..., 4) produces a decimal rate with up to four decimal places.

The SQL is:

WITH first_delivery AS ( SELECT ship_id, MIN(delivered_date) AS delivered_date FROM deliveries GROUP BY ship_id ) SELECT DATE_TRUNC('month', s.shipped_date) AS month, s.ship_from_warehouse AS warehouse, COUNT() AS total_shipments, SUM( CASE WHEN fd.delivered_date IS NOT NULL AND fd.delivered_date <= s.promised_date THEN 1 ELSE 0 END ) AS on_time_shipments, ROUND( SUM( CASE WHEN fd.delivered_date IS NOT NULL AND fd.delivered_date <= s.promised_date THEN 1 ELSE 0 END ) * 1.0 / COUNT(), 4 ) AS on_time_rate FROM shipments s LEFT JOIN first_delivery fd ON s.ship_id = fd.ship_id GROUP BY DATE_TRUNC('month', s.shipped_date), s.ship_from_warehouse ORDER BY month, warehouse;

The main validation check is that the join has not increased the shipment population. After the LEFT JOIN, the row count should match the shipment row count for the same scope. If ship_id is expected to be unique in shipments, COUNT() and COUNT(DISTINCT s.ship_id) should also agree. I would additionally spot-check shipments with multiple delivery scans, shipments with no scans, deliveries exactly on promised_date, and rows where promised_date is NULL.

NULL behavior should be explicit. A shipment with no matching delivery scan has fd.delivered_date = NULL, so the CASE expression returns 0 and the shipment stays in the denominator but not the numerator. If promised_date is NULL, the comparison fd.delivered_date <= s.promised_date evaluates to unknown rather than true, so the CASE also returns 0 under this query. If the business wants another rule for missing promised dates, that rule should be stated and implemented explicitly.

Date boundaries also matter. DATE_TRUNC groups by shipped_date, so a shipment belongs to the month in which it shipped, regardless of when it was promised or delivered. If these columns are timestamps rather than dates, the reporting time zone should be agreed before deriving the month or comparing calendar dates.

An alternative way to choose the earliest delivery is ROW_NUMBER() OVER (PARTITION BY ship_id ORDER BY delivered_date, <deterministic tie-breaker>) and then keeping row number 1. That is useful when additional columns from the exact earliest scan row are needed. For this question, only the earliest delivered_date is required, so MIN(delivered_date) is simpler and matches the approved diagram.

For performance, correctness comes first: reduce the one-to-many delivery rows before joining. On large tables, an index or access path involving deliveries.ship_id and delivered_date may help, but the useful optimization depends on the database engine, data volume, statistics, and execution plan. The important failure mode is joining raw delivery scans first and then counting rows, because that can inflate both totals and rates.

1. Define the grain: one row per shipment before aggregation, then one output row per shipped month and warehouse.
2. Aggregate deliveries by ship_id with MIN(delivered_date) to select the earliest delivery scan and prevent one-to-many row multiplication.
3. LEFT JOIN the deduplicated delivery result to shipments on ship_id so shipments without scans remain in the population.
4. Derive month from shipments.shipped_date and use ship_from_warehouse as the warehouse dimension.
5. Count each joined shipment row once as total_shipments.
6. Use CASE to count a shipment as on time only when delivered_date is not NULL and delivered_date <= promised_date.
7. Divide on_time_shipments by total_shipments using non-integer arithmetic and round the rate for presentation.
8. GROUP BY month and warehouse and ORDER BY month, warehouse.
9. Validate the joined row count against the shipment population and spot-check duplicate scans, NULL deliveries, promised-date boundaries, and NULL promised dates.

The database must first read delivery-scan rows and group them by ship_id to find one minimum delivered_date per shipment. It then joins that smaller result to shipments and performs another aggregation by month and warehouse. Exact runtime depends on table size, indexes, statistics, and the execution plan. The extra deduplication step is worthwhile because it prevents a correctness error: multiple delivery scans must not cause one shipment to be counted more than once.

The implementation follows the approved diagram's five-step flow: get the earliest delivery per shipment, join that result to shipments, classify each shipment as on time or not, aggregate by shipped month and warehouse, and validate the totals. The first_delivery CTE uses MIN(delivered_date) GROUP BY ship_id so the LEFT JOIN remains at one row per shipment. The final query uses COUNT() for total shipments, a CASE expression for the on-time count, and their ratio for on_time_rate.

Joining shipments directly to raw deliveries and counting the multiplied rows; using MAX(delivered_date) instead of the required earliest scan; using INNER JOIN and dropping shipments with no delivery scan; deriving the reporting month from delivered_date instead of shipped_date when following this specification; forgetting that comparisons with NULL are not true; using integer division for the rate; deduplicating only the denominator while leaving the numerator at scan grain; assuming promised_date NULL behavior without stating it; and failing to validate that the post-deduplication join still has one row per shipment.

Lead with the grain problem before showing syntax: deliveries is one-to-many relative to shipments, so reduce it to one row per ship_id first. Then explain why LEFT JOIN keeps undelivered shipments in the denominator, show the CASE condition for on-time classification, and finish with the row-count validation. That demonstrates relational reasoning, metric definition, and SQL correctness in a clear order.

I would first make the joined data one row per shipped order, assuming shipments contains one relevant shipment row per order. I would use the order creation week as week_start, calculate lead_days as shipped_at minus order_created_at, and group by week_start and product_category. PostgreSQL's PERCENTILE_CONT(0.5) gives the median. Then I would use LAG on that weekly result, partitioned by product_category and ordered by week_start, and subtract the previous median from the current median. I would also check duplicate shipment rows, NULL shipment dates, invalid negative lead times, missing weeks, and time-zone boundaries.

The task is to measure the typical number of days from order creation to shipment for each product category and each order-created week, then show how that median changed from the previous available week. The safest approach is to keep the join at one row per shipped order before calculating anything. From that order-level data, derive week_start and lead_days, aggregate to one row per week and category using the median, and finally use LAG to compare each category only with its own earlier weekly result.

1. Should week_start be based on order_created_at, as shown here, or on shipped_at?
2. Does shipments contain exactly one relevant shipment row per order, or can an order have multiple shipment events?
3. If multiple shipment rows exist, should lead time use the first shipment, final shipment, or another business-defined event?
4. Should orders with NULL shipped_at be excluded as unshipped orders?
5. Should rows where shipped_at is earlier than order_created_at be treated as data errors and excluded?
6. Which business time zone should define the weekly boundary if these timestamps are time-zone-aware?
7. Does week-over-week mean the previous available row for that category, or the immediately previous calendar week even when a category has no data that week?

The important starting point is the data grain. orders should contribute one row per order, products should contribute one row per product, and this solution assumes shipments contributes one relevant shipment row per order. Joining orders to shipments on order_id and to products on product_id therefore keeps the working grain at one row per shipped order. If shipments is one-to-many, I would first reduce it to one shipment event per order, such as MIN(shipped_at) when the requirement is first shipment, before performing the joins.

For every valid shipped order, derive week_start with date_trunc('week', order_created_at) and calculate lead_days as the elapsed interval from order_created_at to shipped_at, converted to days with EXTRACT(EPOCH FROM (shipped_at - order_created_at)) / 86400.0. The predicate shipped_at >= order_created_at removes negative lead times. It also excludes rows with NULL shipped_at because a comparison with NULL is not TRUE.

Next, aggregate from order grain to one row per week_start and product_category. PostgreSQL's PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lead_days) returns the continuous 50th percentile, which is the median used here. Using the median rather than the average makes the metric less sensitive to unusually long shipping times.

After aggregation, apply the window function at the weekly-category grain. LAG(median_lead_days) OVER (PARTITION BY product_category ORDER BY week_start) gets the prior available weekly median within the same category. The required wow_change is median_lead_days - previous_week_median. A positive value means the median lead time increased compared with that prior available category week; a negative value means it decreased. The first available row for each category has no previous value, so wow_change is NULL.

One important boundary is that LAG compares adjacent rows in the category's result, not automatically adjacent calendar weeks. If a category has no row for a calendar week, this query compares the next populated week with the previous populated week. If the requirement is a strict calendar-week comparison, I would build a weekly calendar spine for each category, join the weekly medians to it, decide how missing medians should be represented, and only then apply LAG.

For time-zone handling, I would establish one business time zone before truncating timestamps if order_created_at is time-zone-aware. Otherwise, orders near a week boundary can fall into a different reporting week depending on the effective time zone used for the truncation.

Before trusting the result, I would compare the joined row count with the distinct order_id count. If the row count is unexpectedly larger, a one-to-many shipment join may be multiplying orders and changing the median. I would separately count NULL shipped_at values and rows with shipped_at < order_created_at. I would also manually recalculate lead time for a small sample of orders and compare weekly-category counts and medians with a trusted extract or independently reproduced query.

The first CTE creates the correct order-level analytical data. The second CTE changes the grain to one row per week and category and calculates the median. The final SELECT keeps that grain and adds the window-based comparison. This separation makes the query easier to validate because joins and row-level calculations happen before aggregation, while LAG happens only after the median has been calculated. It also avoids applying a weekly comparison at raw order grain, which would compare the wrong level of data.

1. Use PostgreSQL and define the working grain as one row per shipped order after joining orders, shipments, and products.
2. Assume one relevant shipment row per order; if shipments is one-to-many, first reduce it to the required shipment event, such as MIN(shipped_at) for first shipment.
3. Join orders to shipments on order_id and products on product_id.
4. Exclude unavailable or invalid lead times with shipped_at >= order_created_at.
5. Derive week_start from order_created_at with date_trunc('week', ...).
6. Calculate lead_days as the order-to-ship interval converted to days.
7. GROUP BY week_start and product_category.
8. Calculate median_lead_days with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lead_days).
9. Use LAG(median_lead_days) OVER (PARTITION BY product_category ORDER BY week_start).
10. Return wow_change as the current median minus the previous available week's median.
11. Validate distinct order counts, duplicates, nulls, invalid dates, week boundaries, and several manually checked orders.

The joins process the relevant order, shipment, and product rows, while computing an exact median requires ordering lead-time values within each week-and-category group. The LAG step is applied only after aggregation, so it works on the much smaller weekly result. On large tables, indexes on join keys such as shipments.order_id and products.product_id can help the joins, but actual performance depends on table sizes, statistics, filters, and the query plan. Correct grain matters more than adding indexes blindly because duplicate shipment rows would produce a fast but incorrect median.

PostgreSQL implementation matching the approved diagram: the base CTE joins the three tables at order grain, derives the order-created week and lead time in days, and removes rows with missing or negative shipment timing. The med CTE calculates one median per week and product category. The final query uses LAG within each product category and subtracts that prior available weekly median from the current median. This assumes one relevant shipment row per order; if shipments can contain multiple rows, reduce them to the intended shipment event before the join.

Common mistakes are joining a one-to-many shipments table without first choosing the intended shipment event, which duplicates orders and distorts the median; averaging lead time instead of calculating the requested median; grouping by shipped week instead of order-created week without clarifying that choice; applying LAG before producing the weekly-category aggregate; forgetting PARTITION BY product_category and therefore comparing different categories; reversing the subtraction and reporting previous minus current instead of current minus previous; treating LAG as the immediately previous calendar week when missing weeks exist; allowing NULL or negative lead times without a defined rule; and ignoring the business time zone around weekly boundaries.

Explain the grain first: one row per shipped order, then one row per week and category. After that, describe the query as three simple stages—calculate lead time, take the weekly median, then use category-partitioned LAG. Mention the missing-week behavior explicitly because it shows you understand the difference between the previous result row and the previous calendar week.