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) => {