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

97 practiced

Given events(user_id INT, event_time TIMESTAMP, event_type TEXT), write SQL to return the most recent event (all columns) for each user. Use a window function in your solution and return only the latest row per user.

Sample Answer

Approach: Use ROW_NUMBER() partitioned by user_id and ordered by event_time DESC to rank events per user, then select rows with rank = 1.

sql

WITH ranked_events AS (
  SELECT
    user_id,
    event_time,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
  FROM events
)
SELECT user_id, event_time, event_type
FROM ranked_events
WHERE rn = 1;

Key points:- ROW_NUMBER() gives a unique rank per user; ORDER BY event_time DESC ensures newest event gets rn = 1.- Returns all columns for the most recent event per user.

Edge cases:- If multiple events share the exact same latest timestamp and you want all ties, use RANK() or DENSE_RANK() instead of ROW_NUMBER().- If event_time can be NULL and you want to ignore nulls, add WHERE event_time IS NOT NULL in the CTE.

Alternatives:- In Postgres you can use DISTINCT ON (user_id) ... ORDER BY user_id, event_time DESC.- A join against a subquery that selects MAX(event_time) per user also works but window functions are often clearer and avoid extra joins.

MediumSystem Design

56 practiced

Design a SQL query that builds a weekly cohort retention matrix: cohorts identified by signup_week (ISO week), and retention columns week_0, week_1, week_2, ... up to week_12. Use users(user_id, signup_date) and events(user_id, event_date). Provide a clear approach and a sample SQL snippet that fills the cohort-week cells.

Sample Answer

Approach:- Define each user’s cohort as the ISO week of their signup (use the week start date for deterministic arithmetic).- For each event, compute the number of weeks since the user’s signup/week-start (week_index = 0,1,2,...).- Aggregate distinct users per (cohort_week, week_index) to count active users in that cohort-week.- Cross-join cohorts with week indices 0..12 to ensure empty cells show 0.- Optionally compute retention% by dividing by cohort size.

Sample SQL (Postgres) — produces counts and retention% up to week_12:

sql

WITH users_cohort AS (
  SELECT
    user_id,
    date_trunc('week', signup_date::date) AS cohort_week_start,
    COUNT(*) OVER (PARTITION BY date_trunc('week', signup_date::date)) FILTER (WHERE true) -- no-op to show window use
  FROM users
),
cohort_sizes AS (
  SELECT cohort_week_start, COUNT(DISTINCT user_id) AS cohort_size
  FROM users_cohort
  GROUP BY cohort_week_start
),
events_weeks AS (
  SELECT
    e.user_id,
    date_trunc('week', u.signup_date::date)           AS cohort_week_start,
    date_trunc('week', e.event_date::date)            AS event_week_start,
    FLOOR(EXTRACT(EPOCH FROM (date_trunc('week', e.event_date::date) - date_trunc('week', u.signup_date::date))) / 604800)::int AS week_index
  FROM events e
  JOIN users u USING (user_id)
  WHERE e.event_date >= u.signup_date  -- only count events after signup
),
cohort_week_activity AS (
  SELECT
    cohort_week_start,
    week_index,
    COUNT(DISTINCT user_id) AS active_users
  FROM events_weeks
  WHERE week_index BETWEEN 0 AND 12
  GROUP BY 1,2
),
all_cohorts AS (
  SELECT DISTINCT cohort_week_start FROM users_cohort
),
weeks AS (
  SELECT generate_series(0,12) AS week_index
),
grid AS (
  SELECT c.cohort_week_start, w.week_index
  FROM all_cohorts c CROSS JOIN weeks w
)
SELECT
  g.cohort_week_start,
  cs.cohort_size,
  COALESCE(SUM(CASE WHEN g.week_index = 0 THEN a.active_users END),0) AS week_0,
  COALESCE(SUM(CASE WHEN g.week_index = 1 THEN a.active_users END),0) AS week_1,
  COALESCE(SUM(CASE WHEN g.week_index = 2 THEN a.active_users END),0) AS week_2,
  -- ... repeat through week_12 or pivot dynamically in client
  COALESCE(MAX(CASE WHEN g.week_index = 12 THEN a.active_users END),0) AS week_12,
  -- retention% example for week_1
  ROUND(100.0 * COALESCE(MAX(CASE WHEN g.week_index = 1 THEN a.active_users END),0) / NULLIF(cs.cohort_size,0),2) AS week_1_retention_pct
