Window Functions and Time Series Analytics Questions

Advanced SQL window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions (SUM, COUNT, AVG) with OVER and partition clauses. Using window functions to solve practical problems: ranking users or events within segments, calculating running totals and cumulative metrics, identifying trends and transitions over time, detecting patterns in user behavior sequences. Applications: cohort retention analysis (calculating retention rates across cohorts), user lifetime value trends, engagement metrics over time windows, and sequential user actions.

EasyTechnical

51 practiced

Using the purchases table above, show two SQL approaches to get each user's first purchase: (1) aggregation with MIN(purchased_at) GROUP BY user_id, and (2) a window function approach that returns the entire first purchase row (amount and purchase_id). Provide the queries and discuss pros and cons for performance and for retrieving additional columns from the first row.

Sample Answer

Approach 1 — aggregation (get each user’s earliest purchased_at):

sql

SELECT
  user_id,
  MIN(purchased_at) AS first_purchased_at
FROM purchases
GROUP BY user_id;

Pros:- Very fast and simple; uses a grouped index on (user_id, purchased_at) if available.- Low memory overhead; good when you only need the timestamp.

Cons:- Returns only the aggregated column (first_purchased_at). To get purchase_id or amount you must join back to the table, which can be tricky if multiple rows share the same timestamp.

Example join to get full row (handles ties arbitrarily or via extra tie-breaker):

sql

SELECT p.*
FROM purchases p
JOIN (
  SELECT user_id, MIN(purchased_at) AS first_purchased_at
  FROM purchases
  GROUP BY user_id
) m ON p.user_id = m.user_id AND p.purchased_at = m.first_purchased_at;

Approach 2 — window function (returns the entire first purchase row):

sql

SELECT user_id, purchase_id, amount, purchased_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchased_at, purchase_id) AS rn
  FROM purchases
) t
WHERE rn = 1;

Pros:- Directly returns the full first row (you can pick any columns).- Allows deterministic tie-breaking via ORDER BY (e.g., purchase_id).

Cons:- May be heavier on memory/CPU for very large tables because window functions process all rows per partition (although many DB engines optimize via partitioning and indexes).- Performance depends on available indexes and DB optimizer; for large datasets an indexed aggregation + join can be faster.

Recommendation:- If only timestamp needed, use MIN(... ) GROUP BY.- If you need other columns from the same first row, prefer ROW_NUMBER() for correctness and controllable tie-breakers; if performance is a concern, ensure appropriate indexing (user_id, purchased_at) or test both approaches.

MediumTechnical

66 practiced

Find top 3 customers by monthly order_value from orders(order_id, customer_id, order_value, order_date). Include ties such that if multiple customers tie for 3rd place they are all included. Write SQL using RANK or DENSE_RANK and explain why you chose one over the other.

Sample Answer

Approach: Aggregate order_value by customer per month, then use a window function to rank customers within each month. Because we want to include ties for the 3rd place (i.e., if multiple customers tie at the third highest value all should be included), use RANK() — RANK will assign the same rank to tied totals and skip ranks after ties, so all tied 3rd-place customers get rank = 3.

sql

WITH monthly_customer AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    customer_id,
    SUM(order_value) AS monthly_value
  FROM orders
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    month,
    customer_id,
    monthly_value,
    RANK() OVER (PARTITION BY month ORDER BY monthly_value DESC) AS rnk
  FROM monthly_customer
)
SELECT
  month,
  customer_id,
  monthly_value
FROM ranked
WHERE rnk <= 3
ORDER BY month, monthly_value DESC, customer_id;

Why RANK vs DENSE_RANK:- RANK returns ties with the same rank and can skip ranks (e.g., 1,1,3), so all tied at rank 3 are included.- DENSE_RANK would compress ranks (1,1,2), which might exclude customers who tied for the third-highest value because the compressed rank for that value could be 2. For "top 3 positions including ties", RANK better matches the business requirement.

Edge cases:- Months with fewer than 3 customers (will return all).- Null order_value (exclude or treat as 0 per business rule).- Ensure timezone/truncation consistent with reporting needs.

MediumTechnical

60 practiced

Event monetization: events(user_id, event_time, plan) where plan in ('free','paid'). Write SQL to find each user's first_paid_date, i.e., the earliest event_time when plan changed to paid, but return null for users who never paid. Use window functions and consider users whose first event is already paid.

Sample Answer

Approach: partition events by user and order by event_time. Use LAG to detect the moment a user switches into paid (either the first event is paid — LAG is NULL — or the previous plan was not paid). Then for each user take the earliest event_time that satisfies that transition. Users with no such row return NULL.

sql

WITH flagged AS (
  SELECT
    user_id,
    event_time,
    plan,
    LAG(plan) OVER (PARTITION BY user_id ORDER BY event_time, /* tie-breaker if needed */ event_time) AS prev_plan
  FROM events
),
paid_transitions AS (
  SELECT
    user_id,
    event_time AS paid_time
  FROM flagged
  WHERE plan = 'paid'
    AND (prev_plan IS NULL OR prev_plan <> 'paid')
)
SELECT
  u.user_id,
  MIN(p.paid_time) AS first_paid_date
FROM (SELECT DISTINCT user_id FROM events) u
LEFT JOIN paid_transitions p
  ON u.user_id = p.user_id
GROUP BY u.user_id
ORDER BY u.user_id;

Key points:- LAG detects transitions; prev_plan IS NULL handles users whose first event is paid.- Aggregation with MIN ensures the earliest paid transition if multiple transitions occur.- LEFT JOIN over distinct users returns NULL for users who never paid.

Edge cases:- Duplicate event_time rows: add a tie-breaker column (event_id) to ORDER BY.- Out-of-order or late-arriving events require cleaned/consistent event_time ordering.

