extract the latest non zero revenue row

Sample data:

Input :

product_name| snapshot_time (varchar)|  revenue  (varchar)  |
product_A   | 2021-07-01            |    $0.0              |
product_A   | 2021-04-01            |    $30.12            |
product_A   | 2021-06-01            |    $12.23            |
product_B   |  ......               | ......               |
product_C   | ......                | .......              |
  ......

Output should be:

product_name| snapshot_time (varchar)|  revenue  (varchar)   |
product_A   | 2021-06-01             |    $12.23             |
product_B   |  lastest   time        | non zero value        |
product_C   | lastest   time         |non zero value         |
  ......

The requrement is that:

For each product:

  • if there is any non zero revenue records:

extract the latest non zero revenue row

  • if all records are $0.0:

extract the latest $0.0 revenue row


I tried:

with t as (
select *, row_number() over(partition by product_name order by snapshot_time desc, revenue desc) as rnk 
from sample_data) 
SELECT product_name, snapshot_time, revenue
FROM t where rnk = 1;

but it’s wrong because it might extract the $0.0 row.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Consider this version:

WITH t AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY product_name
                                 ORDER BY revenue > 0 DESC, snapshot_time DESC) rn
    FROM sample_data
)

SELECT product_name, snapshot_time, revenue
FROM t
WHERE rn = 1;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x