FROM grid g
LEFT JOIN cohort_week_activity a
  ON a.cohort_week_start = g.cohort_week_start AND a.week_index = g.week_index
LEFT JOIN cohort_sizes cs
  ON cs.cohort_week_start = g.cohort_week_start
GROUP BY g.cohort_week_start, cs.cohort_size
ORDER BY g.cohort_week_start;

Notes and edge cases:- Use date_trunc('week') which starts weeks on Monday in Postgres (aligns with ISO week start).- Ensure event dates before signup are excluded.- For large tables, pre-aggregate events and index date columns. For presentation, pivoting is often done in the BI tool for dynamic columns.

MediumTechnical

53 practiced

You need to identify users who are at risk of churn. Using events(user_id, event_time), describe and implement a SQL approach that computes last_activity per user, activity recency buckets, and flags users who have not been active for more than 30 days relative to a reference date. Use window functions where helpful.

Sample Answer

Approach: compute each user’s most recent event (last_activity) relative to a given reference_date, derive days_since_last_activity, bucket users by recency, and flag churn-risk when days_since_last_activity > 30. Use window functions to get last activity and handle multiple events per user.

sql

-- reference date can be a literal or parameter
WITH params AS (
  SELECT DATE '2025-11-01' AS reference_date
),

-- dedupe events and compute last activity per user using window
last_events AS (
  SELECT
    user_id,
    MAX(event_time)::date AS last_activity  -- last activity date
  FROM events
  GROUP BY user_id
),

-- compute recency and buckets relative to reference date
recency AS (
  SELECT
    le.user_id,
    le.last_activity,
    p.reference_date,
    DATE_PART('day', p.reference_date - le.last_activity) AS days_since_last_activity,
    CASE
      WHEN DATE_PART('day', p.reference_date - le.last_activity) BETWEEN 0 AND 7 THEN '0-7 days'
      WHEN DATE_PART('day', p.reference_date - le.last_activity) BETWEEN 8 AND 30 THEN '8-30 days'
      WHEN DATE_PART('day', p.reference_date - le.last_activity) BETWEEN 31 AND 90 THEN '31-90 days'
      WHEN DATE_PART('day', p.reference_date - le.last_activity) > 90 THEN '90+ days'
      ELSE 'future_or_null'
    END AS recency_bucket,
    CASE
      WHEN DATE_PART('day', p.reference_date - le.last_activity) > 30 THEN TRUE
      ELSE FALSE
    END AS churn_risk_30d
  FROM last_events le
  CROSS JOIN params p
)

SELECT * FROM recency
ORDER BY days_since_last_activity DESC;

Key points:- MAX(event_time) grouped by user gives last_activity efficiently; window-based alternative: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) and pick row_number=1.- Use a param for reference_date to evaluate churn at any snapshot.- Edge cases: users with no events won't appear (LEFT JOIN users table if needed), timezone differences (store UTC), future-dated events produce negative days; handle nulls.- Performance: ensure index on events(user_id, event_time) for fast aggregation; for very large tables, consider incremental last_activity materialized table.

HardSystem Design

55 practiced

Stream processing vs batch: events are arriving in Kafka. You must compute rolling daily active users and session counts in near real time. Outline designs using (a) SQL-like stream processing (ksqlDB, Flink SQL), and (b) micro-batch SQL in a data warehouse. Discuss correctness concerns (event time, watermarks), windowing semantics, and expected latency.

Sample Answer

Requirements:- Compute rolling Daily Active Users (DAU) and session counts per user in near real-time from Kafka events.- Must handle out-of-order and late events, provide near-correct metrics with bounded latency, support corrections.

