Who ate more apples than banana?

Question

For the below sample data, please help me to find the person who ate more apples than bananas? enter image description here

Like for instance in the attached data image, Jhon and Rita ate more apples than bananas. I wrote a query for the same but with an inner query. I was thinking about an alternate optimal solution.

WITH DATA AS (
    SELECT 'Jhon' name, 'Banana' FRUIT, SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Jhon' name, 'Banana' FRUIT,  SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Jhon' name, 'Apple' FRUIT,  SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Jhon' name, 'Apple' FRUIT,  SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Jhon' name, 'Apple' FRUIT,  SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Rita' name, 'Apple' FRUIT,  SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Jack' name, 'Apple' FRUIT,  SYSDATE eatingTime FROM dual UNION ALL
    SELECT 'Jhon' name, 'Banana' FRUIT,  SYSDATE eatingTime FROM dual
)
SELECT name FROM DATA p
WHERE 
(SELECT count(*) FROM DATA b WHERE b.FRUIT= 'Banana' AND b.name = p.name) < 
(SELECT count(*) FROM DATA a WHERE a.FRUIT= 'Apple' AND a.name = p.name)

Answer 1

You can use aggregation and filter with a having clause:

select name
from data
group by name
having 
    sum(case when fruit = 'Apple' then 1 else 0 end) 
        > sum(case when fruit = 'Banana' then 1 else 0 end)