Find Customers With Positive Revenue this Year - Solution & Explanation

Problem Overview: You have a Customers table containing customer_id, name, year, and revenue. The task is simple: return the IDs of customers who generated positive revenue in the year 2021. Only rows where revenue > 0 and year = 2021 should appear in the result.

This is a straightforward filtering problem in database queries. You don’t need joins, aggregation, or window functions. The query just scans the table and keeps rows that satisfy the required conditions.

Approach 1: WHERE Clause Filtering (O(n) time, O(1) space)

The most direct solution uses a WHERE clause to filter rows based on two conditions: the year must be 2021, and the revenue must be greater than zero. SQL evaluates these conditions for each row and returns only the matching customer IDs.

The key idea is predicate filtering. SQL engines iterate through the rows of the table and apply boolean checks: year = 2021 and revenue > 0. Rows that satisfy both predicates are included in the output. Since no additional structures or intermediate tables are required, the query runs with constant extra memory.

This pattern appears frequently in SQL interview questions. Instead of complex logic, the focus is on understanding table schemas and applying precise filters. The resulting query is concise and easy to read:

SELECT customer_id FROM Customers WHERE year = 2021 AND revenue > 0;

Time complexity is O(n) because the database may scan all rows in the table. Space complexity is O(1) since no additional storage beyond the result set is required.

Approach 2: Indexed Filtering (O(log n + k) time with index, O(1) space)

If the table grows large, database engines often rely on indexes to avoid full table scans. An index on year or a composite index like (year, revenue) allows the engine to quickly locate rows for 2021 and then filter by revenue.

With indexing, the engine performs an index lookup rather than scanning every row. This reduces the search portion to roughly O(log n), followed by returning the matching rows k. While indexing is handled internally by the database, understanding how it affects query performance is a key part of practical WHERE clause optimization.

Recommended for interviews: The standard WHERE clause solution is exactly what interviewers expect. The problem tests whether you can read the schema and translate requirements into a precise SQL filter. Mentioning index usage shows deeper understanding of query performance, but the core solution remains a simple conditional filter.