String and Date Manipulation Questions

Covers practical skills for manipulating textual and temporal data. Typical expectations include string operations such as concatenation, substring extraction, case transformation, pattern replacement, and trimming, as well as date and time operations such as truncation, extracting date parts, computing differences, adding intervals, formatting, and handling time zones and daylight saving edge cases. Candidates may be asked to write or explain queries and small code snippets, reason about correctness and performance, and discuss pitfalls such as locale formats, leap seconds, and ambiguous input.

HardTechnical

86 practiced

During the fall DST transition (clocks go back), some timestamps are ambiguous (e.g., '2023-11-05 01:30' occurs twice in 'America/New_York'). As a BI engineer, describe how you'd detect, store, and disambiguate such events so reporting doesn't double-count or misorder events. Include storage recommendations and any data model changes.

Sample Answer

Situation: During fall DST rollback, local clock times repeat (e.g., 2023-11-05 01:30 in America/New_York happens twice). If treated only as naive local timestamps, reports can double-count or misorder events.

Detection:- In ETL, detect ambiguous local times by attempting to convert local timestamp + timezone to UTC using a timezone library that exposes ambiguity (pytz/zoneinfo raise or provide fold/is_dst metadata).- Flag rows where the conversion is ambiguous (fold flag in PEP 495 / zoneinfo).

Storage & data model changes (recommended):- Always store a canonical UTC timestamp: event_ts_utc TIMESTAMP WITH TIME ZONE.- Also persist original local values for audit: event_ts_local TIMESTAMP WITHOUT TIME ZONE, tz_name VARCHAR, tz_offset_seconds INT, and fold BOOLEAN (or is_ambiguous BOOLEAN).- If events are user/actions that have natural ordering, include a monotonically increasing event_id or sequence_within_local INT to preserve ordering when local wall-clock ties exist.

Example columns:- event_id (PK)- event_ts_utc TIMESTAMPTZ- event_ts_local TIMESTAMP- tz_name TEXT- tz_offset_seconds INT- fold BOOLEAN- seq_within_local INT

SQL example to convert and detect ambiguity (Postgres + zoneinfo):

sql

-- assume local_ts and tz provided
SELECT
  local_ts,
  tz_name,
  -- convert to timestamptz using offset-aware construct
  (local_ts AT TIME ZONE tz_name) AT TIME ZONE 'UTC' as event_ts_utc
FROM events_stage;

(Use application-layer library to detect fold/ambiguity and set fold boolean; in Python zoneinfo, check datetime.fold.)

Disambiguation & reporting rules:- For deduplication: dedupe on a unique business key (event_id or user_action_id) plus event_ts_utc. If no unique id, dedupe using (event_ts_utc, tz_name, seq_within_local) — prefer UTC as canonical.- For ordering: sort by event_ts_utc, then by seq_within_local or event_id to break ties.- For time-bucketed reports (hourly/day): decide whether buckets are local or UTC. If reporting in local time, map event_ts_utc -> local using tz_name and use fold to decide which repeated hour it belongs to; include both offset_seconds and fold in the mapping.- For analytics dashboards: expose both local and UTC times and document how ambiguous times are handled. Provide filters to aggregate by UTC or by local-date with explicit DST-handling.

ETL best practices:- Resolve ambiguity at ingestion: require client to send offset or explicit UTC conversion; if not possible, compute both possibilities, flag and request enrichment or use deterministic rule (prefer earlier/later fold) documented in data contract.- Monitor: generate alerts when ambiguous timestamps are present and count occurrences.

Trade-offs:- Storing extra fields increases storage but is cheap vs. analytical correctness.- Making clients send UTC reduces ambiguity but requires engineering changes.

This approach prevents double-counting (use UTC for uniqueness/dedupe) and preserves business semantics (keep local values, tz, fold, and sequence for audits and correct local reporting).

EasyTechnical

100 practiced

You have an `events` table with columns: (event_id INT, event_ts TIMESTAMP WITH TIME ZONE). Write a PostgreSQL query that returns event_id plus extracted fields: event_date (date), year, month, day, hour, and ISO week number for each timestamp. Show how EXTRACT, DATE_PART and DATE_TRUNC differ and when you'd use each for BI aggregations.

Sample Answer

