In monthly telecom analytics, current ARPU is only part of the story. One window-function transformation turns raw subscriber-month rows into three behavioural features close to the source — movement, trajectory, and relative position.
A 30-second animated technical explainer shows how illustrative subscriber-month data can be enriched with SQL. It begins with raw subscriber ID, month, and ARPU values. LAG() then retrieves each subscriber’s previous-period ARPU. CASE uses the change relative to the previous value to assign a trajectory state such as NEW, ACCELERATING, DECELERATING, or STABLE. NTILE(5) then assigns each subscriber to a relative value quintile within the month. The data shown is illustrative and does not represent client, customer, or operational data.
TL;DR: Use the right layer for the transformation. When the work is relational and set-based, and the data already lives in the database, a single SQL transformation can create several behavioural features close to the source — movement with LAG, trajectory with CASE, and relative position with NTILE — provided window functions are ordered by a real chronological field. Python remains the right place for modelling, experimentation, and custom logic; the point is not SQL versus Python, it is choosing the layer that expresses the transformation clearly, testably, and maintainably.
In monthly telecom analytics, the current average revenue per user is only part of the story. A single ARPU value tells you where a subscriber sits right now, but not where they came from or whether their behaviour is changing. The more useful signal is usually in the movement: how value changes over time, whether a subscriber is accelerating or decelerating, and where that subscriber sits relative to others in the same period.
The usual reflex is to move the table into a notebook before computing any of this. That adds a handoff and a second implementation of logic that is really just relational — a set of comparisons over rows that already live in the database.
This short example uses illustrative subscriber data to derive three features close to the source, in one SQL transformation. Each window function answers a different behavioural question.
Partitioned by subscriber and ordered by a real chronological field, LAG(arpu)
puts the prior month’s value next to the current one — the movement the raw table
never showed.
Comparing current ARPU against the previous value assigns an illustrative state — NEW, ACCELERATING, DECELERATING, or STABLE — so a numeric change becomes a readable behavioural label.
NTILE(5) partitions by month, so each subscriber gets a relative position
within their own period rather than a global rank that mixes months together.
This is not SQL versus Python. Python is often the right environment for modelling, experimentation, custom logic, and downstream analysis. SQL is often a strong first layer when the transformation is relational, set-based, and the data already lives in the database.
One implementation detail matters: window functions should be ordered by a real chronological
field such as month_start — not display labels such as Jan,
Feb, or Mar, which can sort alphabetically rather than
chronologically.
One transformation expresses the feature logic near the data, and the output is a table that is already usable for the next step. Movement, trajectory, and relative position arrive together, with no export, no notebook, and no second copy of the logic to keep in sync.
Before moving a table into a notebook, I find one question useful: can the database express this transformation clearly, testably, and maintainably? When the answer is yes, keeping the features in SQL is the simpler, more durable choice.
Design insight: Use the right layer for the transformation. When the work is relational and set-based, and the data already lives in the database, a single SQL transformation can create several behavioural features close to the source — movement with LAG, trajectory with CASE, and relative position with NTILE — provided window functions are ordered by a real chronological field. Python remains the right place for modelling, experimentation, and custom logic; the point is not SQL versus Python, it is choosing the layer that expresses the transformation clearly, testably, and maintainably.
0:00–0:03 — SQL window functions. Raw monthly usage data can become three behavioural
features in one SQL transformation.
0:03–0:08 — Start with monthly subscriber data: subscriber ID, month, and ARPU.
0:08–0:14 — LAG retrieves the previous-period value within each subscriber.
0:14–0:19 — CASE converts the movement into a behavioural trajectory state:
accelerating, decelerating, stable, or new.
0:19–0:24 — NTILE assigns a relative value quintile within the current month.
0:24–0:27 — One SQL transformation expresses the feature logic near the data.
0:27–0:30 — Move to Python when modelling needs it. Stay in SQL when the database
expresses it cleanly. Use the right layer for the transformation.
Use the right layer for the transformation. When the work is relational and set-based, and the data already lives in the database, a single SQL transformation can create several behavioural features close to the source — movement with LAG, trajectory with CASE, and relative position with NTILE — provided window functions are ordered by a real chronological field. Python remains the right place for modelling, experimentation, and custom logic; the point is not SQL versus Python, it is choosing the layer that expresses the transformation clearly, testably, and maintainably.
This was written by Mahmoud Trigui, Senior Data Scientist. One SQL transformation creates three behavioural features from monthly telecom data: LAG for previous-period ARPU, CASE for trajectory state, and NTILE for a value quintile.