1821. Find Customers With Positive Revenue this Year
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) => {
Given a sorted array of integers and an integer called target, find the element that
equals to the target and return its index. Select the correct code that fills the
___ in the given code snippet.
1def binary_search(arr, target):
2 left, right = 0, len(arr) - 1
3 while left ___ right:
4 mid = (left + right) // 2
5 if arr[mid] == target:
6 return mid
7 if arr[mid] < target:
8 ___ = mid + 1
9 else:
10 ___ = mid - 1
11 return -1
121public static int binarySearch(int[] arr, int target) {
2 int left = 0;
3 int right = arr.length - 1;
4
5 while (left ___ right) {
6 int mid = left + (right - left) / 2;
7 if (arr[mid] == target) return mid;
8 if (arr[mid] < target) {
9 ___ = mid + 1;
10 } else {
11 ___ = mid - 1;
12 }
13 }
14 return -1;
15}
161function binarySearch(arr, target) {
2 let left = 0;
3 let right = arr.length - 1;
4
5 while (left ___ right) {
6 let mid = left + Math.trunc((right - left) / 2);
7 if (arr[mid] == target) return mid;
8 if (arr[mid] < target) {
9 ___ = mid + 1;
10 } else {
11 ___ = mid - 1;
12 }
13 }
14 return -1;
15}
16Which of the following is a min heap? 
Which of the following shows the order of node visit in a Breadth-first Search?
How many times is a tree node visited in a depth first search?
Recommended Readings
Top Patterns to Conquer the Technical Coding Interview Should the written word bore you fear not A delightful video alternative awaits iframe width 560 height 315 src https www youtube com embed LW8Io6IPYHw title YouTube video player frameborder 0 allow accelerometer autoplay clipboard write encrypted media gyroscope picture in picture
Recursion Recursion is one of the most important concepts in computer science Simply speaking recursion is the process of a function calling itself Using a real life analogy imagine a scenario where you invite your friends to lunch https algomonster s3 us east 2 amazonaws com recursion jpg You first
Runtime Overview When learning about algorithms and data structures you'll frequently encounter the term time complexity This concept is fundamental in computer science and offers insights into how long an algorithm takes to complete given a certain input size What is Time Complexity Time complexity represents the amount of time
Got a question? Ask the Teaching Assistant anything you don't understand.
Still not clear? Ask in the Forum, Discord or Submit the part you don't understand to our editors.