Zomato: commission at the rate in force

Constraints

1. 1Join each order to exactly one rate row: the version whose window contains ordered_on.
2. 2The window is half-open. effective_from is inclusive, effective_to is exclusive — an order on the change date belongs to the new rate.
3. 3effective_to is NULL for the version still in force. NULL means 'no end', not 'never applies'.
4. 4orders must equal the number of real orders: 4 for r1, 3 for r2, 3 for r3, 2 for r4. Anything higher means an order matched more than one rate row.
5. 5commission is order_value multiplied by rate_pct / 100, summed per restaurant and rounded to 2 decimal places.
6. 6Order by restaurant_id.

Worked example

r1 has three rate versions — 18% from 1 December, 22% from 1 February, 25% from 1 March — and four orders. Order 7002 was placed exactly on 2026-02-01, so it bills at 22%, not 18%. The four orders total 1750 and the correct commission is 384.00. Join on restaurant_id alone and every r1 order matches all three rate rows: orders reports 12 instead of 4, gross_value triples to 5250, and commission comes out at 1137.50. Using only the current rate is the other failure — it bills January's 400 order at 25% and quietly overcharges the partner.

What this tests

The as-of join against a slowly changing dimension: matching a fact to the one version of a dimension row that was valid at the fact's timestamp, including the half-open boundary and the open-ended current row.