CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athena
Erin L
Erin L

Reputation: 95

SQL: add logic to handle tie when select max value

I have a table like this:

store shelf type count
A s1 t1 3
A s1 t2 4
A s2 t3 2
A s2 t2 2
A s3 t4 1
A s3 t5 1

I want to know the dominant type per store per shelf. The rule is:

for each store and shelf:

  1. if there's a max value of count for one type, the dominant would be this type. for store A shelf s1, it would be t2, because t2 > t1)

  2. if there's tie, then check the count of the type for store total, and use whichever has a larger store total. for store A shelf s2, it would be t2, because total count of t2 (2 + 4) > total count of t3 (2)

  3. if still a tie, will put dominant as "can't decide". for store A shelf s3, it would be "can't decide"

Sample output:

store shelf dominantType
A s1 t2
A s2 t2
A s3 can't decide

Now I can achieve the first 2 rules with first calculating countStoreLevel, then use ROW_NUMBER() OVER:

rank AS (
SELECT table.*, ROW_NUMBER() OVER (PARTITION BY store, shelf ORDER BY count DESC, countStoreLevel DESC) AS rn
FROM shopping)

SELECT * FROM rank WHERE rn = 1

But I don't know how to deal with rule 3, when there's a tie, and it should be can't decide.

Could anyone help me with it? Thanks a looooot!

Upvotes: 0

Views: 490

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You can use window functions. The idea is:

  • Count up the rows with type by shelf and by store.
  • Enumerate the counts within a shelf and within a store by the count, so you know which count is highest.
  • Check if there is only one type for a shelf. If so, use that.
  • Check if there is only one type for a store. If so, use that.
  • Otherwise, 'can''t decide'.

As a query, this looks like:

select t.*,
       (case when min(case when seqnum_shelf = 1 then type end) over (partition by store, shelf) =
                  max(case when seqnum_shelf = 1 then type end) over (partition by store, shelf) =
             then min(case when seqnum_shelf = 1 then type end) over (partition by store, shelf)
             when min(case when seqnum_store = 1 then type end) over (partition by store) =
                  max(case when seqnum_store = 1 then type end) over (partition by store) =
             then min(case when seqnum_store = 1 then type end) over (partition by store)
             else 'can''t decide'
          end) as dominantType                   
from (select t.*,
             dense_rank() over (partition by store, shelf order by cnt_shelf desc) as seqnum_shelf,
             dense_rank() over (partition by store order by cnt_shelf desc) as seqnum_store
      from (select t.*,
                   count(*) over (partition by store, shelf, type) as cnt_shelf,
                   count(*) over (partition by store, type) as cnt_store
            from t
        ) t;

Upvotes: 1

Related Questions