John upgraded in June. Which segment gets his Q1 spending?

transcript

John bought things in Q1. In June, he upgraded to Premium. So whose revenue was Q1? Standard's, or Premium's?

January: John is a Standard customer. Bronze tier. He makes purchases in Q1. June: upgraded. Premium, Gold. Now finance wants Q1 revenue by segment.

What did today's Premium customers spend in Q1? Then John is Premium. What was Q1 revenue by segment? Then he's Standard. You must support both.

The easy way: overwrite John's row. Now it just says Premium. Every old sale now sees today's value. His Q1 moves to Premium.

Type 2 adds a row instead. Key 1: customer 123, Standard, January to May. Key 2: the same customer, Premium, from June. Same ID, two keys.

Here's the trick. A sale stores the key that was valid when it happened. So John's Q1 sales point at key 1. Standard, for good. Later sales point at key 2. And the query needs no date range.

But careful. Add a filter for the current row to that join. Now every sale moves to today's segment. The Type 1 problem, all over again.

Full breakdown in the lecture.