Data Transformation and Processing Logic Questions

Direct answer

CSV is the lowest common denominator (universally readable, no schema enforcement, row-oriented, poor compression); JSON adds nested structure at the cost of size and columnar-query performance; Parquet and Avro are both binary and schema-aware, but Parquet is columnar (fast for analytical scans and aggregations) while Avro is row-oriented (better for write-heavy streaming and full-row reads), which is the main axis to reason from when picking one.

Structured elaboration

1. CSV: human-readable, universally supported, but no native types (everything is a string until parsed), no compression, and a classic "small files" problem at scale; fine for interchange with external, less-technical parties, poor as an internal storage format at any real volume.
2. JSON: supports nested structure natively, which CSV cannot express at all, but is verbose (repeats field names in every record) and, like CSV, is row-oriented text, so it doesn't get the query-time benefits of a columnar format.
3. Parquet: columnar, so an analytical query that only touches 3 of 50 columns only reads those 3 columns' data off disk; strong compression from storing like-typed values together; supports schema evolution (adding/removing columns) reasonably well; the natural default for anything that will be queried by column-selective analytical workloads.
4. Avro: row-oriented but still binary and schema-aware (with a schema embedded or in a registry); better suited to streaming/write-heavy pipelines where you're appending whole records rather than scanning by column, and where schema evolution rules (backward/forward compatibility) are a first-class concern.
5. Cross-file type mismatches: when reading many partitioned Parquet/ORC files where the SAME column has drifted type across files (int in older files, string in newer), detect this at read time (most engines surface a clear error or a silent up-cast depending on configuration) and prevent it going forward with an enforced, versioned schema at write time rather than letting each writer infer its own types independently.

Worked example

A pipeline ingesting streaming clickstream events (write-heavy, whole-record reads by downstream consumers) writing to Kafka is well-served by Avro with a schema registry; the SAME data, once landed in a data lake and queried by analysts who mostly aggregate a handful of columns across billions of rows, is far better served by Parquet, which is why a "raw ingestion in Avro, analytical storage in Parquet" pattern is common: each format is doing the job it's actually good at, rather than one format being used everywhere out of inertia.

Trade-offs and pitfalls

• Storing frequently-updated, narrow (few-column) records in Parquet, optimized for wide analytical scans, is a mismatch; a row-oriented format or a proper OLTP store fits that access pattern better.
• CSV's total lack of type enforcement means a schema/type bug (a date column suddenly containing a non-date string) isn't caught until something downstream tries to parse it, often much later and far from the actual source of the bug.
• Reconciling a cross-file type mismatch after the fact (rather than preventing it at write time) usually means an explicit CAST or schema-normalization pass before the data can be safely unioned across the affected files; silently letting the query engine auto-coerce types can produce a subtly wrong result (e.g. a numeric column partially read as its string representation) rather than a loud error.

Direct answer

Build an explicit calendar spine (one row per entity per day in the range you care about) and LEFT JOIN the real activity onto it, so days with zero activity appear as zero-valued rows in the window function instead of silently disappearing from the average's denominator.

Structured elaboration

1. Why a spine is necessary: a plain AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) only sees the rows that exist; if an entity had no activity on 3 of the last 7 days, those days simply aren't in the input, and the average silently becomes an average over 4 real days, not 7, which is a different (and usually wrong) number than what "7-day rolling average" is supposed to mean.
2. Building the spine: generate one row per (entity, date) for every date in range using a date-generating function, then LEFT JOIN the real transaction/aggregate table onto it, coalescing missing amounts to zero.
3. ROWS vs RANGE framing: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts the previous 7 physical rows in sort order; RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW counts by the actual date value, which matters if there could ever be more than one row per entity per day, ROWS would silently include the wrong number of calendar days in that case, RANGE would not.
4. Extending to a full per-user feature table: the same window-function pattern generalizes to computing several rolling metrics at once (last-transaction-date, a rolling 30-day count, a rolling 90-day average) in a single query, which is worth mentioning as the natural next step interviewers often ask for.

Worked example

