Find Customers With Positive Revenue this Year Problem


Description

LeetCode Problem 1821.

Table: Customers

1
2
3
4
5
6
7
8
9
10
+--------------+------+
| Column Name  | Type |
+--------------+------+
| customer_id  | int  |
| year         | int  |
| revenue      | int  |
+--------------+------+
(customer_id, year) is the primary key for this table.
This table contains the customer ID and the revenue of customers in different years.
Note that this revenue can be negative.

Write an SQL query to report the customers with postive revenue in the year 2021.

Return the result table in any order.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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      |
+-------------+------+---------+

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

Customer 1 has revenue equal to 30 in year 2021.
Customer 2 has revenue equal to -50 in year 2021.
Customer 3 has no revenue in year 2021.
Customer 4 has revenue equal to 20 in year 2021.
Thus only customers 1 and 4 have postive revenue in year 2021.


MySQL Solution

1
2
3
4
5
select  customer_id
from Customers
where year = 2021
group by customer_id,year
having sum(revenue) > 0 




Related Posts

Maximum Transaction Each Day Problem

LeetCode 1831. Write an SQL query to report the IDs...

League Statistics Problem

LeetCode 1841. Write an SQL query to report the statistics...

Group Employees of the Same Salary Problem

LeetCode 1875. Write an SQL query to report the IDs...

Find Customers With Positive Revenue this Year Problem

LeetCode 1821. Write an SQL query to report the customers...

Convert Date Format Problem

LeetCode 1853. Write an SQL query to convert each date...

Calculate Special Bonus Problem

LeetCode 1873. Write an SQL query to calculate the bonus...