ORIGINAL COMPANY-CONTEXT PRACTICE
Attribute returns to a purchase cohort
Original practice scenario inspired by Industria de Diseño Textil, S.A.'s public business context; this is not a reported interview question or a description of its internal systems. A fictional fashion retailer wants purchase-month cohort revenue net of later returns. Orders, items, and return events are separate tables; one item can have partial returns. Explain the query grain and the difference between an as-of view and a mature view.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Assign the cohort from the original purchase, then subtract eligible returns linked to its items. Return processing month is a different reporting dimension and should not silently redefine the purchase cohort.
Work through it
- Aggregate purchased and returned quantities or signed amounts at item grain, using stable item identifiers. Deduplicate event replays without removing legitimate partial returns.
- Apply an explicit observation cutoff to return events for an as-of report. Join item-level totals back to the original order month before aggregating cohort revenue.
- Report cohort age and outstanding return windows. Test multiple partial returns, a later-month return, and a return event received after the report cutoff.
Tradeoffs to discuss
- Mature cohorts are more comparable but arrive later. A current as-of view is useful if its incompleteness and revision behavior remain visible.
Common pitfalls
- Joining raw purchases to raw returns can multiply amounts.
- Comparing immature recent cohorts with fully returned older cohorts introduces systematic bias.