WITH spine AS (
  SELECT 1 AS store_id, d::DATE AS d
  FROM range(DATE '2024-01-01', DATE '2024-01-05', INTERVAL 1 DAY) t(d)
),
joined AS (
  SELECT spine.store_id, spine.d, COALESCE(s.revenue,0) AS revenue
  FROM spine LEFT JOIN sales s ON s.store_id = spine.store_id AND s.sale_date = spine.d
)
SELECT store_id, d,
  AVG(revenue) OVER (PARTITION BY store_id ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS roll_avg
FROM joined ORDER BY d;

Against sales of 100 on 01-01, 50 on 01-02, and 80 on 01-04 (01-03 has no sales at all), this correctly produces a rolling average of 57.5 on 01-04, verified: (100+50+0+80)/4, treating the missing day as a real zero rather than skipping it, which is the crux of the whole question.

Trade-offs and pitfalls

• Generating a full calendar spine adds a real cost proportional to (number of entities x number of days in range); for a very large number of entities, it's often cheaper to generate the spine only for entities with at least one row somewhere in a wider window, rather than truly every possible entity.
• The choice of "zero" versus NULL for a missing day is itself a decision: zero is right for revenue/counts, but wrong for something like "average order size" where a day with zero orders should arguably be excluded from that particular average, not counted as a zero.
• ROWS BETWEEN with gaps in the underlying data (no spine at all) silently computes a rolling average over however many rows happen to exist, which can look plausible and still be wrong; this is the single most common bug in this pattern.

Direct answer

An unexpected row-count increase after a join almost always means the join key is not unique on at least one side, so one row on the "one" side is fanning out against multiple matching rows on the other side. The fix is to find which side has the duplicate key values, decide whether that duplication is real data or a bug, and pre-aggregate before joining if it's real.

Structured elaboration

1. Check first: count rows in each input table, then check whether the join key is actually unique on each side (SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1). This single query almost always finds the cause.
2. Sample the fan-out: pull a few of the order_ids that have more than one matching refund row and look at them directly. Is it two legitimate partial refunds on the same order, or a genuine duplicate/bug in the refunds table?
3. Decide the fix: if the duplication is legitimate (partial refunds are a real, expected pattern), pre-aggregate the many-side table (SUM(refund_amount) GROUP BY order_id) before joining, so the join is one-to-one; if it's a bug (accidental double-insert), fix it upstream instead of working around it downstream.
4. Explaining it to a non-technical stakeholder: "each order can have more than one refund, and when we combine the two tables, each of those extra refunds adds an extra row for that order, that's why the count went up even though nothing was duplicated in the original orders."
5. A related but distinct hygiene issue: two tables can share a column name (both have an id or created_at) that means something different in each source; joining them naively can silently produce a nonsensical combined column, or a name collision that overwrites one column with the other depending on the SQL dialect's join-column handling. The fix here is renaming or namespacing columns before the join, and preserving provenance (which source each value came from) rather than letting the join silently pick one.

Worked example

Orders (10,000 rows) LEFT JOIN refunds (200 rows) on order_id, where a few orders have more than one refund row:

SELECT COUNT(*) FROM orders o LEFT JOIN refunds r ON o.order_id = r.order_id;

With orders (1,100),(2,50),(3,75) and refunds (1,20),(1,30),(2,10) (order 1 has two refund rows), the join returns 4 rows, not 3, verified by execution: order 1 fans out into 2 result rows, order 2 into 1, order 3 into 1 (with NULL refund columns) - 2+1+1 = 4. The "missing" row isn't missing at all, it's the extra refund row for order 1 that explains the count increase.

Trade-offs and pitfalls

• Pre-aggregating the many-side table before joining fixes the row-count symptom but can hide a genuine data-quality issue (duplicate refund rows that shouldn't exist); confirm which case you're in before "fixing" it.
• A COUNT(DISTINCT o.order_id) after the join will look correct even when the join is silently fanning out revenue or other summed metrics; row-count sanity checks alone don't catch every symptom of a bad join, check the aggregated business metric too.
• Renaming colliding columns (e.g. o.created_at AS order_created_at, r.created_at AS refund_created_at) before the join is cheap and prevents an entire class of silent-overwrite bugs; do it by convention, not only when a collision is noticed.

Direct answer

A new session starts whenever the gap between two consecutive events for the same user exceeds the configured timeout; the classic implementation looks at the previous event's timestamp with LAG, flags a new session whenever that gap is too large, and turns those flags into a session id with a running sum, this is the well-known "gaps and islands" pattern.

Structured elaboration

1. LAG to see the previous event: for each event, look at the same user's immediately preceding event's timestamp.
2. Flag a new session: if there is no previous event, or the gap since it exceeds the timeout, that row starts a new session.
3. Turn flags into ids: a running SUM() of the new-session flags, computed in timestamp order per user, gives every event in the same session the same session id (the sum only increments at a session boundary).
4. Aggregate per session: group by (user, session id) to get session start, end, and event count.
5. Both SQL and pandas need the same care about unsorted input: neither LAG nor a manual gap-check works correctly unless the events are actually ordered by timestamp first; an unsorted input is the most common source of a wrong session assignment.
6. A related variant: instead of a gap-based session boundary, some raw data pairs explicit start/end events per logical unit (e.g. a trip_start and a trip_end event) that need to be assembled into one row; the assembly technique differs (a self-join or a pivot on event_type rather than a LAG-based gap check) but it's solving the same underlying "build one row out of multiple related raw events" problem.

Worked example

WITH lagged AS (
  SELECT *, LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_ts
  FROM ev
),
flagged AS (
  SELECT *, CASE WHEN prev_ts IS NULL OR event_ts - prev_ts > INTERVAL 30 MINUTE THEN 1 ELSE 0 END AS is_new_session
  FROM lagged
),
sessed AS (
  SELECT *, SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts) AS session_id
  FROM flagged
)
SELECT user_id, session_id, MIN(event_ts) AS start, MAX(event_ts) AS end, COUNT(*) AS n
FROM sessed GROUP BY user_id, session_id;

Verified against a user with 4 events at 10:00, 10:10, 10:50, 11:00: the 40-minute gap between 10:10 and 10:50 correctly starts a new session, so the 4 events split into exactly 2 sessions (10:00-10:10, and 10:50-11:00), not one continuous session, while a second user with a single event forms their own single-event session.

The same competency in pandas:

import pandas as pd

def sessionize(events_df, user_col='user_id', ts_col='event_ts', timeout_minutes=30):
    df = events_df.sort_values([user_col, ts_col]).copy()
    gap = df.groupby(user_col)[ts_col].diff()
    new_session = gap.isna() | (gap > pd.Timedelta(minutes=timeout_minutes))
    df['session_id'] = new_session.groupby(df[user_col]).cumsum()
    return df

This mirrors the SQL logic exactly: sort first, compute the gap with .diff() (pandas' equivalent of LAG plus a subtraction), and turn the boolean flags into session ids with a cumulative sum per user.

Trade-offs and pitfalls

• Forgetting to sort before computing the gap (in either SQL, if the window's ORDER BY is wrong, or in pandas, if .sort_values() is skipped) is the single most common bug, and it fails silently, producing a session assignment that looks plausible but is wrong.
• Timezone-naive timestamps mixed with timezone-aware ones will compute a gap that is off by whatever the timezone offset is; normalize timestamps to a single timezone (ideally UTC) before sessionizing, not during.
• The 30-minute (or whatever) timeout is itself a business decision with real consequences for downstream metrics like average session length; changing it retroactively changes every historical session boundary, which needs to be communicated, not silently patched.

Direct answer

Without a shared unique identifier, entity resolution comes down to three ordered steps: normalize the fields you'll compare, use blocking to cut down the astronomically large number of candidate pairs to a tractable set, then score each remaining candidate pair with a similarity metric and a chosen confidence threshold that decides auto-merge versus human review.

Structured elaboration

1. Normalization first: lowercase, strip punctuation and whitespace, expand common abbreviations, so that "Jon Smith" and "JON SMITH" compare as identical inputs to the similarity step, not as a similarity problem in themselves.
2. Blocking: comparing every pair of records is $O(n^2)$, infeasible past a few hundred thousand records; blocking groups records into buckets that are cheap to compute (first letter of last name, postal code, a phonetic key like Soundex) so only records in the SAME bucket are ever compared to each other. This sacrifices some recall (a true match split across two blocks is missed) for tractability.
3. Similarity scoring: within a block, score each pair with a string-similarity metric (Levenshtein edit distance, Jaro-Winkler for names, or token-set methods for addresses); at truly large scale, MinHash/LSH approximates "which pairs are similar" without materializing all pairwise comparisons, by hashing records into buckets where similar records collide with high probability.
4. Confidence threshold and workflow: pairs scoring above a high threshold auto-merge; pairs in an ambiguous middle band route to a human reviewer with the evidence shown; pairs below a low threshold are left as distinct. Where you set these two thresholds is a precision/recall trade-off made explicit, not hidden inside a single "is duplicate" boolean.
5. At true scale (hundreds of millions of records), an exact pairwise approach is replaced by LSH/MinHash for candidate generation, with the same blocking-then-scoring logic applied within the much smaller candidate set it produces; a hybrid architecture (fast approximate first pass, then exact reconciliation on the surviving candidates) is common precisely because the two techniques catch different failure modes.

Worked example

Records: "Jon Smith", "John Smith", "Jonathan Smith", "Amy Lee", "Amy Le". Using first-letter-of-name blocking and a sequence-similarity ratio (verified computationally):

'Jon Smith' vs 'John Smith'      similarity = 0.95
'Jon Smith' vs 'Jonathan Smith'  similarity = 0.78
'John Smith' vs 'Jonathan Smith' similarity = 0.83
'Amy Lee' vs 'Amy Le'            similarity = 0.92

At an auto-merge threshold of 0.85, "Jon Smith"/"John Smith" and "Amy Lee"/"Amy Le" merge automatically as true near-duplicates, while both pairs involving "Jonathan Smith" (0.78 and 0.83) fall below the bar and are routed to human review rather than silently merged or silently kept apart. This concretely shows why the threshold is a business decision: a lower threshold would auto-merge "Jonathan Smith" into "Jon Smith" too, which may or may not be correct.

Trade-offs and pitfalls

• Blocking is itself a recall risk: if the blocking key is wrong for one of a true pair (a typo in the very field used for blocking), that pair is never even compared. Multiple, differently-keyed blocking passes mitigate this at added cost.
• A pure precision-maximizing threshold (very high) leaves many true duplicates unmerged, which quietly inflates downstream counts (more "unique customers" than really exist); a pure recall-maximizing threshold (very low) risks merging two different real people, which is often the more damaging error since it can misattribute one person's history to another.
• A debugging variant of this problem: two reporting systems disagreeing on a metric by a visible percentage is frequently traced to exactly this kind of inconsistency, one system's fuzzy-matching rules differing from the other's, which is worth naming as a root cause an interviewer may be probing for.