HardTechnical

60 practiced

Detect users who performed a sequence A -> B -> C within 30 days of the first event. Events table: events(user_id, event_type, occurred_at). For each qualifying user, return user_id and the first date when the full sequence completed. Provide a SQL solution using window functions and explain how it handles interleaving events and duplicates.

Sample Answer

Approach: generate one row per event with a row number per user, then self-join the event rows to find an A followed by a later B and then a later C, enforcing the 30-day window. Finally pick the earliest completed C per user. This uses window functions to order events and lets the joins tolerate interleaving and duplicates because we require strict increasing timestamps.

sql

WITH events_sorted AS (
  SELECT
    user_id,
    event_type,
    occurred_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at, event_type) AS rn
  FROM events
),

-- pick all A events
A AS (
  SELECT * FROM events_sorted WHERE event_type = 'A'
),

-- pick B events that occur after A
AB AS (
  SELECT
    a.user_id,
    a.occurred_at AS a_at,
    b.occurred_at AS b_at,
    a.rn   AS a_rn,
    b.rn   AS b_rn
  FROM A a
  JOIN events_sorted b
    ON b.user_id = a.user_id
   AND b.occurred_at > a.occurred_at
   AND b.event_type = 'B'
),

-- pick C events that occur after B and within 30 days of A
ABC AS (
  SELECT
    ab.user_id,
    ab.a_at,
    ab.b_at,
    c.occurred_at AS c_at
  FROM AB ab
  JOIN events_sorted c
    ON c.user_id = ab.user_id
   AND c.occurred_at > ab.b_at
   AND c.event_type = 'C'
   AND c.occurred_at <= ab.a_at + INTERVAL '30 days'
)

-- choose the first completion date (earliest C) per user
SELECT
  user_id,
  MIN(c_at) AS sequence_completed_at
FROM ABC
GROUP BY user_id;

Why this handles interleaving and duplicates:- Ordering by occurred_at (and tie-breaking by event_type) produces a deterministic sequence per user.- Joins require strictly increasing timestamps (b.occurred_at > a.occurred_at and c.occurred_at > b.occurred_at), so intervening unrelated events (A, B, or other types) don’t break the pattern — they are simply ignored unless they satisfy the type/time constraints.- Duplicates (multiple As, Bs, Cs) are handled because we start from every A and search for any later B then any later C; grouping by user and taking MIN(c_at) returns the earliest time the full A→B→C sequence completed.Edge cases:- Concurrent events with identical timestamps: adjust comparison to >= if business rule allows same-timestamp progression; current query treats strictly later events only.- Large event tables: ensure indexes on (user_id, occurred_at, event_type) or consider window/LATERAL optimizations for performance.

HardTechnical

48 practiced

Translate this SQL window-based logic into Spark DataFrame code: compute row_number per user ordered by event_time and a running sum of amount per user. Provide the DataFrame / Spark SQL snippet and discuss memory tuning, broadcast, and alternatives to reduce shuffle overhead in Spark.

Sample Answer

Approach: use Spark window functions to compute row_number per user ordered by event_time and a running (cumulative) sum of amount per user. For large data, tune partitioning and shuffles to avoid expensive full shuffles.

DataFrame API:

python

from pyspark.sql import functions as F
from pyspark.sql.window import Window

# assume df has columns: user_id, event_time (timestamp), amount (double)
w = Window.partitionBy("user_id").orderBy("event_time")\
          .rowsBetween(Window.unboundedPreceding, Window.currentRow)

result = df.withColumn("row_number", F.row_number().over(Window.partitionBy("user_id").orderBy("event_time")))\
           .withColumn("running_sum", F.sum("amount").over(w))

Spark SQL:

sql

SELECT
  user_id,
  event_time,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS row_number,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY event_time
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
FROM events

Memory tuning & shuffle reduction (practical guidance):- Partitioning: ensure data is partitioned by user_id if downstream ops can leverage that. Use df.repartition(n, "user_id") to colocate keys and reduce shuffle for windowing.- shuffle partitions: set spark.sql.shuffle.partitions to a sensible value (e.g., total_cores * 3) to avoid many small tasks or very large partitions.- executor memory: tune spark.executor.memory and spark.memory.fraction; monitor OOMs. Persist intermediate RDDs (cache()) when reused.- sortWithinPartitions: after repartitioning by user_id, call df.sortWithinPartitions("user_id","event_time") then use mapPartitions to compute running sums locally to avoid global shuffle.- Broadcast: not applicable for window per-key computation, but highly useful if you need to join a small lookup table before windowing — use broadcast(small_df) to avoid join shuffle.- Avoid wide transformations: if you only need aggregates per user (e.g., cumulative daily totals), pre-aggregate (groupBy user_id, date) to reduce rows before applying window.

Alternatives to reduce shuffle:- Range-partition by user hash or use custom Partitioner (RDD API) and compute running sums via mapPartitions to operate locally.- Use sortWithinPartitions + mapPartitions to compute row_number and running sum sequentially per partition (fast if each user’s events are co-located).- If exact ordering per user is not strict, consider approximate or time-bucketed aggregates to reduce cardinality.- For streaming / incremental workloads, use stateful aggregations (mapGroupsWithState) to maintain running totals without reprocessing full history.

Edge cases:- Very high-cardinality users: skew mitigation (salting keys) and then combine partial results.- Ties on event_time: define deterministic tie-breaker (e.g., event_id) in ORDER BY.

This approach balances correctness (window semantics) with performance knobs (partitioning, shuffle tuning, local aggregation) that are practical for a Data Analyst working with large Spark datasets.

Unlock Full Question Bank

Get access to hundreds of Window Functions and Time Series Analytics interview questions and detailed answers.

Join thousands of developers preparing for their dream job.