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.

HardTechnical

55 practiced

Discuss trade-offs between computing time-series and windowed analytics in SQL (data warehouse) vs exporting data to Python/R for analysis. Focus on latency, scalability, reproducibility, maintainability, and skillset of a BI team.

Sample Answer

High-level summary: both approaches are valid; SQL/Warehouse is best for low-latency, scalable, reproducible operational analytics; Python/R is best for complex modeling, ad-hoc exploration, and algorithms that don’t translate well to SQL. Choose based on frequency, performance needs, team skills, and long-term maintainability.

Latency- SQL: low end-to-end latency for dashboard queries if you pre-aggregate or use materialized views; near real-time possible with streaming warehouses (Snowflake, BigQuery streaming).- Python/R: higher latency due to extract, transform, load (ETL) and model execution; acceptable for batch analytics or overnight jobs.

Scalability- SQL: scales well for set-based operations; warehouses are optimized for large joins/aggregations and concurrency. Materialized tables and cluster keys aid scale.- Python/R: can scale via distributed frameworks (Dask, Spark) but requires infrastructure/setup. Single-node analyses hit memory/CPU limits sooner.

Reproducibility- SQL: high reproducibility when logic lives in versioned views/stored SQL and CI pipeline; easier for BI tools to reference canonical definitions.- Python/R: reproducible when packaged in notebooks/modules with strict env management (conda, poetry, Docker) and tests, but notebooks often drift and need governance.

Maintainability- SQL: easier for BI teams to maintain—declarative, fewer moving parts, easier to debug for analysts comfortable with SQL. Changes propagate to dashboards.- Python/R: more maintainable for complex algorithms when structured as libraries with tests, but requires software engineering practices (CI/CD, code reviews).

BI Team Skillset- SQL-focused BI: empowers quick iterations, self-service dashboards; minimal ops overhead.- Python/R skills: enable advanced analytics (time-series models, causal inference) and custom visualizations; requires data engineering support for productionization.

Practical guidance- Push aggregations, windows, rolling metrics into warehouse (materialized views, incremental pipelines) for dashboard performance and reproducibility.- Use Python/R for model training, custom forecasting, experimental features; output model predictions back to the warehouse for serving.- Establish standards: versioned SQL, catalog of canonical metrics, CI for code and data tests, and runbooks so both worlds integrate cleanly.

Trade-off example: compute 7-day rolling retention- SQL: simple, fast, reproducible, usable in dashboards.- Python: overkill unless the rolling metric requires complex non-SQL logic; better for exploratory validation.

Conclusion: prefer "compute-in-warehouse" for production metrics and dashboard latency; use Python/R for advanced modeling and ad-hoc research, then persist results in the warehouse for consumption.

HardTechnical

65 practiced

You need to build features for a churn model using only SQL in the warehouse. Propose a set of features (recency, frequency, monetary, trend) computed using window functions over user activity and sketch SQL for each feature. Also describe how you would validate these features before modeling.

Sample Answer

Situation: You need to build churn features only in-SQL in the warehouse. Below is a compact, production-ready set of features (recency, frequency, monetary, trend) computed with window functions, plus validation steps.

Recency- Feature: days_since_last_activity (as of snapshot_date)- SQL (Postgres syntax):

sql

SELECT
  user_id,
  DATE '{snapshot_date}' - MAX(event_date) AS days_since_last_activity
FROM events
WHERE event_date <= DATE '{snapshot_date}'
GROUP BY user_id;

Frequency- Feature: events_last_7d, events_last_30d, unique_active_days_30d- SQL (use windowed counts plus filter by window):

sql

SELECT
  user_id,
  SUM(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '6 days' AND DATE '{snapshot_date}' THEN 1 ELSE 0 END) AS events_last_7d,
  SUM(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' THEN 1 ELSE 0 END) AS events_last_30d,
  COUNT(DISTINCT CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' THEN event_date END) AS unique_active_days_30d
FROM events
WHERE event_date <= DATE '{snapshot_date}'
GROUP BY user_id;

Monetary- Feature: spend_last_30d, avg_order_value_last_30d

sql

SELECT
  user_id,
  SUM(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' THEN amount ELSE 0 END) AS spend_last_30d,
  CASE WHEN COUNT(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' AND amount>0 END) = 0 THEN 0
       ELSE SUM(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' THEN amount ELSE 0 END)
            / NULLIF(COUNT(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' AND amount>0 END),0)
  END AS aov_last_30d
FROM events
GROUP BY user_id;

Trend- Feature: velocity (events/week slope), spend_growth (pct change 30d vs prior 30d)- SQL (example computing percent change and slope via linear regression approximation using window aggregates):

sql

WITH per_day AS (
  SELECT user_id, event_date,
         COUNT(*) AS events,
         SUM(amount) AS spend
  FROM events
  WHERE event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '89 days' AND DATE '{snapshot_date}'
  GROUP BY user_id, event_date
),
agg AS (
  SELECT
    user_id,
    AVG(events) AS avg_events_90d,
    -- simple trend: compare avg events last 14d vs prior 14d
    AVG(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '13 days' AND DATE '{snapshot_date}' THEN events END) AS avg_events_14d,
    AVG(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '27 days' AND DATE '{snapshot_date}' - INTERVAL '14 days' THEN events END) AS avg_events_prev_14d,
    SUM(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '29 days' AND DATE '{snapshot_date}' THEN spend ELSE 0 END) AS spend_30d,
    SUM(CASE WHEN event_date BETWEEN DATE '{snapshot_date}' - INTERVAL '59 days' AND DATE '{snapshot_date}' - INTERVAL '30 days' THEN spend ELSE 0 END) AS spend_prior_30d
  FROM per_day
  GROUP BY user_id
)
SELECT
  user_id,
  avg_events_90d,
  avg_events_14d,
  avg_events_prev_14d,
  CASE WHEN avg_events_prev_14d IS NULL OR avg_events_prev_14d = 0 THEN NULL ELSE (avg_events_14d - avg_events_prev_14d) / avg_events_prev_14d END AS events_pct_change,
  CASE WHEN spend_prior_30d = 0 THEN NULL ELSE (spend_30d - spend_prior_30d) / spend_prior_30d END AS spend_pct_change
FROM agg;

Session / engagement features (session_count_30d, avg_session_length) can be derived by partitioning events by user and session_id or using lead/lag to detect session breaks.

Why these choices- Recency often strongest churn predictor.- Frequency captures engagement intensity.- Monetary captures economic value and retention incentives.- Trend captures momentum — declining usage signals imminent churn.

Validation before modeling1. Sanity checks: null/zero rates, percentiles, outliers, distribution plots (histograms) exported to BI tool.2. Correlations: compute pairwise correlation and VIF to detect multicollinearity.3. Predictive power: build quick baseline model (logistic or XGBoost) on a holdout and measure AUC, precision@k, lift; compute feature importance / SHAP.4. Time leakage check: ensure features only use data <= snapshot_date; perform backtesting with multiple snapshots (anchored rolling windows).5. Stability: population stability index (PSI) across time slices and cohort analysis by acquisition date.6. Missingness and imputation test: evaluate model sensitivity when imputing zeros vs mean.7. Ablation: remove one feature group at a time (recency/frequency/monetary/trend) to measure marginal value.

These SQL patterns and validations let you create robust, warehouse-native churn features and ensure they are predictive and stable for BI and modeling.

MediumTechnical

62 practiced

Explain how window functions interact with GROUP BY, aggregate functions, WHERE and HAVING clauses. Provide an example where attempting to filter on a window function result in WHERE fails, and show how to correctly filter by using a subquery or CTE.

Sample Answer

Window functions are computed after rows are filtered by WHERE and after GROUP BY/HAVING aggregation has occurred; they run as part of SELECT (and ORDER BY) evaluation and operate over the result set (or each group partition) without reducing rows themselves. Key implications:- WHERE filters input rows before aggregation and before any window functions — you cannot reference a window function in WHERE.- GROUP BY + aggregate functions produce one row per group; window functions can then operate on those group-level rows (so you can use aggregates inside window functions).- HAVING filters groups after aggregation but still before window functions are computed in the SELECT phase; you cannot use a window function inside HAVING either.

Example showing WHERE failing (attempt to filter by row_number):

sql

-- This will fail: row_number is not available in WHERE
SELECT
  user_id,
  order_date,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders
WHERE rn = 1;  -- Error: rn unknown in WHERE

Correct approaches: use a subquery or CTE to compute the window function first, then filter the outer query.

Using a CTE:

sql

WITH ranked AS (
  SELECT
    user_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT user_id, order_date, total_amount
FROM ranked
WHERE rn = 1;  -- works: filtering after window computed

Using a subquery:

sql

SELECT user_id, order_date, total_amount
FROM (
  SELECT
    user_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

Notes:- If you need group-level aggregates inside a window, aggregate in an inner query (GROUP BY), then apply window functions on that result.- HAVING cannot reference window columns either — compute window results in a subquery/CTE and then apply HAVING-like filters in an outer WHERE (or use outer aggregation if appropriate).

MediumTechnical

65 practiced

Implement SQL to compute N-day active users (e.g., 7-day active users) given events(user_id, event_date). Show how to compute DAU, WAU (7-day active users), MAU (30-day active users), and retention ratios like DAU/WAU. Discuss efficiency when computing distinct users over sliding windows.

Sample Answer

Approach: first reduce events to one row per user per day (daily active users). Then compute DAU directly from that table; compute WAU/MAU as distinct users in the trailing 7/30-day window per reference date. For performance, prefer pre-aggregated daily_user table + approximate distinct (HLL/APPROX_COUNT_DISTINCT) or bitmap ORs for sliding-window distincts.

Example (ANSI SQL / works in BigQuery, Postgres with minor tweaks):

-- 1) create daily uniques (one row per user per day)

sql

WITH daily_users AS (
  SELECT DISTINCT user_id, CAST(event_date AS DATE) AS day
  FROM events
)

-- 2) DAU per day

sql

SELECT day,
       COUNT(*) AS dau
FROM daily_users
GROUP BY day
ORDER BY day;

-- 3) WAU (7-day active) and MAU (30-day) per day (exact)

sql

SELECT d.day,
       (SELECT COUNT(DISTINCT user_id)
        FROM daily_users u
        WHERE u.day BETWEEN d.day - INTERVAL '6 day' AND d.day) AS wau_7,
       (SELECT COUNT(DISTINCT user_id)
        FROM daily_users u
        WHERE u.day BETWEEN d.day - INTERVAL '29 day' AND d.day) AS mau_30,
       (SELECT COUNT(*) FROM daily_users du WHERE du.day = d.day) AS dau
FROM (
  SELECT DISTINCT day FROM daily_users
) d
ORDER BY d.day;

-- 4) DAU/WAU ratio

sql

SELECT day, dau, wau_7,
       ROUND(1.0 * dau / NULLIF(wau_7,0), 4) AS dau_over_wau
FROM (
  -- join DAU and WAU subqueries or reuse CTE above
) t;

Efficiency discussion and optimizations:- COUNT(DISTINCT) over sliding windows is expensive if computed from raw events each query. Reduce cardinality first: daily_users (one row per user/day) dramatically reduces data scanned.- Use materialized view or daily job that writes daily_user table. Querying sliding windows then scans at most 7 or 30 days of daily_user instead of raw events.- Use approximate distinct functions where acceptable: - BigQuery: APPROX_COUNT_DISTINCT(user_id) or COUNT(DISTINCT user_id) with optimized engines - Postgres: hll extension or hyperloglog aggregates Approximations reduce memory and CPU and are sufficient for dashboards.- For exact fast queries at scale: store per-day bitmaps (RoaringBitmap/Bitmap indexes) and compute OR across day bitmaps then count bits — very fast for sliding windows.- Indexing/partitioning: partition daily_user by day, index by (day, user_id) to speed range scans.- Precompute rolling metrics (e.g., compute WAU for each day in a daily batch) and store in a metrics table for dashboard reads — avoids repeated heavy aggregations.- Consider incremental updates: for new day, compute WAU by combining previous 6 days + new day's daily_user rather than recomputing full history.

Edge cases:- Timezones: normalize event_date to a business timezone before grouping.- Multiple events per day: handled by DISTINCT.- Nulls and empty windows: guard division by zero when computing ratios.

This pattern (daily dedupe → window distinct via limited-range scans → approximate or bitmap acceleration → materialization) balances accuracy and performance for BI dashboards.

MediumTechnical

58 practiced

Given time-series metrics(user_id, metric_date date, value numeric), write SQL to flag anomalies where the current value is greater than mean + 3 * stddev over the past 30 days for that user. Include handling for small sample sizes and explain any assumptions.

Sample Answer

Approach: for each user/date compute the rolling 30-day mean, sample stddev, and count of prior days; flag current row as anomaly if value > mean + 3*stddev. For small samples use a minimum-history rule (e.g., min_count = 5); if insufficient data, fall back to a robust rule (value > median + 3*MAD) or mark as "insufficient_history".

Example (Postgres-compatible):

sql

WITH rolling AS (
  SELECT
    user_id,
    metric_date,
    value,
    COUNT(value)  OVER (
      PARTITION BY user_id
      ORDER BY metric_date
      RANGE BETWEEN INTERVAL '30 days' PRECEDING AND INTERVAL '1 day' PRECEDING
    ) AS prior_count,
    AVG(value)    OVER (
      PARTITION BY user_id
      ORDER BY metric_date
      RANGE BETWEEN INTERVAL '30 days' PRECEDING AND INTERVAL '1 day' PRECEDING
    ) AS prior_mean,
    STDDEV_SAMP(value) OVER (
      PARTITION BY user_id
      ORDER BY metric_date
      RANGE BETWEEN INTERVAL '30 days' PRECEDING AND INTERVAL '1 day' PRECEDING
    ) AS prior_stddev
  FROM metrics
)
SELECT
  user_id,
  metric_date,
  value,
  prior_count,
  prior_mean,
  prior_stddev,
  CASE
    WHEN prior_count >= 5 AND prior_stddev IS NOT NULL AND prior_stddev > 0
      AND value > prior_mean + 3 * prior_stddev THEN 'anomaly'
    WHEN prior_count >= 5 AND (prior_stddev IS NULL OR prior_stddev = 0)
      AND value > prior_mean + 0.0001 THEN 'anomaly_static' -- extreme jump from flat baseline
    WHEN prior_count < 5 THEN 'insufficient_history'
    ELSE 'ok'
  END AS status
FROM rolling
ORDER BY user_id, metric_date;

Key points / assumptions:- Uses prior 30 days excluding current day. Adjust RANGE semantics if metric_date has gaps; if DB doesn't support RANGE with dates, use a lateral join or correlated subquery.- Minimum prior_count = 5 avoids noisy stats; choose threshold based on product tolerance.- Handle stddev = 0 (flat series) by treating any meaningful jump as anomaly.- Alternatives: use median + k*MAD for robustness to outliers, or percentile-based thresholds (95/99th).- Document chosen thresholds in dashboards so stakeholders understand sensitivity.

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.