Teradata CASE and HAVING COUNT

Question

I have a table Handset. There are some duplicate imei's and i have to select only one imei each with these requirement:

1. when unique imei found then pick that one
2. when duplicate imei found, if one data_capable = 'Y', pick that one.
3. when duplicate imei found, if both data_capable = 'Y', pick one with max(revenue)
4. when duplicate imei found, if both data_capable = 'N' then pick one with max(revenue)

---

IMEI               MSISDN        REVENUE   DATA_CAPABLE

35622200000001  4282336700001   1000        Y
35622200000001  4282336700002   2000        N
35622200000002  4282336700003   3000        Y
35622200000003  4282336700004   4000        Y
35622200000004  4282336700005   5000        Y
35622200000005  4282336700006   6000        Y
35622200000005  4282336700007   7000        Y
35622200000006  4282336700008   8000        Y
35622200000007  4282336700009   9000        N
35622200000007  4282336700010   1100        N

I am confused to combine CASE WHEN and HAVING COUNT(*)>1 for this case. Any help from master really appreciated

Answer 1

This is a case for a ROW_NUMBER.

Assuming that the options for data_capable are Yand N:

select *
from tab
qualify
   row_number()
   over (partition by imei          -- for each imei
         order by data_capable desc -- 'Y' first
                  ,revenue desc     -- max(revenue) first
        ) = 1