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

67 practiced

Given users(id INT, user_email TEXT, created_at TIMESTAMP, last_login TIMESTAMP) which may contain duplicate emails, write a SQL query using ROW_NUMBER() to deduplicate and keep only the latest created_at row per user_email (ties broken by last_login). Provide sample rows and the expected deduplicated output.

Sample Answer

Approach: use ROW_NUMBER() partitioned by user_email, ordered by created_at DESC then last_login DESC to pick the newest created_at per email; keep row_number = 1.

Sample data (users):

sql

CREATE TABLE users(id INT, user_email TEXT, created_at TIMESTAMP, last_login TIMESTAMP);

INSERT INTO users VALUES
(1, 'alice@example.com', '2024-01-01 08:00', '2024-06-01 09:00'),
(2, 'bob@example.com',   '2024-02-01 09:00', '2024-05-01 10:00'),
(3, 'alice@example.com', '2024-03-01 10:00', '2024-04-01 11:00'),
(4, 'carol@example.com', '2024-01-15 12:00', '2024-06-10 08:00'),
(5, 'alice@example.com', '2024-03-01 10:00', '2024-06-02 07:00'); -- tie on created_at with id=3

Deduplication query:

sql

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_email
           ORDER BY created_at DESC, last_login DESC, id DESC
         ) AS rn
  FROM users
)
SELECT id, user_email, created_at, last_login
FROM ranked
WHERE rn = 1;

Explanation:- Partition by user_email to consider duplicates.- ORDER BY created_at DESC ensures newest created_at wins.- Tie-breaker last_login DESC picks the most recent login when created_at ties; id DESC final deterministic tie-break.- Filter rn = 1 returns one row per email (deduplicated).

Expected output for sample data:id | user_email | created_at | last_login---|------------------|---------------------|---------------------5 | alice@example.com| 2024-03-01 10:00 | 2024-06-02 07:002 | bob@example.com | 2024-02-01 09:00 | 2024-05-01 10:004 | carol@example.com| 2024-01-15 12:00 | 2024-06-10 08:00

Notes:- You can replace ROW_NUMBER() with RANK() if you want to keep multiple tied rows; ROW_NUMBER() guarantees a single keeper.- For large tables, ensure appropriate indexing (e.g., on user_email, created_at) or perform dedupe in a windowed batch to optimize.

EasyTechnical

63 practiced

Scenario: You maintain SQL definitions for windowed metrics (e.g., 7-day rolling active users). How would you document and communicate those SQL-based metric definitions to analysts so the business uses consistent numbers? Describe required artifacts, examples, and lifecycle steps.

MediumTechnical

55 practiced

When using Spark SQL to compute window functions over very large event tables, what execution and memory considerations should you account for? Describe specific strategies such as repartitioning, broadcast joins, adjusting shuffle partitions, and avoiding skew. Provide example Spark SQL or DataFrame snippets illustrating repartitioning before windowing.

Sample Answer

When running window functions on very large event tables, Spark will often need to shuffle and hold partitions in memory for each partition key range. Key execution and memory considerations and strategies:

- Repartition by the window partition key before windowing to align data locality and reduce network shuffle during the window operation.- Avoid wide shuffles and large single partitions — tune spark.sql.shuffle.partitions to an appropriate value (not the default 200 for large clusters) and/or use coalesce when reducing partitions.- Use broadcast joins for small dimension tables to avoid another expensive shuffle; ensure broadcast threshold (spark.sql.autoBroadcastJoinThreshold) is tuned.- Detect and mitigate skew: if certain keys dominate, consider salting the key, using approximate aggregations, or separate handling for heavy keys to avoid OOM on single executors.- Memory: expect spills to disk for very large per-key state; increase executor memory, off-heap (Tungsten) settings, and shuffle/compression settings, but prefer repartitioning and skew fixes first.- Persist intermediate results when reused; prefer narrow transformations before wide operations.

Examples:

Repartition DataFrame before windowing:

python

from pyspark.sql import functions as F, Window

# repartition by event_key to localize window computation
df = spark.read.parquet("/data/events")
df = df.repartition("event_key")  # or .repartition(num_partitions, "event_key")

w = Window.partitionBy("event_key").orderBy("event_time").rowsBetween(-5, 0)
res = df.withColumn("sum_last_5", F.sum("value").over(w))

If using SQL, create a temp view and hint repartitioning:

sql

-- SQL repartition via CREATE TABLE AS or using DISTRIBUTE BY in Hive contexts
CREATE OR REPLACE TEMP VIEW events AS SELECT * FROM parquet.`/data/events`;