To return event_id plus extracted fields including ISO week, you can use EXTRACT or DATE_PART (they're synonyms) and DATE_TRUNC for bucketed timestamps. Example:

sql

SELECT
  event_id,
  (event_ts AT TIME ZONE 'UTC')::date AS event_date,          -- date only (normalized to UTC if needed)
  EXTRACT(year FROM event_ts)    AS year,
  EXTRACT(month FROM event_ts)   AS month,
  EXTRACT(day FROM event_ts)     AS day,
  EXTRACT(hour FROM event_ts)    AS hour,
  EXTRACT(week FROM event_ts)    AS iso_week,                 -- ISO week number (1-53)
  DATE_TRUNC('day', event_ts)    AS day_bucket,
  DATE_TRUNC('week', event_ts)   AS week_bucket,              -- week starting Monday (ISO week start)
  DATE_TRUNC('month', event_ts)  AS month_bucket
FROM events;

Key differences and BI guidance:- EXTRACT(field FROM timestamp) and DATE_PART(field, timestamp) return numeric components (year, month, day, hour, week). They are interchangeable; use them when you need scalar parts for grouping, filtering, or labels (e.g., group by EXTRACT(year FROM event_ts), EXTRACT(week FROM ...)).- DATE_TRUNC(unit, timestamp) returns a timestamp rounded down to the unit (e.g., start of day/week/month). Use DATE_TRUNC when you want to bucket events into time intervals for aggregations (GROUP BY DATE_TRUNC('week', event_ts)) or to join by period boundaries.- ISO week nuance: EXTRACT(week FROM ...) follows ISO week numbering; for week start alignment also use DATE_TRUNC('week', ...) which uses ISO (week starting Monday) in Postgres. For business reporting, ensure timezone handling (convert event_ts to the business timezone before casting to date or truncating).Examples:- Count per ISO week: SELECT DATE_TRUNC('week', event_ts AT TIME ZONE 'America/Los_Angeles') AS week_start, COUNT(*) FROM events GROUP BY 1 ORDER BY 1;- Pivot by year/month: GROUP BY EXTRACT(year FROM ...), EXTRACT(month FROM ...).

MediumTechnical

105 practiced

Customer names may include Unicode variants (accents, ligatures). Describe how you would normalize names to a canonical form for deduplication using Python (e.g., NFKC/NFKD), and discuss performance and correctness concerns when running this at scale on millions of rows.

Sample Answer

Approach (brief): produce a canonical "key" per name by applying Unicode normalization, removing diacritics (if appropriate), lowercasing, collapsing punctuation/whitespace, and optionally transliterating. Preserve the original raw name for display and keep provenance. Use NFKC/NFKD for compatibility; NFKD + stripping combining marks removes accents, while NFKC preserves compatibility composites (like ligatures) — combine both depending on needs.

python

import unicodedata
import re

_COMBINING = dict.fromkeys(range(0x300, 0x370))  # common combining diacritics

def canonical_name(name: str, remove_diacritics: bool = True) -> str:
    if not name:
        return ""
    # 1) Normalize to NFKC to fold compatibility characters (ligatures, etc.)
    s = unicodedata.normalize("NFKC", name)
    # 2) Optionally decompose and remove combining marks to strip accents
    if remove_diacritics:
        s = unicodedata.normalize("NFKD", s)
        s = s.translate(_COMBINING)
    # 3) Lowercase, collapse whitespace, strip punctuation (keep hyphens if needed)
    s = s.lower()
    s = re.sub(r"[^\w\s\-]", "", s)         # remove punctuation except hyphen
    s = re.sub(r"\s+", " ", s).strip()      # normalize whitespace
    return s

Correctness concerns:- Removing diacritics can conflate distinct names (e.g., "José" vs "Jose" may be acceptable or not). Decide policy with stakeholders and keep raw value.- Locale-specific rules: casing and character equivalence can vary (Turkish dotted/dotless i). Use locale-aware methods if needed.- Ligatures: NFKC maps ﬂ → fl; NFKD decomposes accents — choose based on business rules.

Performance & scaling:- For millions of rows, avoid per-row Python in a single process. Options: - Push normalization to the database if it supports ICU functions (faster, avoids data transfer). - Use vectorized operations in pandas (apply on chunks) or Polars for better throughput. - Parallelize: chunk data and use multiprocessing, or run in Spark/Databricks where normalization can be applied in map tasks. - Precompute and store canonical key and index it (hash + store) to speed dedupe joins. - Memory: process in streaming/chunks to avoid OOM. - Use compiled libs (PyICU) for locale rules when needed.- Benchmark: measure throughput (rows/sec), CPU, and I/O; test on representative samples.- Validation: build tests for edge cases, sampling-based precision/recall for dedupe, and monitor merge rates and false merges. Keep human review for ambiguous merges.

EasyTechnical

86 practiced

As a BI analyst you must normalize email addresses stored in `users.email` before deduplication. In SQL (choose Postgres or BigQuery) write an expression that: trims whitespace, lowercases the value, replaces any non-breaking spaces, and removes trailing dots before the domain. Provide a sample input and expected normalized output.

Sample Answer

To normalize emails in Postgres: trim leading/trailing whitespace, convert to lowercase, replace non-breaking spaces (U+00A0) with normal spaces, collapse internal extra spaces if desired, and remove any dots immediately before the @ (e.g., "user.@example.com" -> "user@example.com").

Expression (Postgres):

sql

-- normalize users.email in Postgres
lower(
  regexp_replace(
    btrim(            -- trim leading/trailing whitespace
      replace(email, chr(160), ' ')  -- replace non-breaking space (U+00A0) with normal space
    ),
    '\.+@', '@', 'g'  -- remove one-or-more trailing dots immediately before the @
  )
) AS normalized_email

Explanation:- replace(email, chr(160), ' ') substitutes NBSP with a normal space.- btrim(...) trims leading/trailing spaces (including those from the NBSP replacement).- regexp_replace(..., '\.+@', '@', 'g') finds one or more dots immediately preceding the '@' and removes them.- lower(...) converts the entire result to lowercase for consistent deduplication.

Sample inputs and expected outputs:- ' Alice.@Example.COM ' -> 'alice@example.com'- 'Bob\u00A0.Smith@Example.com' (contains NBSP) -> 'bob.smith@example.com' -- NBSP becomes space; trailing dot removed only if directly before @- ' user..name@Domain.COM ' -> 'user..name@domain.com' -- internal consecutive dots preserved; only dots immediately before @ removed- '.leading@EX.com' -> '.leading@ex.com' -- leading dot in local part preserved (we only strip trailing dots before @)

Notes:- If you also want to collapse multiple internal spaces or remove all spaces in local part, add additional regexp_replace steps.- This approach is safe for deduplication where normalization rules are limited to trimming, lowercase, NBSP handling, and removing trailing dots before domain.

MediumTechnical

86 practiced

Describe efficient ways to implement case-insensitive joins on large string columns (e.g., joining `users.email` to `events.actor_email`) in a data warehouse. Provide SQL examples for at least one DB (Postgres or Snowflake) and discuss indexes/collations/functional indexes or hash keys.

Sample Answer

Efficient approach summary:- Avoid row-by-row functions during join; normalize keys once (lower/upper or collation) and use indexed columns or hash keys. Use persistent normalized columns or functional indexes so joins can be executed with index lookup rather than full table scan.

Postgres example (best practice: functional index):- Add a generated column or create a functional index on lower(email) so queries can use an index without modifying runtime values.

sql

-- functional index
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- join using lower(...) matches the index
SELECT u.id, e.event_id
FROM users u
JOIN events e ON lower(u.email) = lower(e.actor_email);

Better: store normalized columns

sql

ALTER TABLE users ADD COLUMN email_normalized text GENERATED ALWAYS AS (lower(email)) STORED;
CREATE INDEX idx_users_email_norm ON users (email_normalized);

ALTER TABLE events ADD COLUMN actor_email_norm text GENERATED ALWAYS AS (lower(actor_email)) STORED;
CREATE INDEX idx_events_actor_email_norm ON events (actor_email_norm);

SELECT u.id, e.event_id
FROM users u
JOIN events e ON u.email_normalized = e.actor_email_norm;

Snowflake example (no functional indexes; use materialized or clustered columns):

sql

-- create normalized columns and cluster by them
ALTER TABLE users ADD COLUMN email_norm STRING DEFAULT LOWER(email);
ALTER TABLE events ADD COLUMN actor_email_norm STRING DEFAULT LOWER(actor_email);
-- cluster by to improve pruning
ALTER TABLE events CLUSTER BY (actor_email_norm);
SELECT u.id, e.event_id
FROM users u JOIN events e ON u.email_norm = e.actor_email_norm;

Indexes / collations / hashes:- Postgres: functional indexes (lower/upper) or use citext extension (case-insensitive text type) to index directly. Collations can make comparisons case-insensitive for specific locales but are less portable.- Snowflake: no traditional indexes; use clustering keys, materialized views, or precomputed normalized columns.- Hash keys: store a fixed-length hash (e.g., md5(lower(email))) and index that for faster comparisons (smaller and fixed size). Use as an additional join key to speed equality checks and reduce index size:

sql

ALTER TABLE users ADD COLUMN email_hash text GENERATED ALWAYS AS (md5(lower(email))) STORED;
CREATE INDEX idx_users_email_hash ON users (email_hash);
-- join on hash then verify equality to avoid collisions
SELECT u.id, e.event_id
FROM users u JOIN events e ON u.email_hash = e.actor_email_hash
WHERE u.email_normalized = e.actor_email_norm;

Trade-offs and notes:- Normalizing columns increases storage but yields fastest joins.- Functional indexes work well in Postgres; citext simplifies code.- Hash-based joins reduce index size but require secondary equality check to handle collisions.- Ensure consistent trimming/Unicode normalization (NFKC) to avoid mismatches.- For BI workloads, materialized columns or ETL-normalized fields are preferred for stable, repeatable performance.

Unlock Full Question Bank

Get access to hundreds of String and Date Manipulation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.