SQL (Presto): Return Date when Sum of x equals some threshold

user_16733472 0
Viewed 136

I have a table the one below. I want to return the Date when the sum of Result not equal to 'na' is equal to 4

Date ID Result
12-01-21 a Negative
12-01-21 b Negative
12-02-21 b Negative
12-02-21 c na
12-03-21 d Positive

Desired output:

Date
2021-03-21

What is the most efficient way to do this? I have an idea of some elements people use from quickly searching, such as some combination rank functions, inner queries, or WITH, but I do not know the structure or format in which to arrange.

For example, logically I was thinking, rank the table by date, when pull the Date when count(Date) = 4 ,but I'm not sure of the structure. I can come up with logic, it's the structure that is the hard part for me.

Thank you for your patience and time :)

presto rank window-functions sql
1 Answers

You can use SUM() OVER () window function along with a conditional such as

WITH tt AS
(
 SELECT t.*, 
        SUM(CASE WHEN Result!='na' THEN 1 END) OVER (ORDER BY Date) AS rnk
   FROM t
)
SELECT Date
  FROM tt
 WHERE rnk = 4

or ROW_NUMBER()

WITH tt AS
(
 SELECT *, ROW_NUMBER() OVER (ORDER BY Date) AS rnk
   FROM t
  WHERE Result!='na' 
)
SELECT Date
  FROM tt
 WHERE rnk = 4
user_58413060
Related