Find orders exceeding $1,000 in the last 30 days.

To find orders exceeding $1,000 in the last 30 days, the most direct approach for historical data involves a SQL query. For real-time detection, a streaming solution leveraging watermarks and window aggregations is necessary.

Mechanics and Why

Batch Processing (SQL):
The core SQL uses a WHERE clause to filter amount > 1000 and order_date >= CURRENT_DATE - INTERVAL '30 days'. This ensures only recent, high-value orders are considered. CURRENT_DATE (or GETDATE(), NOW() depending on the specific SQL dialect) provides the current date, and subtracting an interval correctly defines the 30-day lookback period.

For large datasets, performance is critical. An index on (order_date, amount) or (amount, order_date) significantly speeds up this query by allowing the database to quickly locate relevant rows without a full table scan. In systems like Snowflake, this translates to efficient micro-partition pruning and leveraging clustering keys. For very large tables, partitioning the data by order_date (e.g., daily or monthly partitions in Delta Lake or Hive) can further reduce the amount of data scanned. Parameterizing dates in production queries is also good practice for reusability and testing.

Streaming Processing (Real-time):
For real-time detection, a streaming approach is required. This involves using watermarks to handle late-arriving data and window aggregations (specifically a 30-day tumbling or sliding window) to process events within the specified time frame. A streaming engine (e.g., Apache Spark Structured Streaming, Apache Flink) would ingest data from a message queue like Kafka, maintain state for orders within the window, and emit results as orders exceed the threshold. The transaction log in systems like Delta Lake can provide an idempotent sink for streaming results.

Example and Trade-offs

This SQL snippet demonstrates the batch approach:

SELECT
    order_id,
    customer_id,
    amount,
    order_date
FROM
    orders
WHERE
    amount > 1000
    AND order_date >= CURRENT_DATE - INTERVAL '30 days';

This batch query is suitable for daily reports or historical analysis. For immediate alerts or dashboards requiring sub-minute latency, a streaming solution is necessary, trading off eventual consistency for low latency.

In the interview, also mention considerations like data volume, required latency, and the specific capabilities of the data platform (e.g., cloud data warehouses vs. streaming engines) to demonstrate a holistic understanding.