SQL, Pandas, And dplyr Data Manipulation

What's being tested

These problems test vectorized conditional logic and robust dtype-aware feature engineering in pandas, plus SQL skills for aggregation, deduplication, and window-based analytics. Interviewers probe whether you can write correct, efficient transformations (no row-wise loops), handle NULL/NaN semantics, and reason about precedence and deduplication in event data.

Patterns & templates

• Use np.select for multi-condition column creation with strict precedence; fallback handled by the default array, O(n) time.

• For simple two-way logic prefer np.where(cond, a, b) or Series.where/.mask to preserve dtypes and NaN semantics.

• Convert and validate dates with pd.to_datetime(..., errors='coerce') then use .dt accessors; cast floats carefully with astype(float).

• Deduplicate events with ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) then filter row_number = 1; ties need deterministic tie-breaker.

• Conditional counts in SQL: use SUM(CASE WHEN cond THEN 1 ELSE 0 END) or COUNTIF(cond) where supported for clarity.

• Month-over-month use LAG(value) OVER (PARTITION BY country ORDER BY month) then compute (value - prev)/prev; guard divide-by-zero.

• String-id composition: COALESCE(user_id, '') || '-' || COALESCE(email, '') or CONCAT_WS('-', ...) and treat NULLs explicitly.

Common pitfalls

Pitfall: Using chained np.where for many conditions accidentally flips precedence; prefer np.select for clarity and correctness.

Pitfall: Casting to int before filling NaNs loses null semantics; fill or use nullable dtypes (Int64) instead.

Pitfall: Not deduplicating repeated user actions inflates counts—always show the dedupe rule and tie-breaker you applied.