How to consolidate multiple values into one value using case when and partition by in SQL?

user_11692944 0
Viewed 83

I have a table that contains 3 distinct names in one field, the respective account those names are associated to, Unique IDs, the total counts for each pair, and a rank column based on the total counts value. Example here:

LOGO     |  Account |  ID    |   Count_Per_Logo   |  Rank

Walmart     Acct_A     ABC          3                 1
Walmart     Acct_A     DEF          3                 1
Walmart     Acct_A     GHI          3                 1
Vudu        Acct_A     JKL          1                 2
Bonobos     Acct_A     MNO          1                 2

My goal is to 'consolidate' the LOGO field into just one Logo to one Account - so for each of the unique ID's - they should only be associated to 1 logo (instead of the 3 shown above).

Desired Output

LOGO     |  Account |  ID    |   Count_Per_Logo   |  Rank   | Consolidated_LOGO

Walmart     Acct_A     ABC          3                 1       Walmart     
Walmart     Acct_A     DEF          3                 1       Walmart     
Walmart     Acct_A     GHI          3                 1       Walmart     
Vudu        Acct_A     JKL          1                 2       Walmart     
Bonobos     Acct_A     MNO          1                 2       Walmart

Right now I am using a count with partition by to get the rank - but I'm unsure how to create a new field that has the 'desired logo' based on the rank values - would a lag function be necessary for this? Any help would be greatly appreciated

Current logic

SELECT LOGO, ACCOUNT, ID FROM
(SELECT *, DENSE_RANK() OVER (PARTITION BY ACCOUNT ORDER BY LOGO_NAME_CNT DESC) AS LOGO_RANK
FROM
(SELECT *, count(ACCOUNT) over (partition by LOGO) AS LOGO_NAME_CNT FROM "TABLE1" WHERE ACCOUNT = 'Acct_A')
)
WHERE LOGO_RANK = 1
dense-rank partition snowflake-cloud-data-platform case sql
2 Answers

So it looks like your base SQL is:

SELECT
    a.*
    ,dense_rank() over (partition by account order by count_per_logo desc) as rank
FROM (
    SELECT 
        logo
        ,account
        ,id
        ,count(logo) over (partition by account, logo) as count_per_logo
    FROM VALUES 
        ('walmart', 'acct_a', 'abc'),   
        ('walmart', 'acct_a', 'def'), 
        ('walmart', 'acct_a', 'ghi'),
        ('vudu', 'acct_a', 'jkl'),
        ('bonobos', 'acct_a', 'mno')
        v(logo, account, id)
) AS a
ORDER BY 4 DESC, 5;

then all you need to add is a FIRST_VALUE like:

SELECT
    a.*
    ,dense_rank() over (partition by account order by count_per_logo desc) as rank
    ,first_value(logo) over (partition by account order by count_per_logo desc) as Consolidated_LOGO
FROM (
    SELECT 
        logo
        ,account
        ,id
        ,count(logo) over (partition by account, logo) as count_per_logo
    FROM VALUES 
        ('walmart', 'acct_a', 'abc'),   
        ('walmart', 'acct_a', 'def'), 
        ('walmart', 'acct_a', 'ghi'),
        ('vudu', 'acct_a', 'jkl'),
        ('bonobos', 'acct_a', 'mno')
        v(logo, account, id)
) AS a
ORDER BY 4 DESC, 5;

which gives::

LOGO ACCOUNT ID COUNT_PER_LOGO RANK CONSOLIDATED_LOGO
walmart acct_a abc 3 1 walmart
walmart acct_a def 3 1 walmart
walmart acct_a ghi 3 1 walmart
vudu acct_a jkl 1 2 walmart
bonobos acct_a mno 1 2 walmart
user_439920

Simeon's answer should work as long as you don't have ties. Actually, if you don't care for ties, it gets even simpler

select logo,
       account,
       id,
       mode(logo) over (partition by account) as consolidated_logo
from your_table;

If you want the result set to replicate for each instance of logo that ties on count, consider below

with cte as

(select logo, account 
 from your_table 
 group by logo, account
 qualify max(count(*)) over (partition by account)=count(*))


select a.*, b.logo as consolidated_logo
from your_table a
join cte b on a.account=b.account
order by b.logo;
user_99471590
Related