ORIGINAL COMPANY-CONTEXT PRACTICE
Compare promotion baskets without join inflation
Original practice scenario inspired by Reliance Industries Limited's public business context; this is not a reported interview question or a description of its internal systems. A synthetic retailer stores baskets, line items, and promotion applications. Several promotions can touch the same basket. Explain how to compare basket revenue for promoted versus unpromoted baskets while preserving one contribution per basket.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Classify and aggregate at the basket grain before summarizing the promotion groups. Joining line items directly to promotion applications multiplies revenue whenever a basket has more than one application.
Work through it
- Sum signed line-item amounts per basket, including the defined treatment of returns and discounts. Independently derive a boolean indicating any qualifying promotion application.
- Join those one-row-per-basket results to completed baskets. Group by the promotion flag and report basket count, total revenue, and average basket revenue.
- Test a basket with two items and two promotions, an empty basket, and a refunded basket. Reconcile total revenue to the original basket aggregate.
Tradeoffs to discuss
- The result describes associated basket values, not incremental promotion effect. Customer selection and campaign targeting can confound the comparison.
Common pitfalls
- SUM(DISTINCT amount) can remove legitimate equal-priced items.
- Counting application rows as baskets distorts both the denominator and group assignment.