Write a query to find minimum age.

The most efficient way to find the minimum age is by using the MIN() aggregate function. An alternative, though generally less performant, is to sort the data and limit to the first record.

Mechanics and Why

The MIN(age) approach directly computes the minimum value across the age column. Database engines are highly optimized for aggregate functions like MIN(), often performing a single pass over the relevant data or leveraging indexes for near-instant retrieval. This makes it the standard and most performant method for this task.

Conversely, ORDER BY age ASC LIMIT 1 requires the database to sort the entire dataset (or at least enough to satisfy the LIMIT clause) before returning the first record. Sorting is a resource-intensive operation, especially on large tables, involving memory allocation and potentially disk I/O. While an index on age can speed up both methods, MIN() typically benefits more directly from index structures (e.g., B-trees) by directly accessing the smallest value, whereas ORDER BY still incurs sorting overhead if the index isn't perfectly aligned with the sort order.

Key Trade-offs and Production Considerations

A critical consideration is NULL handling. The MIN() aggregate function, by SQL standard, ignores NULL values. If NULL should be treated as a valid minimum (e.g., equivalent to 0 or a specific placeholder), you must explicitly handle it. For instance, COALESCE(age, 0) would substitute NULL ages with 0 before finding the minimum.

SELECT MIN(COALESCE(age, 0)) AS min_age_with_null_as_zero
FROM people;

In production systems, MIN() scales far better. On data platforms like Snowflake, MIN() can leverage metadata stored in micro-partitions, potentially returning the minimum without scanning any data blocks if the column is part of a clustering key. For distributed systems like Spark, MIN() still involves less data movement and computation than a full sort for LIMIT 1.

In the interview, also mention the importance of understanding data volume, index availability, and the specific semantics of NULL values in the dataset.