Find Customers With Positive Revenue this Year

Find Customers With Positive Revenue this Year - Problem

Database Easy

You're working as a data analyst for a growing e-commerce company that needs to identify their most valuable customers for the year 2021. The finance team has provided you with a database table containing customer revenue data across multiple years.

Given a Customers table with the following structure:

Column Name	Type
customer_id	int
year	int
revenue	int

Where (customer_id, year) forms the primary key, your task is to find all customers who generated positive revenue in 2021. Note that revenue can be negative (perhaps due to returns or refunds exceeding purchases).

Goal: Write a SQL query to return the customer_id of all customers with positive revenue in 2021. The result can be returned in any order.

This problem tests your ability to filter data based on multiple conditions and work with business logic in SQL queries.

Input & Output

example_1.sql — Basic Case

$ Input: Customers table: | customer_id | year | revenue | |-------------|------|----------| | 1 | 2021 | 300 | | 2 | 2021 | -150 | | 3 | 2021 | 450 | | 1 | 2020 | 500 | | 2 | 2020 | 200 |

› Output: | customer_id | |-------------| | 1 | | 3 |

💡 Note: Customer 1 had $300 positive revenue in 2021, Customer 3 had $450 positive revenue in 2021. Customer 2 had negative revenue (-$150) in 2021, so they're excluded. Records from 2020 are not considered.

example_2.sql — All Negative Revenue

$ Input: Customers table: | customer_id | year | revenue | |-------------|------|----------| | 1 | 2021 | -100 | | 2 | 2021 | -250 | | 3 | 2020 | 400 |

› Output: | customer_id | |-------------| (Empty result)

💡 Note: No customers had positive revenue in 2021. Customer 3 had positive revenue, but it was in 2020, not 2021. Customers 1 and 2 both had negative revenue in 2021.

example_3.sql — Mixed Years

$ Input: Customers table: | customer_id | year | revenue | |-------------|------|----------| | 1 | 2021 | 1000 | | 1 | 2020 | -500 | | 2 | 2019 | 800 | | 2 | 2021 | 150 | | 3 | 2021 | 0 |

› Output: | customer_id | |-------------| | 1 | | 2 |

💡 Note: Customer 1: $1000 in 2021 (positive). Customer 2: $150 in 2021 (positive). Customer 3: $0 in 2021 (not positive, so excluded). Previous years' data doesn't affect the 2021 results.

Constraints

1 ≤ customer_id ≤ 10⁵
2019 ≤ year ≤ 2023
-1000 ≤ revenue ≤ 10000
Each (customer_id, year) combination is unique
Focus on year 2021 only

Visualization

Tap to expand

Understanding the Visualization

Access Database

Connect to the Customers table containing all customer revenue records across multiple years

Apply Year Filter

Filter records to only include data from 2021, ignoring all other years

Apply Revenue Filter

From the 2021 records, keep only those where revenue is greater than 0

Extract Customer IDs

Return the customer_id values from the filtered results

Key Takeaway

🎯 Key Insight: SQL filtering with multiple conditions in a WHERE clause is the most efficient approach for this type of data selection problem - no complex algorithms needed!

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

This problem requires a simple SQL filtering query using a WHERE clause with two conditions. The optimal approach is to directly filter the Customers table for records where year = 2021 and revenue > 0, then return the customer_id. This is efficient with proper indexing and demonstrates fundamental SQL filtering concepts.

Common Approaches

Approach	Time	Space	Notes
✓ Basic SQL Query with Multiple Conditions	O(n)	O(1)	Simple WHERE clause filtering for year and positive revenue

Basic SQL Query with Multiple Conditions — Algorithm Steps

Query all records from the Customers table
Filter for year = 2021
Filter for revenue > 0
Return the customer_id column

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Table

Database scans through all records in Customers table

Apply Year Filter

Filter records where year = 2021

Apply Revenue Filter

Filter remaining records where revenue > 0

Return Results

Return customer_id values that match both conditions

Code -

solution.c — C

// SQL Query Implementation
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int customer_id;
    int year;
    int revenue;
} Customer;

const char* findPositiveRevenueCustomers() {
    return "SELECT customer_id "
           "FROM Customers "
           "WHERE year = 2021 AND revenue > 0;";
}

// Example usage with sample data processing
int processSampleData(Customer* customersData, int size, int* result) {
    int count = 0;
    for (int i = 0; i < size; i++) {
        if (customersData[i].year == 2021 && customersData[i].revenue > 0) {
            result[count++] = customersData[i].customer_id;
        }
    }
    return count;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Linear scan through all records in the table

✓ Linear Growth

Space Complexity

O(1)

No additional space needed for the query execution

✓ Linear Space

34.7K Views

High Frequency

~8 min Avg. Time

892 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Basic SQL Query with Multiple Conditions — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler