How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

What difficulty level is this interview question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at WeRide.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at WeRide during technical interviews.

Compute window averages and merge intervals

Quick Overview

Compute window averages and merge intervals evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.

Compute window averages and merge intervals

You are given two independent pandas tasks.

1. Centered sliding-window average

Input DataFrame df with columns:
- row_id INT — unique row-order key, already sorted ascending
- value FLOAT — numeric value
Given an integer k >= 0 , add a new column window_avg such that for each row i : window_avg(i) = average(value[i-k], ..., value[i], ..., value[i+k]) (the average of the current value plus the k values before it and the k values after it).
Only compute the average when the row has at least k previous rows and k later rows. For the first k rows and the last k rows, set window_avg = -1 .
Return the original DataFrame with the new column window_avg (columns row_id , value , window_avg ).

2. Merge autonomous-driving event intervals

Input DataFrame of time intervals, each representing an event generated by an autonomous vehicle. Depending on the variant you are handed, the schema is one of:
- vehicle_id STRING, start_ts TIMESTAMP, end_ts TIMESTAMP, or
- vehicle_id STRING, event_type STRING, start_ts TIMESTAMP, end_ts TIMESTAMP (group by both), or
- a single table intervals with start INT and end INT.
Assume all timestamps share the same timezone and start <= end for every row.
Within each group (per vehicle_id , or per (vehicle_id, event_type) when event_type is present — the whole table is one group when there is no grouping column), merge intervals that overlap or touch: a later interval is merged into the current one when next.start <= current.end .
Return one row per merged interval with the group key(s) plus merged_start / merged_end (named merged_start_ts / merged_end_ts when the inputs are timestamps), sorted by the group key(s) then merged_start .

Write pandas code for both tasks.

Constraints & Assumptions

Preserve the scope, facts, inputs, and requested outputs from the prompt above.
If the prompt leaves a detail unspecified, state a reasonable assumption before relying on it.
Keep the answer interview-ready: concise enough to present, but concrete enough to implement or evaluate.

Clarifying Questions to Ask Guidance

Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
Define the grain of each intermediate result before aggregating.
State expected output columns and ordering explicitly.

What a Strong Answer Covers Guidance

A query or pandas plan that matches the requested output grain.
Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
A brief explanation of why the result is correct and how it handles edge cases.
Performance notes, indexes/partitioning, and validation queries when relevant.

Follow-up Questions Guidance

How would you test the query on a tiny hand-built dataset?
What changes if duplicate events or late-arriving data are present?
Which indexes, clustering, or partitions would help at production scale?

Quick Overview

Compute window averages and merge intervals

You are given two independent pandas tasks.

1. Centered sliding-window average

Input DataFrame df with columns:

row_id INT — unique row-order key, already sorted ascending
value FLOAT — numeric value

Given an integer k >= 0 , add a new column window_avg such that for each row i : window_avg(i) = average(value[i-k], ..., value[i], ..., value[i+k]) (the average of the current value plus the k values before it and the k values after it).

Only compute the average when the row has at least k previous rows and k later rows. For the first k rows and the last k rows, set window_avg = -1 .

Return the original DataFrame with the new column window_avg (columns row_id , value , window_avg ).

2. Merge autonomous-driving event intervals

Input DataFrame of time intervals, each representing an event generated by an autonomous vehicle. Depending on the variant you are handed, the schema is one of:

vehicle_id STRING, start_ts TIMESTAMP, end_ts TIMESTAMP, or
vehicle_id STRING, event_type STRING, start_ts TIMESTAMP, end_ts TIMESTAMP (group by both), or
a single table intervals with start INT and end INT.

Assume all timestamps share the same timezone and start <= end for every row.

Within each group (per vehicle_id , or per (vehicle_id, event_type) when event_type is present — the whole table is one group when there is no grouping column), merge intervals that overlap or touch: a later interval is merged into the current one when next.start <= current.end .

Return one row per merged interval with the group key(s) plus merged_start / merged_end (named merged_start_ts / merged_end_ts when the inputs are timestamps), sorted by the group key(s) then merged_start .

Write pandas code for both tasks.

Constraints & Assumptions

Preserve the scope, facts, inputs, and requested outputs from the prompt above.

If the prompt leaves a detail unspecified, state a reasonable assumption before relying on it.

Keep the answer interview-ready: concise enough to present, but concrete enough to implement or evaluate.

Clarifying Questions to Ask Guidance

Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.

Define the grain of each intermediate result before aggregating.

State expected output columns and ordering explicitly.

What a Strong Answer Covers Guidance

A query or pandas plan that matches the requested output grain.

Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.

A brief explanation of why the result is correct and how it handles edge cases.

Performance notes, indexes/partitioning, and validation queries when relevant.

Follow-up Questions Guidance

How would you test the query on a tiny hand-built dataset?

What changes if duplicate events or late-arriving data are present?

Which indexes, clustering, or partitions would help at production scale?

Compute window averages and merge intervals

Quick Overview

Compute window averages and merge intervals

Compute window averages and merge intervals

Constraints & Assumptions

Clarifying Questions to Ask Guidance

What a Strong Answer Covers Guidance

Follow-up Questions Guidance

Write your answer

Compute window averages and merge intervals

Quick Overview

Compute window averages and merge intervals

Compute window averages and merge intervals

Constraints & Assumptions

Clarifying Questions to Ask Guidance

What a Strong Answer Covers Guidance

Follow-up Questions Guidance

Write your answer