Exclude records with certain values in Qubole

Question

Using Qubole

I have

Table A (columns in json parsed...)

ID  Recommendation    Decision
1     GOOD            GOOD
2     BAD             BAD
2     GOOD            BAD
3     GOOD            BAD
4     BAD             GOOD
4     GOOD            BAD

I need to Select only IDs which have Recommendation GOOD but Decision BAD. Therefore output should be 3.

I tried :

SELECT a.ID  
FROM (
select json_parsed['ID'] as ID
,json_parsed["Decision"] as Decision
,json_parsed["Recommendation"] as Recommendation
from  A  
where create_date >= '2020-11-18') a
Left JOin
(select json_parsed['ID'] as ID
,json_parsed["Decision"] as Decision
,json_parsed["Recommendation"] as Recommendation
from  A
where create_date >= '2020-11-18') as b on a.ID = b.ID and b.Recommendation = "GOOD"
Where
b.Recommendation is NULL

values in `Decission` field seems wrong - as per your logic it should be `FAIL,NULL` etc.? could you pls correct it? — Koushik Roy, Nov 24 '20 at 12:33
can you try this? This will give you when Recommendation = GOOD AND Decision=BAD - `select json_parsed['ID'] as ID ,json_parsed["Decision"] as Decision ,json_parsed["Recommendation"] as Recommendation from A where create_date >= '2020-11-18' AND json_parsed["Decision"] ='BAD' AND json_parsed["Recommendation"] ='GOOD' ` — Koushik Roy, Nov 24 '20 at 19:26
This query returns also IDs which have Recommendation "GOOD" for example ID 2, but I need to exclude those. In the perfect scenario I d like to see just just ID 3 which have Recommendation only GOOD. — Kurlito, Nov 25 '20 at 06:57
Problem is same as here https://stackoverflow.com/questions/15389091/how-to-exclude-records-with-certain-values-in-sql-select however the offered solution there doesnt seems to be working in Qubole. — Kurlito, Nov 25 '20 at 07:03
You are right, it will return 2,3,4. but how can i exclude 2 and 4 ? Based on what condition? — Koushik Roy, Nov 25 '20 at 13:19
Thats what I dont know. I just need IDs which have recommendation Good and decision BAD without any Goods. — Kurlito, Nov 26 '20 at 08:17

score 0 · Accepted Answer · answered Dec 08 '20 at 13:07

Use analytic functions.

Demo:

with your_table as (--use your table instead of this sample
select stack(6,
1,'GOOD','GOOD',
2,'BAD','BAD'  ,
2,'GOOD','BAD' ,
3,'GOOD','BAD' ,
4,'BAD','GOOD' ,
4,'GOOD','BAD') as (ID,Recommendation,Decision)
)

select ID,Recommendation,Decision
from
(
select d.*, 
       count(*) over(partition by id) as cnt,
       count(case when Recommendation  = 'GOOD' then 1 end) over(partition by id) cnt_Recommendation_good, 
       count(case when Decision  = 'BAD' then 1 end) over(partition by id) cnt_Decision_BAD
  from 
  your_table d
) s
where cnt_Recommendation_good=cnt
  and cnt_Decision_BAD = cnt

Result:

id  recommendation  decision
3   GOOD            BAD

Exclude records with certain values in Qubole

1 Answers1