(a) SQL-like stream processing (ksqlDB / Flink SQL)Design:- Ingest Kafka topic with event_time field.- Define event-time processing using TIMESTAMP BY event_time.- DAU: use a hopping or tumbling 24-hour window anchored to day boundaries: SELECT window_start, COUNT(DISTINCT user_id) FROM stream GROUP BY TUMBLE(event_time, INTERVAL '1' DAY);- Sessions: use SESSION window with gap (e.g., 30m): SELECT user_id, SESSION(event_time, INTERVAL '30' MINUTE) AS sess, COUNT(*)...Correctness concerns:- Watermarks: configure watermark strategy (e.g., current_max_ts - 1m) to bound lateness. Watermarks determine when a window is emitted and state purged.- Allowed lateness: set a retention / allowed-lateness parameter to accept late events and update results (emit retractions or updates).- Exactly-once: rely on Flink’s checkpointing and Kafka transactional writes for correctness.Window semantics:- Event-time windows ensure correctness despite out-of-order events; session windows handle dynamic sessions per user.Expected latency:- Milliseconds to seconds (depending on watermark delay and processing time). Trade-off: smaller watermark lag → lower latency but higher risk of dropping late events or producing corrections.

(b) Micro-batch SQL in data warehouse (e.g., BigQuery / Snowflake using scheduled ingestion)Design:- Use streaming ingestion or Kafka->staging S3 -> load micro-batches every 1–5 min.- For DAU: run incremental SQL that deduplicates by event_id and computes unique users per day using approximate distinct (HyperLogLog) or exact with dedupe.- Sessions: sessionization via SQL using LAG(event_time) OVER (PARTITION BY user ORDER BY event_time) and CASE WHEN gap>30m THEN new_session.Correctness concerns:- Event-time: need to ensure ingestion preserves event_time and batches include late events; batch window boundaries may miss very-late events until next run.- Deduplication: required to handle duplicates in micro-batches.Window semantics:- Windowing operates on event_time but bounded by batch granularity; sessionization in SQL must handle boundaries across batches (require storing per-user last event timestamp/state).Expected latency:- Minutes (batch interval + processing), typically 1–5 minutes or higher. Lower operational complexity but slower updates and more complex state management for sessions.Trade-offs summary:- Stream SQL: stronger event-time correctness, native watermarks, session windows, low latency, but operationally more complex.- Micro-batch: simpler infra, easier SQL tooling, higher latency, harder to handle late events and cross-batch session state; use for less time-sensitive reports.

EasyTechnical

65 practiced

Describe scenarios where you would prefer a window function over a subquery or GROUP BY in an analytics pipeline. Provide at least three concrete examples tied to metrics commonly used by product teams.

Sample Answer

Window functions are preferable when you need row-level context while still aggregating — they avoid extra joins, preserve granularity, and are more efficient/readable than subqueries or repeated GROUP BYs. Here are three concrete analytics scenarios product teams use, with brief SQL patterns and reasoning.

1) Rolling conversion by user (per-user sequence/order)- Metric: For each user, mark whether a session led to conversion and compute conversion for the nth session.- Why window: You need per-session rows with a rank and a partitioned aggregate (e.g., first-conversion session). GROUP BY would collapse sessions; subqueries become complex joins.- SQL:

sql

SELECT user_id, session_id, session_ts,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_ts) AS session_num,
       SUM(CASE WHEN converted THEN 1 ELSE 0 END) 
         OVER (PARTITION BY user_id ORDER BY session_ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_conversions
FROM sessions;

2) 7-day moving average DAU (time-series smoothing)- Metric: 7-day moving average of daily active users.- Why window: Easy sliding-window computations across ordered dates; GROUP BY gives daily counts but not sliding averages without self-joins.- SQL:

sql

WITH dau AS (
  SELECT date, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY date
)
SELECT date, dau,
       AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mau_7d
FROM dau;

3) Percentile / ranking among cohorts (power users)- Metric: Identify top 5% users by spend within each cohort.- Why window: Pct_rank or NTILE per cohort is straightforward; subqueries would need complex joins and lose row context.- SQL:

sql

SELECT user_id, cohort, total_spend,
       PERCENT_RANK() OVER (PARTITION BY cohort ORDER BY total_spend) AS cohort_pct
FROM user_spend;

Key benefits: keep row-level detail, simpler/performant sliding-window or ordered computations, and clearer intent for analytics consumers. Use GROUP BY when you only need a single aggregated row per key. Use window functions when you need aggregates alongside original rows or ordered/rolling calculations.

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.