Product Sales Analysis Iii - Leetcode Solution

Problem Statement:

You are given a table Sales that represents the sales of the products. The columns are: ProductID: id of the product Year: year of the record Month: month of the record Revenue: revenue of the product Write an SQL query that reports the products that have not been sold in 2021. Assume we are running this query in 2022.

Solution:

We need to find out the products that have not been sold in 2021. To do so, we need to filter out the rows where the year is 2021 and then group by ProductID. If there is no row with year 2021 for a particular ProductID, then that means the product has not been sold in 2021.

We can use the following query to get the desired result:

SELECT ProductID FROM Sales WHERE Year <> 2021 GROUP BY ProductID HAVING COUNT(*) = COUNT(CASE WHEN Year = 2021 THEN 0 END)

Explanation:

• The WHERE clause filters out all the rows with year 2021.
• We are grouping by ProductID to get the count of rows for each product.
• We are using a HAVING clause to filter out the products that have zero rows with year 2021.
• The COUNT(CASE WHEN Year = 2021 THEN 0 END) expression returns a count of rows where the year is 2021. If the product has not been sold in 2021, then this count will be zero.

1