Non-Additive: Ratios, Percentages, Distinct Counts

The interviewer gives you a table with CTR (click-through rate) as a column and asks you to roll it up by product category. This is a trap. Pre-computed ratios break on every rollup. The candidate who says 'I would not store CTR in the fact table; I would store clicks and impressions and compute the ratio at query time' passes. The candidate who says 'just average the CTRs' fails, because that produces a mathematically wrong result. Why the Candidate Who Stores CTR Fails Set up the trap: 'Two product categories: Shoes has 10,000 impressions with 5% CTR. Hats has 100 impressions with 20% CTR. What is the overall CTR?' Pause. If you average the stored CTRs, you get 12.5%. The correct answer from components is 520/10,100 = 5.15%. The stored-ratio answer is 2.4x wrong.' Walk through this math