Find Customers With Positive Revenue this Year - Problem

Given a table Customers that contains customer information including their customer ID, year, and revenue data, write a SQL solution to find all customers who had positive revenue in the year 2021.

Note: Revenue can be negative, so we only want customers where revenue > 0 for the year 2021.

Return the result table in any order.

Table Schema

Customers
Column Name Type Description
customer_id PK int Unique identifier for each customer
year PK int The year of the revenue record
revenue int Customer revenue for that year (can be negative)
Primary Key: (customer_id, year)
Note: The combination of customer_id and year forms the primary key

Input & Output

Example 1 — Mixed Revenue Records
Input Table:
customer_id year revenue
1 2021 1000
2 2021 -1000
3 2020 2000
1 2020 4000
Output:
customer_id
1
💡 Note:

Only customer 1 has positive revenue in 2021 (1000). Customer 2 has negative revenue in 2021 (-1000), and customer 3's positive revenue is from 2020, not 2021.
Example 2 — No Positive Revenue in 2021
Input Table:
customer_id year revenue
1 2021 -500
2 2021 0
3 2020 1000
Output:
customer_id
💡 Note:

No customers have positive revenue in 2021. Customer 1 has negative revenue, customer 2 has zero revenue (not positive), and customer 3's positive revenue is from 2020.
Example 3 — Multiple Positive Customers
Input Table:
customer_id year revenue
1 2021 500
2 2021 1500
3 2021 200
Output:
customer_id
1
2
3
💡 Note:

All three customers have positive revenue in 2021, so all customer IDs are returned in the result.

Constraints

  • 1 ≤ customer_id ≤ 100000
  • 2000 ≤ year ≤ 2025
  • -1000000 ≤ revenue ≤ 1000000

Visualization

Tap to expand
Find Customers With Positive Revenue (2021) INPUT: Customers Table customer_id year revenue 1 2018 50 1 2021 100 2 2021 -100 2 2020 80 3 2021 200 4 2019 150 = 2021 with revenue > 0 Filter: year = 2021 AND revenue > 0 ALGORITHM STEPS 1 SELECT customer_id Choose output column 2 FROM Customers Specify source table 3 WHERE year = 2021 Filter for year 2021 4 AND revenue > 0 Filter positive revenue SELECT customer_id FROM Customers WHERE year = 2021 AND revenue > 0; FINAL RESULT customer_id 1 3 OK - 2 customers found Both had positive revenue in year 2021 Excluded: Customer 2: revenue = -100 Customer 4: year = 2019 Key Insight: Use WHERE clause with two conditions: year = 2021 AND revenue > 0. The AND operator ensures both conditions must be true. Remember that revenue can be negative, so checking > 0 is essential. TutorialsPoint - Find Customers With Positive Revenue this Year | Optimal Solution

Related Problems

Asked in
Amazon 15 Google 12 Microsoft 8
25.0K Views
Medium Frequency
~5 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen