Skip to interview prep
Apexed

23. Revenue doubles after an orders table is joined to support tickets. How would you diagnose and correct the query?

MediumSQL

Technical
Show model answer

Check the grain and cardinality on both sides. An order with several tickets appears several times after a one-to-many join, so summing its amount repeats revenue. Compare row counts, distinct order IDs and totals before and after the join.

Aggregate tickets to the required order grain before joining, or calculate revenue and ticket measures separately at a shared grain. DISTINCT on an amount can erase legitimate equal-valued orders, so validate with fixtures containing multiple tickets and repeated amounts.

What interviewers listen for

  • Inspect join cardinality
  • Aggregate to a shared grain
  • Avoid DISTINCT as a blind fix
Revenue doubles after an orders table is joined to support tickets. How would you diagnose and correct the query — Data & analytics interview question — Apexed