Query that reports the customers with the total purchases strictly increasing-CodePudding

In an interview I have been asked to return the SQL query that reports the IDs of customers with the total purchases strictly increasing yearly

Table is something like below:

And the output expected is 11,22 as 33 is not strictly increasing.

I did not able to solve it however, on googling I found the solution which is:

WITH year_cte AS (
    SELECT  customer_id, 
            YEAR(order_date) AS year, 
            SUM(price) AS total 
    FROM Orders
    GROUP BY customer_id, year
    ORDER BY NULL
)

SELECT a.customer_id
FROM year_cte a 
     LEFT JOIN year_cte b
     ON b.customer_id = a.customer_id AND b.year = a.year   1
GROUP BY a.customer_id
HAVING SUM(a.total >= IFNULL(b.total, 0)) = 1 // Did not get this SUM
ORDER BY NULL;

Now my problem is that I am able to understand the solution apart from 1 line which is:

HAVING SUM(a.total >= IFNULL(b.total, 0)) = 1 // Did not get this SUM

Can someone please help me to understand why there is a condition inside the sum() and why it is equating with 1?

CodePudding user response：

The a table contains the total price for a customer_id for the year prior to table b.

You want to retrieve all customer_ids having total strictly increasing so you need to check that the number of rows satisfying the condition a.total >= IFNULL(b.total, 0) are 1.

This case happens when the customer has a strictly increasing streak of totals during the years, the = 1 accounts for the fact that the last line will have a value of b.total equal to NULL (and so a.total >= 0 will satisfy the condition).

Using customer_id = 11 as an example:

customer_id | year | total 
--------------------------
         11 | 2019 |   150
         11 | 2020 |   200
         11 | 2021 |   250

For a.year = 2019 and b.year = 2020 you will have 150 >= 200.
For a.year = 2020 and b.year = 2021 you will have 200 >= 250.
For a.year = 2021 and b.year = 2022 you will have 250 >= 0.

The SUM of this expression will give 1.