-- Use a repartitioning hint in DataFrame or use DISTRIBUTE BY in Spark SQL (spark 3.x may ignore)
SELECT /*+ REPARTITION(event_key, 200) */ 
  event_key, event_time, value,
  SUM(value) OVER (PARTITION BY event_key ORDER BY event_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as sum_last_5
FROM events;

Extra tips: monitor stages with Spark UI, watch shuffle write/read sizes and peak task memory; if tasks spill a lot, split keys, reduce parallelism per task, or implement two-phase aggregation.

MediumTechnical

53 practiced

Compare approaches to enrich events with the most recent user profile as of event_time: (A) computing latest profile per user using window functions and joining, (B) temporal range joins, (C) doing enrichment in the pipeline (stateful). Discuss consistency, complexity, and performance trade-offs for analytics at scale.

Sample Answer

Context: goal is to enrich event records with the user profile that was valid at event_time (point-in-time correctness) for large-scale analytics. Compare three common patterns: (A) precompute latest-profile-per-user via window functions and join, (B) temporal (range) joins (time-travel/AS OF joins), (C) in-pipeline stateful enrichment.

(A) Latest-profile-per-user via window + join- What: For a batch job, sort profile-change stream by user/time, use ROW_NUMBER()/LAST_VALUE over partition to produce "latest as of X" snapshots (often materialized per day), then join events on user and snapshot_time <= event_time (or join to snapshot keyed by ingestion date).- Consistency: Good for batch analytics if snapshots are produced at deterministic cutoffs; point-in-time correctness depends on snapshot granularity (e.g., daily snapshots lose intra-day updates).- Complexity: Low-to-moderate. Simple SQL, easy to reason about.- Performance: Scales well for large joins if snapshots are compact; cheaper than scanning full change history. But cannot easily produce per-event millisecond-accurate enrichment without very frequent snapshots.

(B) Temporal range joins (exact time-travel join)- What: Join events to profile-change table using condition profile.user = event.user AND profile.effective_from <= event_time < profile.effective_to (or use DB systems supporting AS OF/temporal joins).- Consistency: Strong — yields exact historical profile matching event_time (point-in-time correct).- Complexity: Higher. Need correct effective intervals, handle concurrent writes, late-arriving profile changes, and maintain profile-change history.- Performance: Potentially expensive: range-joins require scanning profile history or indexed interval joins. At scale you need temporal indexing, partitioning by user, or precomputed intervals; otherwise heavy I/O and shuffle in distributed engines.

(C) Stateful enrichment in pipeline (streaming)- What: Maintain per-user state (latest profile with timestamps) in a streaming engine; on event arrival, lookup state to enrich event. Can optionally buffer events until profile arrives or handle out-of-order with event-time timers.- Consistency: Can be strong if you implement event-time handling and hold windows for late profile updates; otherwise can be eventual/approximate. Hard to guarantee exact point-in-time across restarts unless state is checkpointed reliably.- Complexity: High operational complexity: state management, scaling hot keys (heavy users), windowing for late data, checkpointing and retention, and replay semantics.- Performance: Very efficient for low-latency enrichment (no joins), scales well when state is sharded evenly; suffers with skewed keys and large per-user state. Can reduce downstream compute but increases streaming infrastructure needs.

Trade-offs & recommendation:- If analytics tolerate snapshot granularity (daily/hourly), (A) is simplest and cost-effective.- If exact historical accuracy is required for each event (auditing, billing), use (B) with indexed temporal data store or optimized interval join implementations.- If you need low-latency enrichment for streaming analytics or real-time features, choose (C) but invest in robust state handling, autoscaling, and late-data logic.- Hybrid: use (C) for online low-latency needs and (B) (or frequent (A) snapshots) to backfill/validate analytics and support auditable historical queries.

EasyTechnical

63 practiced

Explain the differences between ROW_NUMBER, RANK, and DENSE_RANK window functions in SQL. In your answer: 1) describe how ties are handled by each function; 2) show a small example table of user_id and score and the resulting output for each function; 3) list typical use-cases where you would choose one over the others.

Sample Answer

ROW_NUMBER, RANK, and DENSE_RANK are window functions that assign a sequential number to rows based on an ORDER BY, but they handle ties differently.

How ties are handled:- ROW_NUMBER(): assigns a unique, consecutive integer to each row regardless of ties (ties get arbitrary but distinct numbers).- RANK(): assigns the same rank to tied rows, then leaves gaps after ties (e.g., if two rows tie at rank 1, next rank = 3).- DENSE_RANK(): assigns the same rank to tied rows, but does NOT leave gaps (if two rows tie at rank 1, next rank = 2).

Example input:user_id | score1 | 952 | 903 | 904 | 855 | 80

SQL to compute:

sql

SELECT user_id, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()      OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK()OVER (ORDER BY score DESC) AS dense_rnk
FROM users;

Result:user_id | score | row_num | rnk | dense_rnk1 | 95 | 1 | 1 | 12 | 90 | 2 | 2 | 23 | 90 | 3 | 2 | 24 | 85 | 4 | 4 | 35 | 80 | 5 | 5 | 4

Typical use-cases:- ROW_NUMBER: pick a single arbitrary row per partition (deduping, top-N with deterministic tie-breaker), or when you need unique IDs.- RANK: when relative position with gaps matters (e.g., competition scoring where ties occupy the same podium positions and subsequent positions reflect count).- DENSE_RANK: when you care about distinct rank groups without gaps (e.g., bucketing users by percentile groups, category-based ranking).

Notes: include additional ORDER BY tie-breakers if deterministic ordering is required (e.g., ORDER BY score DESC, created_at ASC).

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.