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) => {
What is the space complexity of the following code?
1int sum(int n) {
2 if (n <= 0) {
3 return 0;
4 }
5 return n + sum(n - 1);
6}
What is the space complexity of the following code?
1int sum(int n) {
2 if (n <= 0) {
3 return 0;
4 }
5 return n + sum(n - 1);
6}
Solution Implementation
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
12
1public 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}
16
1function 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}
16
What's the output of running the following function using input [30, 20, 10, 100, 33, 12]
?
1def fun(arr: List[int]) -> List[int]:
2 import heapq
3 heapq.heapify(arr)
4 res = []
5 for i in range(3):
6 res.append(heapq.heappop(arr))
7 return res
8
1public static int[] fun(int[] arr) {
2 int[] res = new int[3];
3 PriorityQueue<Integer> heap = new PriorityQueue<>();
4 for (int i = 0; i < arr.length; i++) {
5 heap.add(arr[i]);
6 }
7 for (int i = 0; i < 3; i++) {
8 res[i] = heap.poll();
9 }
10 return res;
11}
12
1class HeapItem {
2 constructor(item, priority = item) {
3 this.item = item;
4 this.priority = priority;
5 }
6}
7
8class MinHeap {
9 constructor() {
10 this.heap = [];
11 }
12
13 push(node) {
14 // insert the new node at the end of the heap array
15 this.heap.push(node);
16 // find the correct position for the new node
17 this.bubble_up();
18 }
19
20 bubble_up() {
21 let index = this.heap.length - 1;
22
23 while (index > 0) {
24 const element = this.heap[index];
25 const parentIndex = Math.floor((index - 1) / 2);
26 const parent = this.heap[parentIndex];
27
28 if (parent.priority <= element.priority) break;
29 // if the parent is bigger than the child then swap the parent and child
30 this.heap[index] = parent;
31 this.heap[parentIndex] = element;
32 index = parentIndex;
33 }
34 }
35
36 pop() {
37 const min = this.heap[0];
38 this.heap[0] = this.heap[this.size() - 1];
39 this.heap.pop();
40 this.bubble_down();
41 return min;
42 }
43
44 bubble_down() {
45 let index = 0;
46 let min = index;
47 const n = this.heap.length;
48
49 while (index < n) {
50 const left = 2 * index + 1;
51 const right = left + 1;
52
53 if (left < n && this.heap[left].priority < this.heap[min].priority) {
54 min = left;
55 }
56 if (right < n && this.heap[right].priority < this.heap[min].priority) {
57 min = right;
58 }
59 if (min === index) break;
60 [this.heap[min], this.heap[index]] = [this.heap[index], this.heap[min]];
61 index = min;
62 }
63 }
64
65 peek() {
66 return this.heap[0];
67 }
68
69 size() {
70 return this.heap.length;
71 }
72}
73
74function fun(arr) {
75 const heap = new MinHeap();
76 for (const x of arr) {
77 heap.push(new HeapItem(x));
78 }
79 const res = [];
80 for (let i = 0; i < 3; i++) {
81 res.push(heap.pop().item);
82 }
83 return res;
84}
85
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.