Data Transformation and Processing Logic Questions

A pandas-based transformation runs out of memory or is unacceptably slow on a large dataset (tens of millions of rows or more, on a machine with limited RAM). Systematically walk through how you would diagnose the bottleneck and the options available to fix it, and give one concrete example of a specific fix you'd apply here.

Given a table of sales by date and product category, write a SQL query that pivots the category values into columns, showing monthly revenue per category for the last several months. Handle categories with no sales in a given month (NULL versus zero) and discuss what you would change to make the query portable across SQL dialects.

Design and implement a streaming deduplication component that consumes a stream of (id, timestamp) events and reports whether each event is new or a duplicate within a bounded time window, using bounded memory (an LRU cache or a Bloom filter, your choice). Discuss the correctness trade-off of the approach you chose: can it produce false positives or false negatives, and what does that mean for events that get silently dropped or double-counted?

Design (pseudocode or code) a mechanism that maintains an incremental aggregate (count, sum, mean) per entity as new events arrive, without reprocessing the full history on every update. Describe what state you keep per entity, how you update it on each new event, and how you persist or checkpoint that state so it survives a restart or a new day's batch of delta records.

A table stores raw event data as a JSON field containing nested objects and arrays (for example a user object and a products array). Write a SQL query that extracts the nested scalar fields into named columns and un-nests (explodes) the array into one row per array element, producing a flat, analysis-ready table. Discuss the trade-off between flattening at ingestion time (fixed schema, faster queries) versus parsing the JSON at query time (flexible, slower) as new nested fields appear.