Warm tip: This article is reproduced from stackoverflow.com, please click
sql amazon-redshift

Most popular category listed with every row redshift database

发布于 2020-03-28 23:17:33

I require the following data: e.g. sweden male brandA EUR sweden female brandA EUR sweden male brandB EUR england male brandA EUR england female brandA EUR england male brandB EUR england female brandC EUR

EUR came from the fact that of 8000 people, 5000 use euro, 2000 use gbp and 1000 use SEk . SO the most popular currency is EURO. I would like it displayed for every line

to get the most popular currency, max_curr

and for every row returned to have a column called max_curr with this value displayed

    select 
cdl.country
,cd.gender
,cd.brand
,t.max_curr
from customer_data_list cdl  
left join customer_data cd on cd.customerid = cdr.customerid
left join 
(
    select 
        trans.customerid, a.*
    from
    (
        select
            a.*
            ,max(count_currency) over() as max_curr
        FROM
        (
            select 
                t.currency
                ,count(t.currency) as count_currency
            from transactions t
            where t.function = 'DEPOSIT' and t.status = 'ACCEPTED' 
            group by t.currency
            order by currency
        ) a
    ) a
    left join transactions trans on trans.currency = a.currency 
    where count_currency = max_curr) t on t.customerid = cdl.customerid 

This is not returning what I require because most max_curr column entries are empty. Can someone help please?

Questioner
Lilz
Viewed
17
Gordon Linoff 2020-02-01 01:12

If, for each customer, you want the currency that is used most often for ACCEPTED, DEPOSIT transactions, then you can use aggregation and window functions:

select cc.*
from (select t.customerid, t.currency, count(*) as cnt,
             row_number() over (partition by t.customerid order by count(*) desc) as seqnum
      from transactions t
      where t.function = 'DEPOSIT' and
            t.status = 'ACCEPTED' 
      group by t.customerid, t.currency
     ) cc
where seqnum = 1;

EDIT:

For the revised interpretation of the question, just use a cross join:

select t.customerid, t.currency, count(*) as cnt,
       tt.currency as most_popular_currency
from transactions t cross join
     (select t.currency
      from transactions t
      where t.function = 'DEPOSIT' and
            t.status = 'ACCEPTED'
      group by t.currency
      order by count(*) desc
      limit 1
     ) tt
where t.function = 'DEPOSIT' and
      t.status = 'ACCEPTED' 
group by t.customerid, t.currency