Leetcode 1821. Find Customers With Positive Revenue this Year Solution

Problem Explanation

In this problem, we are given a table Customers with information about the customer ID, the year, and the revenue they generated in that year. Our goal is to write an SQL query that reports the customers with positive revenue in the year 2021.

Example

Let's consider the provided example:

Customers +-------------+------+---------+ | 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 | +-------------+------+---------+

We have to find customers with positive revenue in the year 2021. From the table above, we can see that customer 1 has a revenue of 30 in 2021, customer 2 has a revenue of -50, and customer 4 has a revenue of 20. Customer 3 has no revenue in 2021. Thus, our query should return customers 1 and 4, resulting in the following table:

Result table: +-------------+ | customer_id | +-------------+ | 1 | | 4 | +-------------+

Approach

Since we only need to find customers with positive revenue in 2021, we can use a simple SELECT statement with a WHERE clause to filter for the specified year and a positive revenue condition.

SQL Query

Here's the SQL query that we'll use:

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

This query selects the customer_id for all rows in the Customers table where the year is 2021 and the revenue is greater than zero.## Implementing the Query in Python, JavaScript, and Java

While the given problem is an SQL query problem, you might want to run the query in Python, JavaScript, or Java for some applications. Here's how you can do it using popular database libraries:

Python

To execute the query in Python, we can use the sqlite3 library. Make sure you have SQLite installed and configured on your system.

1import sqlite3
2
3# Connect to the SQLite database file
4conn = sqlite3.connect('example.db')
5
6# Create a cursor to interact with the database
7c = conn.cursor()
8
9# SQL query to find customers with positive revenue in 2021
10query = """
11SELECT customer_id
12FROM Customers
13WHERE year = 2021 AND revenue > 0;
14"""
15
16# Execute the SQL query
17c.execute(query)
18
19# Fetch all results
20results = c.fetchall()
21
22# Print the results
23for row in results:
24    print(row)
25
26# Close connection
27conn.close()

JavaScript

To execute the query in JavaScript, you can use the sqlite3 package, which can be installed via npm.

First, install the sqlite3 package:

1npm install sqlite3

Here's an example of how to run the query with the sqlite3 package in JavaScript:

1const sqlite3 = require('sqlite3').verbose();
2
3// Open the SQLite database file
4const db = new sqlite3.Database('./example.db', sqlite3.OPEN_READWRITE, (err) => {
5  if (err) {
6    console.error(err.message);
7  }
8});
9
10// SQL query to find customers with positive revenue in 2021
11const query = `
12SELECT customer_id
13FROM Customers
14WHERE year = 2021 AND revenue > 0;
15`;
16
17// Execute the SQL query
18db.all(query, (err, rows) => {
19  if (err) {
20    throw err;
21  }
22
23  // Print the results
24  rows.forEach((row) => {
25    console.log(row);
26  });
27});
28
29// Close the connection
30db.close((err) => {