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:

SELECT customer_id
FROM Customers
WHERE 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.

import sqlite3

# Connect to the SQLite database file
conn = sqlite3.connect('example.db')

# Create a cursor to interact with the database
c = conn.cursor()

# SQL query to find customers with positive revenue in 2021
query = """
SELECT customer_id
FROM Customers
WHERE year = 2021 AND revenue > 0;
"""

# Execute the SQL query
c.execute(query)

# Fetch all results
results = c.fetchall()

# Print the results
for row in results:
    print(row)

# Close connection
conn.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:

npm install sqlite3

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

const sqlite3 = require('sqlite3').verbose();

// Open the SQLite database file
const db = new sqlite3.Database('./example.db', sqlite3.OPEN_READWRITE, (err) => {
  if (err) {
    console.error(err.message);
  }
});

// SQL query to find customers with positive revenue in 2021
const query = `
SELECT customer_id
FROM Customers
WHERE year = 2021 AND revenue > 0;
`;

// Execute the SQL query
db.all(query, (err, rows) => {
  if (err) {
    throw err;
  }

  // Print the results
  rows.forEach((row) => {
    console.log(row);
  });
});

// Close the connection
db.close((err) => {