Problem

Table: Customers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| customer_id  | int  |
| year         | int  |
| revenue      | int  |
+--------------+------+
(customer_id, year) is the primary key (combination of columns with unique values) for this table.
This table contains the customer ID and the revenue of customers in different years.
Note that this revenue can be negative.

Write a solution to report the customers with postive revenue in the year 2021.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Input: 
Customers table:
+-------------+------+---------+
| customer_id | year | revenue |
+-------------+------+---------+
| 1           | 2018 | 50      |
| 1           | 2021 | 30      |
| 1           | 2020 | 70      |
| 2           | 2021 | -50     |
| 3           | 2018 | 10      |
| 3           | 2016 | 50      |
| 4           | 2021 | 20      |
+-------------+------+---------+
Output: 
+-------------+
| customer_id |
+-------------+
| 1           |
| 4           |
+-------------+
Explanation: 
Customer 1 has revenue equal to 30 in the year 2021.
Customer 2 has revenue equal to -50 in the year 2021.
Customer 3 has no revenue in the year 2021.
Customer 4 has revenue equal to 20 in the year 2021.
Thus only customers 1 and 4 have positive revenue in the year 2021.

Solution

Method 1 – Simple Filtering

Intuition

We need to select customers who have positive revenue in the year 2021. This is a straightforward filtering problem.

Approach

  • For MySQL/PostgreSQL:
    1. Select rows where year = 2021 and revenue > 0.
    2. Return the customer_id column.
  • For Python (Pandas):
    1. Filter the DataFrame for rows where year == 2021 and revenue > 0.
    2. Return the customer_id column as a DataFrame.

Code

1
2
3

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

class Solution:
    def find_customers_with_positive_revenue(self, df):
        return df[(df['year'] == 2021) & (df['revenue'] > 0)][['customer_id']].reset_index(drop=True)

Complexity

  • ⏰ Time complexity: O(n), as we scan all rows once.
  • 🧺 Space complexity: O(n), for storing the filtered results.